I need a formula to calculate rates based on current age

[Guest]

I am calculate benefit rates based on current age. I have entered the
following, which works but I have more age catagories that need to be added
=IF(E22>70,"2.01",IF(E22>65,"1.24",IF(E22>60,".66",IF(E22>55,".43",IF(E22>50,".23",IF(E22>45,".15",IF(E22>40,".10",IF(E22>35,".09",".07"))))))))
Two additional arguments need to be added. Help

 

[Guest]

Martha,

I suggest this is done with a table instead. Create a table of your values
like the one I have part created here:-

0 7
36 9
41 10
46 15

Note it must be laid out ascending in the left column. then use the formula:-

=VLOOKUP(E22,A1:B4,2,TRUE)


It will check E22 and return the value from Column 2 of the table

Mike

 

[Peo Sjoblom]

If those are rates you should remove all the quotation marks since those
make the values text and not numbers, anyway you can bypass IF limits by
using a lookup formula, this will do exactly the same as your posted formula
and it can be amended

=VLOOKUP(E22,{0,0.07;36,0.09;41,0.1;46,0.15;51,0.23;56,0.43;61,0.66;66,1.24;71,2.01},2)

 

[Don Guillett]

use a LOOKUP formula or a CHOOSE formula