empty cell + 5 = #value or equals 5

[christhedonstar]

Hi,

Why is it that when I enter the formula =B5 + 5 I get the result #value. If I enter =b6 + 5 I get the result 5, when both the cells are empty and the format is the same.

Why is it when I hit f2 enter on cell B5 and then use the formula =B5 + 5 I get the result 5.

Is there anyway of me getting the result 5 using the exact formula above without having to hit f2 enter on B5?

Thanks,

Chris

 

[Bong]

when both the cells are empty and the format is the same.

I would have to surmise that both cells are not empty.

_________________
Bob Rashkin

 

[christhedonstar]

Not sure I understand what you mean?

 

[Bong]

It seems like the behavior that would pertain if B5 had a space (" ").

_________________
Bob Rashkin

 

[christhedonstar]

=isblank(b5) returns true though... So I don't think that is the case.

 

[christhedonstar]

ok - It looks like what has happened is that the values were copied and pasted from cells that contained "". so even though you don't see anything in the Fx bar it is still not blank? When you press f2 enter then the data comes up as blank. Is there a way around this? Or do I have to build a macro to check for the empty cells? There isn't an Excel way around this issue?

Thanks,

Chris

 

[Bong]

Maybe you should look for what should be in the cell if it isn't "blank". That is, what data can you expect to be there if you want to proceed with your function. I'm thinking maybe you want it to be a number? In that case maybe something like =if(isnumber(b5),b5+5," ")

_________________
Bob Rashkin

 

[firefytr]

Null values count as a zero-length text-based value. So if you have ="" in a cell, even though the length is zero. As Bob points out, if this may be the case, you should think about performing your test from the ISNUMBER() philosophy.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[cresbydotcom]

I end up using formulae like =if(C5="","",C5)" because it covers text or numbers
or the VBA equivalent in a macro.

ISNUMBER pre-supposes arithmatic, as per original question.

there is a tide in the affairs of man that you Canute ignore.................