elseif formula

I am trying to write a formula that would look at D2 and compare it to
determine the value of F2 on the attached sheet. I have tried sumif, elseif
etc... and cannot get this to work. Please help!!!

Need formulas for individual and family

Weekly Hours Emp Cost Individual Emp Cost Family
19 #REF!
21
35
29
39

Compare to this for cost

# of Hrs Individual Family
40 $37.71 $83.62
38 $41.72 $92.51
36 $45.73 $101.40
35 $47.73 $105.84
34 $49.73 $110.28
32 $53.74 $119.17
30 $57.75 $128.06
28 $61.76 $136.00
26 $65.77 $145.83
24 $69.78 $154.72
22 $73.78 $163.61
20 $77.79 $172.49

1st
formula:=IF(D2>39,L2)(D2>37<39,L3)(D2>36<37,L4)(D2>35<36,L5)(D2>34<35,L6)(D2>32<34,L7)(D2>30<32,L8)(D2>28<30,L9)(D2>26<28,L10)(D2>24<26,L11)(D2>22<24,L12)(D2>20<22,L13)

2nd formula:
If Weekly Hours = 40 Then
Emp Cost Individual = L2
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L3
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L4
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L5
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L6
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L7
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L8
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L9
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L10
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L11
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L12
ElseIf Weekly Hours = 38 Then
Emp Cost Individual = L13

Weekly Hours <= 19
End If
End Function

Sort your table in ascending order on the hours, then use for the individual
cost

=VLOOKUP(hours,table,2)

change the 2 to a 3 to get the family cost


"macrodummy" wrote:

> I am trying to write a formula that would look at D2 and compare it to
> determine the value of F2 on the attached sheet. I have tried sumif, elseif
> etc... and cannot get this to work. Please help!!!
>
> Need formulas for individual and family
>
> Weekly Hours Emp Cost Individual Emp Cost Family
> 19 #REF!
> 21
> 35
> 29
> 39
>
> Compare to this for cost
>
> # of Hrs Individual Family
> 40 $37.71 $83.62
> 38 $41.72 $92.51
> 36 $45.73 $101.40
> 35 $47.73 $105.84
> 34 $49.73 $110.28
> 32 $53.74 $119.17
> 30 $57.75 $128.06
> 28 $61.76 $136.00
> 26 $65.77 $145.83
> 24 $69.78 $154.72
> 22 $73.78 $163.61
> 20 $77.79 $172.49
>
> 1st
> formula:=IF(D2>39,L2)(D2>37<39,L3)(D2>36<37,L4)(D2>35<36,L5)(D2>34<35,L6)(D2>32<34,L7)(D2>30<32,L8)(D2>28<30,L9)(D2>26<28,L10)(D2>24<26,L11)(D2>22<24,L12)(D2>20<22,L13)
>
> 2nd formula:
> If Weekly Hours = 40 Then
> Emp Cost Individual = L2
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L3
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L4
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L5
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L6
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L7
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L8
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L9
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L10
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L11
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L12
> ElseIf Weekly Hours = 38 Then
> Emp Cost Individual = L13
>
> Weekly Hours <= 19
> End If
> End Function
>
>
>
>