Something is wrong with this IF statment

[Guest]

if(AS3>1100,73,if(as3>1000,77,if(as3>900,82,if(as3>800,82,if(as3>700,84,if(as3>600,77,if(as3>500,80,if(as3>400,85.1,if(as3>300,83.4,if(as3>200,89.8,if(as3>100,103,if(as3>0,103,0))))))))))))

Does Office cutout after too many nested if? when i click ok on the error it
higlights my 6th last if. If i delete all ifs after and make the 6th last
the last if, it works fine... it also doesn't highlight my as3 reference
after the 6th last if...

 

[phchenier]

Yes... Excel has that limit and it is the reason for it not working...

 

[Pete_UK]

You could replace all those IFs by using VLOOKUP. Set up this small
table somewhere (eg in cells X1 to Y12 in the same sheet as your
formula):

0.01 103
100.01 103
200.01 89.8
300.01 83.4
400.01 85.1
500.01 80
600.01 77
700.01 84
800.01 82
900.01 82
1000.01 77
1100.01 73

Then replace your formula with this:

=IF(AS3=0,0,VLOOKUP(AS3,X$1:Y$12,2,1))

This should give you what you want. the ".01" added on to each initial
number ensures that AS3 has to be larger than the integer - you can
make this 0.1 or 0.000001, depending on the values that AS3 can take.

Hope this helps.

Pete