Formula NOT displaying 0 (zero)

Topic

New Reply

On one tab I have a cell that has the value 0 (zero) in it and formatted to display as five zeros (00000). Another sheet has a formula that looks at that cell and returns either a null (blank) character or the value of the cell (IF(Data!AB17=””,””,Data!AB17)). I would expect the formula to display a 0 since that is what is in the referenced cell… but it returns a blank instead. Options has zero values with a check mark. If I change the formula to read something like… IF(Data!AB16=””,””,IF(Data!AB16=0,0,Data!AB16)), I still get a blank cell. Any help would be greatly appreciated.

DennisK

Reply | Quote

Replies

It sounds to me like the cell does not have a zero. Can you attach a sample file that demonstrates the problem

Steve

Reply | Quote

Dennis,

Check the format of the cells that contain the formulas. Some formats will change a zero to a blank (eg. #####)

Reply | Quote

Try =IF(Data!AB17″”,Data!AB17,””)

It worked for me

Reply | Quote

OK… will upload a file called 0test0. If you look on the tab called “Data” at cell D18 you will see that the cell if formatted to display 5 digits… the actual value in the cell is 0 (zero). If you then look at the tab called “LinkedSheet” at cell D14 you will see that the cell is blank. I did change the formula to try to look at the way that Alan Wade suggested but to no success.

Reply | Quote

You have the spreadsheet to evaluate as Lotus123 did. In Lotus123 a null evaluated as equal to a zero, which makes the IF comparison evaluate as false, so it displays a null.
in XL2010:
File-options
Advanced
Lotus compatibility settings
Uncheck: “Transition formula evaluation”

In earlier versions I believe it was under:
Tools-Options -Transition(tab)

Steve

Reply | Quote

THAT’S IT!!! Thanks so much.

Reply | Quote