# Sum empty cell returns #value (update)

Not sure if this has been ask before

I'm trying to have SUM of 3 components =SUM(H211-F211)*E211 when there's a component (square) that is empty it returns #value

How do I prevent this from happening?

Thank you for helping in advance....

Updated.... i tried =(H211-F211)E211 instead of =sum(H211-F211)E211 see attachment of error I still get #value error when I use formula C:\fakepath\value error Libreoffice 01-02-2019.JPG

/////////////////////////// updated

I found the solution .... by experimenting =IFERROR(SUM(H211-F211)*E211,0)

this replaced #value! (error) with 0.00 which i needed

edit retag close merge delete

=SUM(H211-F211)*E211 isn't a sum of three components, but the product of a difference with a third operatand.
The usage of SUM() is just an obfuscation in this case. You should better use =(H211-F211)*E211.
Check your "empty cells". They surely aren't empty, but contain formulae returning zero-length strings or whitespace - or the whitespace as direct content.

( 2019-01-03 00:56:36 +0200 )edit

Sort by » oldest newest most voted

(What do you mean by "square"?)

The SUM() function ignores text, but neither the subtraction operator - nor the multiplication operator * do so. And empty text (an empty string returned by a formula) is text.

If an operand of an arithmetic operation is text and cannot be automatically converted to a number, you get the error alert #VALUE!. The empty text is not convertible. A reference to a BLANK cell ist converted to 0.

Text not being empty, but consisting of whitespace characters only also is not convertible to number.

more

## Stats

Seen: 688 times

Last updated: Jan 03 '19