Subscriber Tim S. wrote about an oft-frustrating error in Excel. It’s not that an occasional error is aggravating. The problem is when you’re building a spreadsheet into which you’ll enter or paste data at a later time.
The calculations that try to use those cells that are currently blank, will often display Excel errors, such as the ones about which Tim wrote:
First let me say I love receiving this!!
Do you give tips on excel spreadsheets? I keep having "#VALUE!" appear in cells if and when the range of cells within the formula are blank. Is there a shortcut or tweak I can add to the formula equation in order to get rid of "#VALUE!" so it does not appear and yet the formula remains intact?
Attached is an example.
Since these kinds of frustrations often need quick fixes, I wrote back to Tim and pointed him to some solutions:
The easiest thing to do is to use either the isblank() test or the isnumber() test as part of an if. Then, depending on the test, either assign a 0 or calculate a formula.
There are some other ways to handle the examples below.
As you can see, if the cell is empty and you try to add its value in a formula, all is well. It works with the plus and with the sum functions.
However, if you put a space or letters (in this case, the word "space") into the cell, the simple addition fails with the #VALUE! error. On the other hand, you can solve an addition by using the Sum function to sum across the cells that may include both numbers and non-number values.
You can also test to see if the cell value is a number, and then use its value or some other value (here the number 0).
The easiest test takes advantage of the Sum function working with a single cell, as opposed to a range of cells (compare the cell formulas displayed in E7 and D7 in the example — these display the formula in the cell immediately above them).
As you can see, SUM(E3) returns a value of 0, which the formula uses successfully.
Addition is the simple example.
Now, let’s look at an example of using Sum(E7) in a formula, to be used as either the number in cell E7 or as 0, if there isn’t a number in E7. We want to multiply the quantity of items in inventory (in cell E7) by cost of the item (in cell M3, not shown) to figure out how much money we have tied up in inventory of that item (which we’ll store in cell E20).
It looks like the easiest way to use an unknown cell’s value in a calculation, especially if you want to avoid the #VALUE! error in spreadseheet rows that don’t have values yet, is to refer to that value using the SUM() function.