Returning Zero when a Referenced Cell is Blank. By Allen Wyatt. (last updated July 11, 2017) If you have a formula in a worksheet, and the cell referenced by the formula is blank, then the formula still returns a zero value. For instance, if you have the formula =A3, then the formula returns the contents of cell A3, unless cell A3 is blank.
Re: Formula result will only display as 0 (zero) 1 Try: Hit F9 to recalculate. 2 Select the cell which is giving you 0, On “Formula Tab” click “Trace Precedents” see if the arrows go to the right cells. 3 Make sure your precedent cells are actually numbers and not text.
This is what my research found. Whenever they type a simple formula in their spreadsheet, the value keeps returning a 0. When you type something like 10/2 the value returns as 5. Once the = sign is used (=10/2), the value is 0. This also happens when we try to reference other cells (i. e. =k12/1500 will also return a value of 0.).
What happens if the return value is 0 in Excel?
If your return value is 0, the cell will be shown as an empty string. Further calculations might be difficult: For example, if you want to add a number to the return value. Referring directly to the cell, for example =F6+G6 leads to the #VALUE error message as shown above.
Why won’t excel let me enter zeros?
So you’ve typed 00198 and Excel has completely ignored the 00 and only entered the 198 into the cell. This is because Excel sees the zeros as insignificant and as a result drops them. Frustrating isn’t it? You may have tried this for part numbers, post codes, item codes and mobile numbers .
Intuitively you know that numbers are supposed to be automatically formatted by Excel to remove leading zeros. So, the leading zeros mean that these cells were probably formatted so that the leading zeros show. The problem is, there are more ways than one in which this could have been done.
This begs the query “How to prevent digits from being changed to zero in Excel?”
To prevents digits from being changed to zero, type a single quotation mark before you enter the number. To do this, select a blank cell, type a single quotation mark (‘), and then enter the number. All digits are displayed in the cell. Format the cells.
If you want to remove two leading zeros from cell A1, then just drag the formula right by one cell. This will further reduce the number of zeros 1. Similarly, to remove three zeros, drag the formula right by one cell again. Repeat this for the number of zeros you want to remove.