# Thread: formula help zero's and blanks (2003 SP3)

1. ## formula help zero's and blanks (2003 SP3)

Hi,

I have the following formula
=IF(GY2<0,"blank",IF(GY2<10,\$ID\$2,IF(GY2<20,\$ID\$3, IF(GY2<30,\$ID\$4,IF(GY2<40,\$ID\$5,IF(GY2<50,\$ID\$6,IF (GY2<60,\$ID\$7,IF(GY2<70,\$ID\$8,FALSE))))))))
which does not give the results I want
but =IF(GY2<1,"blank",IF(GY2<10,\$ID\$2,IF(GY2<20,\$ID\$3, IF(GY2<30,\$ID\$4,IF(GY2<40,\$ID\$5,IF(GY2<50,\$ID\$6,IF (GY2<60,\$ID\$7,IF(GY2<70,\$ID\$8,FALSE))))))))
does not give the correct results either.

Basically column GY holds numeric values that can be grouped at a higher level (column ID)
My problem is with the value 0

0 should fall within the first group at ID2
blanks however should fall into the category blank (eg they have not been coded)

Is there a method to get Excel to differentiate between a cell containing the value zero and a blank cell?

Thanks for any help

capri

2. ## Re: formula help zero's and blanks (2003 SP3)

You could test for a blank value:

=IF(GY2="","blank",IF(GY2<10,\$ID\$2,IF(GY2<20,\$ID\$3 ,IF(GY2<30,\$ID\$4,IF(GY2<40,\$ID\$5,IF(GY2<50,\$ID\$6,I F(GY2<60,\$ID\$7,IF(GY2<70,\$ID\$8,FALSE))))))))

You can simplify the formula by creating a lookup table - see the attached example. The formula becomes

=IF(GY2="","blank",VLOOKUP(GY2,IC2:ID9,2))

This is easier to maintain - you can change the values in the lookup table instead of in the formula.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•