Custom Formatting makes formula not recognize cell as blank

**williamthomp** · 04-12-2012, 11:34 AM

Here is my formula..It does not appear to work as it should due to the custom format that the cell has assigned to it

=IF(COUNTA(AO3)=1,"Delivered",IF(COUNTA(AN3)=1,"Out for Delivery",IF(COUNTA(AF3)=1,"Arrived",IF(COUNTA(X3)=1,"Lifted","On Ground"))))

The way it should work is if the AO3 cell has data in it then it will return "Delivered" as the cell answer..and if it is blank then it goes to the next cell to see if it is blank or not and so on..

The problem is even though AO3 is blank it is still seeing it as having data in it..In fact if I go to AO3 and hit delete on the blank cell the formula will progress.. The cell is blank the only thing about it is it has custom formating so the it is a time/date format. ( 04/04/12 15:30 )
Not sure why the formula doesn't see it as blank..

Any help would be greatly appreciated.

thanks
Bill

**ChemistB** · 04-12-2012, 11:58 AM

The use of custom formatting should not result in your COUNTA not working. Can you upload an example of one that is not working. Delete all sensitive and nonrelative data

**williamthomp** · 04-12-2012, 12:19 PM

How do I upload ChemistB

**williamthomp** · 04-12-2012, 12:25 PM

Ok..Figured it outTest Workbook.zip

**ChemistB** · 04-12-2012, 01:43 PM

There's something in your column AO that is not registering as a blank. To fix this quickly, Do a "Text to Columns" (In 2003, I believe it's under Data dropdown). Then click "Finish". All your null strings will disappear and you'll get the proper values. Did this work for you?

**williamthomp** · 04-13-2012, 02:14 PM

Not sure what you mean by text to columns..where is that in Excel 2010 also I have to leave the customs formatting in these cell the same as it will be pasted unto another vendors spreadsheet

thanks,
Bill

**ChemistB** · 04-13-2012, 03:33 PM

This shouldn't hurt your custom formatting. In Excel 2010, it's on the Data Tab near the center. It's usually used to separate strings into separate columns by a delimiter (i.e. spaces or : or tab) but also can be used to reset numbers to numbers when stored as text or to remove null strings. When you click on it, it'll open a window (Text to Columns Wizard) which, in your case, all you need to do is click on "Finish" and your formulas should start working.