Multiple If Formula

N

natei6

Hello to all the experts,

I'm need a formula that says, =If A1<=5,"15.000", If a1=6,"18.000",I
A1=7,"21.000",If A1=8,"24.000",I
A1=9,"27.000",IFA1=10,"30.000",IfA1=11,"33.000",IfA1=12,"36.000",I
A1=13,"39.000",If A1=14,"42.000",If A1=15,"45.000",If A1>=16,"49.000".
I'm not sure how to tie all of this together in one formula. Help muc
appreciated.

Nathan Sargean
 
F

Frank Kabel

Hi
setup a lookup table on a separate sheet (e.g. called 'lookup') with
the following structure:
A B
0 15.000
6 18.000
7 21.000
....
16 49.000

now use the following formula
=VLOOKUP(A1,'lookup'!$A$1:$B$20,2)
 
N

natei6

Hi Frank,

Thanks for the response, but I'm not sure how that will work with th
different criteria at the beginning and end of the formula, ("=I
(A1<=5,"15.000" )at the beginning and (=If(A1>=16,"49.000") at th
end.

Thanks Again

Nathan Sargean
 
J

JWolf

=IF(A1<=5,15,IF(A1>=16,49,A1*3)) , format the cell as a number with
three decimal places.
Formula also assumes that A1 has integers.
 
F

Frank Kabel

Hi
try the formula. It should work as I use VLOOKUP without FALSE as 4th
parameter. So VLOOKUP will return the value <= the lookup value
 
N

natei6

Thanks Frank and JWolf,

Frank, your solution works ok unless A1 is 0, then it says n/a. JWolf
your solution seems to work perfectly without adding another sheet t
the workbook. Is there a reason I should go with the vlookup solution


Thanks again for all the help.

Nathan Sargean
 
F

Frank Kabel

Hi
in your case I would use JWolf's solution. VLOOKUP would be the
prefered solution if you have conditions and results what are not
linked by an algorithmn
 
Top