Excel Function - Need ISBLANK in nested IF function

[JonesL]

I have the following function in a series of cells:

 

=IF(J41>=2000,"Outstanding",IF(J41>=1900,"Above Average",IF(J41>=1600,"Average",IF(J41>=1500,"Below Average",IF(J41<1500,"Poor","")))))

 

However if cell J41 is blank, it still returns "Poor" whereas I'd like it to stay blank until a number is entered. I've tried using an ISBLANK function at the end (replacing the value if false instead of the "") but cannot seem to find what it should be. Can anybody help?

 

Thanks

[bladedanny]

I just added an extra if to the start to test for no text, then if false carry on. Seems to work.

 

=IF(A1= "","",IF(A1>=2000,"Outstanding",IF(A1>=1900,"Above Average",IF(A1>=1600,"Average",IF(A1>=1500,"Belo w Average",IF(A1<1500,"Poor",""))))))

[pcstru]

Not sure why you don't just use another if such that :

 

=IF(J41="","", IF(J41>=2000,"Outstanding",IF(J41>=1900,"Above Average",IF(J41>=1600,"Average",IF(J41>=1500,"Belo w Average",IF(J41<1500,"Poor",""))))))

 

Another pointer might be to use a lookup table using the ability to go to the nearest value and treat blank as zero with a suitable looked up vale of blank. It will give exactly the same result but is easier to maintain if the boundaries change.

[clareq]

This works too: =IF(A1>=2000,"Outstanding",IF(A1>=1900,"Above Average",IF(A1>=1600,"Average",IF(A1>=1500,"Below Average",IF(ISBLANK(A1),"","POOR")))))

[spadam]

=IF(ISBLANK(J41),"",IF(J41>=2000,"Outstanding",IF(J41>=1900,"Above Average",IF(J41>=1600,"Average",IF(J41>=1500,"Below Average",IF(J41<1500,"Poor",""))))))

 

I think this should work. The blank cell is considered to have a value in Excel so the check for blank needs to be before the other conditions to ensure that they do not prevent it from being evaluated.

[bladedanny]

Many ways to do it, but the overall answer is to add another IF statement. Or as @pcstru says use a VLOOKUP