Excel thinks cell is a "0" when it is anything but.

[Pointy Teeth]

I have a column of data that returns positive-only numbers, yet when I chart that data, many zeros are returned. I did a find (CTRL+F) in the column and searched for "0", and sure enough, excel thinks a cell is a zero even when the FIND ALL shows the value of that cell as a positive value.

for example, in Cell Q20 I have this formula "=IF(P20<=$S$43,P20,NA())", which is currently returning "8". FYI, I'm using "NA()" so the chart will ignore those and not plot them.
It's not a rounding error.
I've verified the formatting and I have tried GENERAL as well as NUMBER, with no change in behavior.
I've even wrapped the formula in the VALUE() function, just in case.
I've evaluated the formula, and at no point does a zero even show up.
I've even used the Error Checking feature which returns no errors.

Does anyone know why this happens and what the fix is?
I appreciate any insight.

Thank you~

 

[Fluff]

Make sure that the Look in: box is set to Values not Formulas

 

[Pointy Teeth]

Make sure that the Look in: box is set to Values not Formulas

thank you. Even when the "Look In:" box is set to 'Values', it still finds that cell (and many others) as a zero.

 

[Fluff]

It shouldn't. When you look at the results you posted it is finding the 0 in the cell references.

 

[Pointy Teeth]

It shouldn't. When you look at the results you posted it is finding the 0 in the cell references.

Agreed; it shouldn't. When I check that cell with a simple formula, the formula doesn't treat the cell as a zero, it treats it as '8'. however, both the Chart and the Find and Replace tool are treating it as '0'.

 

[Joe4]

Out of curiosity, what do these formulas return, if you enter them in any blank cell on your sheet:
=ISNUMBER(P20)
=ISNUMBER(P30)

 

[Pointy Teeth]

The ISNUMBER functions each return TRUE.
Interestingly, when I first hit enter after typing the formula, a zero appears for a couple seconds before the formula returns TRUE.

This workbook had built built upon a couple of times and I just decided to rebuild this from a new file. The zero problem appears to have corrected itself. Starting from a new file did the trick. Not sure if there was corrupted data or some other thing lurking in the background. Either way, after rebuilding it, am no longer experiencing a problem.

Thanks for the speedy responses!!

 

[Joe4]

The ISNUMBER functions each return TRUE.
Interestingly, when I first hit enter after typing the formula, a zero appears for a couple seconds before the formula returns TRUE.

This workbook had built built upon a couple of times and I just decided to rebuild this from a new file. The zero problem appears to have corrected itself. Starting from a new file did the trick. Not sure if there was corrupted data or some other thing lurking in the background. Either way, after rebuilding it, am no longer experiencing a problem.

Thanks for the speedy responses!!

Yeah, it sounds like it might have been some corruption or something else going on.
Glad you were able to get it working!