add/subtract formula account empty rows

[Manolocs]

Columns A, B and C have search formula to find in a table the element in B1.
column D is a simple add/subtract formula.
Problem when the row is empty "", the formula got Error.
Is there a way to use this formula and skip the empty row and preserve the last balance?
Thanks in advance.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD]ERROR[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Select Product = [/TD]
[TD]pen[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Product[/TD]
[TD]Amount[/TD]
[TD]Tax[/TD]
[TD]Balance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]pen[/TD]
[TD]10[/TD]
[TD].10[/TD]
[TD]=d2+B3-C3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=d3+B4-C4[/TD]
[TD]#VALUE][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]pen[/TD]
[TD][/TD]
[TD][/TD]
[TD]=d4+B5-C5[/TD]
[TD]#VALUE][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#VALUE][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]pen[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#VALUE][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#VALUE][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#VALUE][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]pen[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Nutster]

You are referencing cell D3, which contains a word, "Balance". This text cannot be converted to a number, so cannot be included in a mathematical operation. This is what generates the error message #VALUE because it is trying to subtract non-convertible text. Please review your formulas to make sure they are referencing the cells you actually want to work on.

 

[Manolocs]

sorry was my mistake... I added rows and did not change the formula....
but the formula is correct my question is in case of empty row how can I calculate using this formula.
I can not edit the table.

 

[Nutster]

You can use the IFERROR(x, y) Excel function to handle error returns. If the first argument does not give an error, then it returns that value. If the first argument gives an error, return the second result instead.

=IFERROR(D2+B3-C3, D2)

This will calculate D2+B3-C3. If that results in an error, usually because B3 or C3 contains non-convertible text, then it just reports D2 (the second argument). Drag this formula down with the cell button or copy and paste to the other cells in D column.