If formulas?

D

Dale

I'm trying to create a formula to give me the following:

$1.00-30.00=1.46
30.01-60.00=4.35
60.01-89.00=8.70
89.01-119.00=12.16
119.01-147.00=19.54
147.01-178.00=23.88
178.01-206.00=28.22
206.01-236.00=32.56
236.01-295.00=39.07
295.01-354.00=54.11
354.01-413.00=75.23
413.01-471.00=86.81
741.01-530.00=122.98
530.01-590.00=130.22

Can anyone help??...Thank You
 
T

Teethless mama

=LOOKUP(A1,{1,30.01,60.01,89.01,119.01,147.01,178.01,206.01,236.01,295.01,354.01,413.01,741.01,530.01},{1.46,4.35,8.7,12.16,19.54,23.88,28.22,32.56,39.07,54.11,75.23,86.81,130.22})
 
G

Gord Dibben

Dale.....use a VLOOKUP table and VLOOKUP formulas.

In A1:B8..........partial data example

0 1.45
30.01 4.35
60.01 8.7
89.01 12.16
119.01 19.54
147.01 23.88
178.01 28.22
206.01 32.56

In D1 enter =VLOOKUP(C1,$A$1:$B$8,2,TRUE)

Drag/copy down to D8

In C1 enter a number e.g. between 0 and 30 to return 1.45 in D1

or 30.01 to 60 to return 4.35


Gord Dibben MS Excel MVP
 
Top