Sep 17 2020 12:16 PM
I have two columns of numbers. (Debits & credits). I need to net these columns using a simple x-y formula. For every row one cell has a positive number and cell that is blank. I have one workbook that is returning #value instead of a number. If I enter a 0 in the blank cell I get a result. How do I get excel to register the blank cells as 0's?
Sep 17 2020 12:54 PM
In calculations blank cell is considered as one having zero value, other words returns 0. Perhaps you have not blank cells, but cells with empty string, when you have an error. Usually formulas return empty string instead of blank, like =IF(A1>B1, A1*10, "")
As workaround instead of
=A1+B1
you may use
=SUM(A1:B1) or =SUM(A1,B1)
SUM() ignores all texts.
Sep 17 2020 10:32 PM - edited Sep 17 2020 10:40 PM
@samd685 ,,,
The #VALUE! error is a common issue, and possible reasons are,,,
Fixing needs entering the right kind of value.
Below I've shown a sample data finding how to calculate RUNNING TOTAL while handling Debit & Credit amount.
N.B.
Formula In cell E81 =(B81+D80)-C81 , encounters with #VALUE error in Cell E84, reason is that the cell C84 has TEXT value .
Now to trap the error I've used correct formula in cell D81 is,,
=IFERROR(B81+D80-C81,D80)