Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 12 Jan 2019 20:04:55 +0100how to get around white space problemhttps://ask.libreoffice.org/en/question/179033/how-to-get-around-white-space-problem/Here is the formula in column L:
=IF(ISBLANK(D5)," ",(G5+K5)+(G5+K5)*$M$3)
Column D is units; column G is the extension of E*F (qnty*cost); column K is tax; M3 is markup rate. Not every line item, or row, have tax; so K is sometimes white space.
This formula works in Open Office, Google spreadsheets, etc. But in Libre the answer is #VALUE!
I have replaced the commas with semi-colons but the program changes them back to commas.
I have replaced the blank (" ") with "0" (without quotes) but the answer is #VALUE!
I have added parenthesis to encompass the entire third set, as in ((G5+K5)+(G5+K5)*$M$3)).
Any help here? Thanks in advance.Fri, 11 Jan 2019 22:29:29 +0100https://ask.libreoffice.org/en/question/179033/how-to-get-around-white-space-problem/Comment by cominus for <p>Here is the formula in column L:
=IF(ISBLANK(D5)," ",(G5+K5)+(G5+K5)*$M$3)</p>
<p>Column D is units; column G is the extension of E<em>F (qnty</em>cost); column K is tax; M3 is markup rate. Not every line item, or row, have tax; so K is sometimes white space.</p>
<p>This formula works in Open Office, Google spreadsheets, etc. But in Libre the answer is #VALUE!
I have replaced the commas with semi-colons but the program changes them back to commas.
I have replaced the blank (" ") with "0" (without quotes) but the answer is #VALUE!
I have added parenthesis to encompass the entire third set, as in ((G5+K5)+(G5+K5)*$M$3)).
Any help here? Thanks in advance.</p>
https://ask.libreoffice.org/en/question/179033/how-to-get-around-white-space-problem/?comment=179118#post-id-179118**Lupp** - there are whitespace characters. However, OpenOffice and Google treat them as NULL with the same value as 0. I need to figure out how to get Libre to treat this as zero without returning a zero if D5 is NULL or whitespace. Thanks.
**SM_Riga** - SUM(G5;K5)+SUM(G5;K5)*$M$3 returns 0. Furthermore, not looking for SUM(G5;K5) looking for G5 + K5. AS for this being an ISBLANK issue, sometimes column K is blank because there is no tax for the line item; but I see what you say, so I renamed the problem to white space. Thanks.
**JohnSUN** - SUM(G5;K5)*(1+N($M$3)) returns 0. Furthermore, not looking for SUM(G5;K5) looking for G5 + K5. Thanks.Sat, 12 Jan 2019 20:04:55 +0100https://ask.libreoffice.org/en/question/179033/how-to-get-around-white-space-problem/?comment=179118#post-id-179118Comment by JohnSUN for <p>Here is the formula in column L:
=IF(ISBLANK(D5)," ",(G5+K5)+(G5+K5)*$M$3)</p>
<p>Column D is units; column G is the extension of E<em>F (qnty</em>cost); column K is tax; M3 is markup rate. Not every line item, or row, have tax; so K is sometimes white space.</p>
<p>This formula works in Open Office, Google spreadsheets, etc. But in Libre the answer is #VALUE!
I have replaced the commas with semi-colons but the program changes them back to commas.
I have replaced the blank (" ") with "0" (without quotes) but the answer is #VALUE!
I have added parenthesis to encompass the entire third set, as in ((G5+K5)+(G5+K5)*$M$3)).
Any help here? Thanks in advance.</p>
https://ask.libreoffice.org/en/question/179033/how-to-get-around-white-space-problem/?comment=179084#post-id-179084Expression `SUM(G5;K5)*(1+N($M$3))` should do the trickSat, 12 Jan 2019 15:33:30 +0100https://ask.libreoffice.org/en/question/179033/how-to-get-around-white-space-problem/?comment=179084#post-id-179084Comment by SM_Riga for <p>Here is the formula in column L:
=IF(ISBLANK(D5)," ",(G5+K5)+(G5+K5)*$M$3)</p>
<p>Column D is units; column G is the extension of E<em>F (qnty</em>cost); column K is tax; M3 is markup rate. Not every line item, or row, have tax; so K is sometimes white space.</p>
<p>This formula works in Open Office, Google spreadsheets, etc. But in Libre the answer is #VALUE!
I have replaced the commas with semi-colons but the program changes them back to commas.
I have replaced the blank (" ") with "0" (without quotes) but the answer is #VALUE!
I have added parenthesis to encompass the entire third set, as in ((G5+K5)+(G5+K5)*$M$3)).
Any help here? Thanks in advance.</p>
https://ask.libreoffice.org/en/question/179033/how-to-get-around-white-space-problem/?comment=179037#post-id-179037Why do state that it is `ISBLANK` issue? Try to divide formula to pieces and test each of them to determine #VALUE! cause. ISBLANK(D5) should return 1/0 or TRUE/FALSE. Try to change `(G5+K5)+(G5+K5)*$M$3` to `SUM(G5;K5)+SUM(G5;K5)*$M$3` - this will ignore non-numeric cells. Btw, `" "` is a whitespace, not pseudo-blank value. I vote for @Lupp comment - the issue shoud be in `(G5+K5)+(G5+K5)*$M$3` part and some text string passed in it.Fri, 11 Jan 2019 23:12:52 +0100https://ask.libreoffice.org/en/question/179033/how-to-get-around-white-space-problem/?comment=179037#post-id-179037Comment by Lupp for <p>Here is the formula in column L:
=IF(ISBLANK(D5)," ",(G5+K5)+(G5+K5)*$M$3)</p>
<p>Column D is units; column G is the extension of E<em>F (qnty</em>cost); column K is tax; M3 is markup rate. Not every line item, or row, have tax; so K is sometimes white space.</p>
<p>This formula works in Open Office, Google spreadsheets, etc. But in Libre the answer is #VALUE!
I have replaced the commas with semi-colons but the program changes them back to commas.
I have replaced the blank (" ") with "0" (without quotes) but the answer is #VALUE!
I have added parenthesis to encompass the entire third set, as in ((G5+K5)+(G5+K5)*$M$3)).
Any help here? Thanks in advance.</p>
https://ask.libreoffice.org/en/question/179033/how-to-get-around-white-space-problem/?comment=179035#post-id-179035Check G5, K5, and $M$3 for whitespace characters.Fri, 11 Jan 2019 22:37:40 +0100https://ask.libreoffice.org/en/question/179033/how-to-get-around-white-space-problem/?comment=179035#post-id-179035