Why won't it work

[Brown]

I am trying to get a cell to return a value, if cell 1 has a value entered from a drop down box then I want cell 2 to return a value. Here is what I am using and cannot get it to work;

=if(b6=" "," ",if(b6=A,"4.0",if(b6=B,"3.0",if(b6=C,"2.0",if(b6=D,"1.0",if(b6=F,"0.0",if(b6=AP,"5.0",if(b6=BP,"4.0",if(b6=CP,"3.0",if(b6=DP,"2.0"," ")))))))))))

Any ideas?

Thanks

 

[SteveO59L]

There is a limit (7 I think) to the number of If conditions you can have in a formula

Try tabulating your relationships, and use vlookup()

 

[Andrew Poulsom]

Too many nested IFs. Use a lookup table:

http://www.contextures.com/xlFunctions02.html

 

[Jonmo1]

Try

=IF(B6="","",LOOKUP(B6,{"A","B","C","D","F","AP","BP","CP","DP"},{4,3,2,1,0,5,4,3,2}))

 

[Brown]

I have never used a vlookup, but I will try to figure it out. Thanks for the quick response.

 

[Brown]

jonmo that worked, except the AP comes back as a 4 not a 5, and the BP comes back as a 3 not 4. Any ideas?

Thanks again!
Brown

 

[Jonmo1]

Woops..forgot to sort the lookup table...

Post # 4 should be

=IF(B6="","",LOOKUP(B6,{"A","AP","B","BP","C","CP","D","DP","F"},{4,5,3,4,2,3,1,2,0}))

 

[Brown]

AWESOME!!!
Thanks!
Brown