IF FORMULA....

[Guest]

Im having a problem trying to check the result via IF....

ok heres how it goes....

I have 2 checks that i need to do....

First is if a number is a certain Range... if it is... then it will be given
a certain Grade 1 - 5 the command is as FOLLOWs...

This is what i have in my B1 Cell....
=IF(A1=0,"0",IF(A1>41,"5",IF(AND(A1<=41,A1>=36),"4",
IF(AND(A1<=35,A1>=32),"3",
IF(AND(A1<=31,A1>=27),"2",IF(AND(A1<=26,A1>=22),"1","0"))))))


Next i need to check... if the person got more than Grade 3...
so in C1 i have
=IF(B1>=3,"GOLD","SILVER")

It seems that i cant do it...

Is it not possible to have an IF Function to check an IF Result?

I really hope someone is able to assist me... Im very lost... Preferably if
i can get someone via MSN Messenger to discuss would be even greater....

 

[Gary Keramidas]

you have quotes around the results of your if statement. so "5" as the result in
b1 is not going to = 5 in the formula in c1

remove the quotes
=IF(A1=0,0,IF(A1>41,5,IF(AND(A1<=41,A1>=36),4,IF(AND(A1<=35,A1>=32),3,IF(AND(A1<=31,A1>=27),2,IF(AND(A1<=26,A1>=22),1,0))))))

 

[Guest]

Why not do it in one cell.

Creat a table of your results in this case a17 to B21:-

22 1
27 2
32 3
36 4
41 5


Then in B1 use

=VLOOKUP(A1,A17:B21,2,TRUE)

You don't say what happens for <22 so the formula returns an error.

Mike

 

[Ferris]

Im having a problem trying to check the result via IF....

ok heres how it goes....

I have 2 checks that i need to do....

First is if a number is a certain Range... if it is... then it will be given
a certain Grade 1 - 5 the command is as FOLLOWs...

This is what i have in my B1 Cell....
=IF(A1=0,"0",IF(A1>41,"5",IF(AND(A1<=41,A1>=36),"4",
IF(AND(A1<=35,A1>=32),"3",
IF(AND(A1<=31,A1>=27),"2",IF(AND(A1<=26,A1>=22),"1","0"))))))

Next i need to check... if the person got more than Grade 3...
so in C1 i have
=IF(B1>=3,"GOLD","SILVER")

It seems that i cant do it...

Is it not possible to have an IF Function to check an IF Result?

I really hope someone is able to assist me... Im very lost... Preferably if
i can get someone via MSN Messenger to discuss would be even greater....

Try this instead:
=IF(A1>41,5,IF(A1>35,4,IF(A1>31,3,IF(A1>26,2,IF(A1>21,1,0)))))

You don't need to test if a number is greater than or less than a
number if you test in descending order. The IF will find the first
correct match and then exit out.

 

[Guest]

Apologies you wanted a zero for <22 so:-

=IF(ISERROR(VLOOKUP(A1,A17:B21,2,TRUE)),0,VLOOKUP(A1,A17:B21,2,TRUE))

Mike

 

[Ferris]

The < 22 condition should return a 0, which Mike's solution will do if
you add
0 0 <--add this to the top of his list
22 1
27 2
etc...