how to get around white space problem

asked 2019-01-11 22:29:29 +0100

cominus gravatar image

updated 2019-01-12 21:14:10 +0100

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 EF (qntycost); 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.

edit retag flag offensive close merge delete



Check G5, K5, and $M$3 for whitespace characters.

Lupp gravatar imageLupp ( 2019-01-11 22:37:40 +0100 )edit

Why 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.

SM_Riga gravatar imageSM_Riga ( 2019-01-11 23:12:52 +0100 )edit

Expression SUM(G5;K5)*(1+N($M$3)) should do the trick

JohnSUN gravatar imageJohnSUN ( 2019-01-12 15:33:30 +0100 )edit

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.

cominus gravatar imagecominus ( 2019-01-12 20:04:55 +0100 )edit