Formula reads 0 as blank

I cannot replicate this behavior. My copy of Excel correctly distinguishes between "" and 0, and returns C1 when C1 contains 0. Can you upload a spreadsheet that exhibits this behavior?

Please Login or Register  to view this content.

Try this array formula
Use CTRL SHIFT ENTER instead of ENTER

I get it to return the 0

That is what he want

How do I get it to return 0

take a look at this simple file

I am not sure why it would work correctly in one workbook, and fail to work in another workbook.

One debugging step you might try is to use the Formula evaluate tool (https://support.office.com/en-us/art...6-a70aa409b8a7 ). This will allow you to step through the formula that isn't working and see what value it pulls up for C1 (which may be different from what is displayed, if cell formatting causes the display to be different from the actual value), which should allow you to see why it is returning FALSE for C1<>"". From this side of the internet, it seems obvious to me that C1 must contain "" or be blank in order for that logic statement to return FALSE, but this tool should allow you to see what it is returning.

My next debugging step, then, would be to look very carefully at C1. What is in C1?

Originally Posted by kfeo

Unfortunately, I can't upload the spreadsheet because it contains confidential info on it. I tried to copy it without the confidential info onto a new workbook but now the formula is working correctly for me on the new copy too. It is literally an exact copy. What would cause a formula to not work on one workbook but work properly on another? I've checked my calculation options and it's set to automatic. All of my other formulas are working properly. I'm at a loss...

Hi,

In that case it seems to me that establishing the problem will only be established by us seeing the actual workbook. Since you managed to get it working merely by copying then the problem seems to be something to do with your confidential data.

In cases like this I take a binary approach to establishing causation. i.e. Try copying it but with half the confidential data still in place and see what happens. If that's OK then repeat but with the other half of the confidential data which will presumably give you an error. So that tells you that the error is with the last set of copied data.

So iterate the whole process each time copying half the data you know causes the problem. You'll soon hone it down to a single line.