I am running a complex IF equation in Excel 2003 (yes, the computers at work are that old too) and it is important to the equation that blank cells are valued at zero, which is, I understand, how excel reads blank cells. The cells in question are formatted to date ( *9/10/2014 ).
This equation is working just fine except for one cell, left blank, is not registering to the equation as zero and so I'm getting a false value when it should be true. I have checked the formatting, it is (as far as I can see) the same as the cells around it.
I can correct this problem by cutting and pasting from a blank cell above it, but I don't know why this is working. I need to know why in order to correct for it when I put the equation to use, to make sure that I am not getting false values.
24 Answers
A blank cell is not the same as zero in Excel. Excel recognizes blank as empty rather than a value. If you need a blank cell to be treated as zero in a formula, do something like this (for this example, I'll use A1 as the referenced cell):
if(isblank(a1),0,a1)Use this in place of a1 in your formula.
EDIT: You received several answers. Both address the "how to fix it", but neither really addresses the second part of your question.
Excel does recognize the difference between an explicit value of zero and the absence of a value. In some cases, the implications of a missing value in a formula don't make a difference. 3 + 0 is the same as 3 + nothing. When there is no logical difference in a computation, Excel will treat an empty cell as if it contained zero.
That's fine if that's what you intend. However, if the cells represent data, you normally want to distinguish between a value you entered as 0 and a value that is missing and should be something else or should be excluded. That's why it is good practice to not rely on Excel to guess how you want to use the cell, but rather to make it explicit.
At the time of this edit, there are several suggestions for forcing a blank to a value of zero. A simple method: prefix the cell reference with a pair of minuses:
--a1The double minuses will leave a numerical value unchanged, return a zero if it is blank, or an error if it is something else (like a space character).
As to why you have a cell that behaves differently, there could be a number of reasons. We would have to see the spreadsheet and how you are using the problem cell. However, it probably is not formatting. At least in later versions of Excel, even if the cell is formatted to something like text, if it is empty, it will be treated as zero for calculations. A more likely cause would be that the cell is not actually empty but contains something like a space character. If that is what's going on, none of the suggested fixes will work (a space is not a blank or a number). Try deleting the cell, which will remove any content that is not visible.
If you are curious about the contents, use string functions to see what it is. Len(a1) will tell you if it is empty.
5FORMULA:
=VALUE(...)
This formula will evaluate to 0 for blanks and valid values will not be altered.
3I had same issue and found the reason: cells seem empty but they were not- there were spaces in cells (its hard to see it though). So by just removing spaces (CTRL R: Replace " " with blank) solved the issue.
This seems to be a bug in Excel 2013.
It is possible to encounter a problem of a seemingly empty cell. When refereced reports it is not a number, not zero, not blank, not empty. Yet no matter how you reference the cell, you can not find what it contains.
These are the results I get when I try to reference the cell or see its contents:
• =IF(ISBLANK(a1),1,2) ==> returns a 2. (i.e. the cell is not blank).
• =IF(A1=0,0,1) ==> Returns a 1. (i.e. the cell does not contain zero).
• =A1*2 ==> Returns #VALUE! (i.e. the cell does not contain a number).
• =IF((A1)>1,1,0) ==> Returns a 1 (i.e. the cell contains a number greater than 1!).
• =IF(A+>9.99999999999999E+307,1,0) ==> Returns a 1. (i.e. the cell contains a very large number).
• ="X"&A1&"X" ==> Returns "XX". (i.e. The cell A1 is really empty.).
• =CELL("format",A1) ==> Returns "G". (i.e. the number format of the cell is General).
• =CELL("prefix",A1) ==> Returns "^". (i.e. the cell is center formatted for text and numbers).
• =CELL("type",A1) ==> Returns "I". (i.e. the cell contains text!).
• =VALUE(A1) ==> Returns #VALUE! (i.e. the call does not contain a text string which looks like a number).
• =LEN(A1) ==> Returns 0. (i.e. the length of any string is zero).
• =T(A1) ==> Returns nothing (i.e. it is returning a text string of zero length).
• =UNICODE(A1) ==> Returns #VALUE! (i.e. the cell does not contain a string).
For me this happens when I have imported data from another application. (Telekurs Infinacials in my case). The function =If(A1=0,0,1) reports that the cell a1 is not zero. The function =A1+1 produces an #VALUE! error. Several other kind of maths on the empty cell produces a #VALUE! result.
The logical connclusion is that the cell does not contain a number. However this is contradicted by the formula =if(A1>99,1,1), which implies that the cell contains a high number.
I have eliminated the possibility that the contents of the cell are hidden, or that the cell contains a space.
(You can test this by for example putting in a formula like ="X"&A1&""X", where A1 is the cell you suspect is not empty. If the formula returns "XX", then the cell does not contain any number, nor any text. Otherwise there would be a space, number or text containes beteween the two "X"s.).
The cell seems not to be blank either, because if you use the formula =IF(ISBLANK(a1),1,2), it returns a 2.
So the problem is that Excel is logging the cell as not blank, not zero, yet containing neither a number nor a character.
Reformatting the cell to a number format or to text does not change anything. Putting a1 in brackets, (a1), does not return any value (neither zero, nor a blank, nor any character or space).
The only solution is to click on the cell then click the input bar and press enter. The problem vanishes and it becomes a blank cell, which also returns a value of zero.
This solution is fine if you only have one cell to deal with, but if you have a spreadsheet with thousands of such cells, its a problem. Therefore in a spreadsheet which contains numbers and in which you want the "apparently" empty cell to be calculated as zero, you need to use the iferror function as follows:
Old formula: =A1*2
New Formula: =IFERROR((A1*1),0)*2
