Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 03 Jan 2019 00:59:45 +0100Sum empty cell returns #value (update)https://ask.libreoffice.org/en/question/178080/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](/upfiles/15464740327500592.jpg)
///////////////////////////
updated
I found the solution .... by experimenting
=IFERROR(SUM(H211-F211)*E211,0)
this replaced #value! (error) with 0.00 which i neededThu, 03 Jan 2019 00:22:41 +0100https://ask.libreoffice.org/en/question/178080/sum-empty-cell-returns-value-update/Comment by Lupp for <p>Not sure if this has been ask before</p>
<p>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</p>
<p>How do I prevent this from happening?</p>
<p>Thank you for helping in advance....</p>
<p>Updated....
i tried =(H211-F211)<em>E211 instead of =sum(H211-F211)</em>E211
see attachment of error
I still get #value error when I use formula <a href="/upfiles/15464740327500592.jpg">C:\fakepath\value error Libreoffice 01-02-2019.JPG</a></p>
<p>///////////////////////////
updated</p>
<p>I found the solution .... by experimenting
=IFERROR(SUM(H211-F211)*E211,0)</p>
<p>this replaced #value! (error) with 0.00 which i needed</p>
https://ask.libreoffice.org/en/question/178080/sum-empty-cell-returns-value-update/?comment=178085#post-id-178085`=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.Thu, 03 Jan 2019 00:56:36 +0100https://ask.libreoffice.org/en/question/178080/sum-empty-cell-returns-value-update/?comment=178085#post-id-178085Answer by Lupp for <p>Not sure if this has been ask before</p>
<p>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</p>
<p>How do I prevent this from happening?</p>
<p>Thank you for helping in advance....</p>
<p>Updated....
i tried =(H211-F211)<em>E211 instead of =sum(H211-F211)</em>E211
see attachment of error
I still get #value error when I use formula <a href="/upfiles/15464740327500592.jpg">C:\fakepath\value error Libreoffice 01-02-2019.JPG</a></p>
<p>///////////////////////////
updated</p>
<p>I found the solution .... by experimenting
=IFERROR(SUM(H211-F211)*E211,0)</p>
<p>this replaced #value! (error) with 0.00 which i needed</p>
https://ask.libreoffice.org/en/question/178080/sum-empty-cell-returns-value-update/?answer=178086#post-id-178086(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.Thu, 03 Jan 2019 00:59:45 +0100https://ask.libreoffice.org/en/question/178080/sum-empty-cell-returns-value-update/?answer=178086#post-id-178086