Checking and IF ?

[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... unless B1 is a just a number and does not
contain AN IF Formula....

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....

 

[Vasant Nanavati]

Remove the quotes around the numbers in your first formula.
_______________________________________________________________________

 

[Peo Sjoblom]

It's better to use this

=VLOOKUP(A1,{0,0;22,1;27,2;32,3;36,4;41,5},2)

however you can remove the quotes from your formula (A1=0,"0" instead use
A1=0,0) and it will work.
Your formula uses text representation of those numbers and a text number
will always be greater than any real number

the formula I posted will do the same with much less effort

 

[Guest]

hello,

if ur satisfied with the first if's
then u may consider to just modify the next one, with this*--*

=IF(--B1>=3,"GOLD","SILVER")

regards,
driller

 

[Guest]

The problem you're running into is that your first IF statement is returning
text values rather than numbers. Your second IF statement is looking for
numbers.

Try removing the quotes from your Grades in the first IF. The quotes tell
Excel to treat the result as Text instead of a Number. The number 1 and the
text value 1 may look the same to you, but Excel treats them differently.

Also, you could shorten up your first formula a bit. No need for all of the
AND functions.

=IF(A1>41,5,IF(A1>=36,4,IF(A1>=32,3,IF(A1>=27,2,IF(A1>=22,1,0)))))

The A1>=36 is only evaluated when A1>41 is False. Therefore, it is
redundant to include the A1<=41 part, etc...

HTH,
Elkar


=

 

[Guest]

=LOOKUP(A1,{0,22,27,32,36,42},{0,1,2,3,4,5})