Ask Your Question
0

Need help with Libre functions

asked 2016-10-28 23:49:40 +0100

Calcasieu gravatar image

Thanks in advance for help. Here is what I am trying to do. If cells D2 through M2 are all empty then leave designated cell empty regardless of other circumstances. If cells D2 through M2 are not empty and the SUM of cells D2 through M2 is zero, then enter “OK”. If cells D2 through M2 are not empty and the SUM of cells D2 through M2 is not zero, then enter “Error”. When I enter =IF(D2:M2="","",IF(SUM(D2:M2)=0,"OK';"error"")) I get Err:508 Help is appreciated

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-10-29 00:52:43 +0100

Lupp gravatar image

Err:508 should mean "Parentheses not paired correctly. I cannot see this error in your formula.
However, it is syntactically erroneous in more than one points: There is a single apostrophe in one place where a qoutation mark should be and in another place there are two quotes where one should be. In addition I would recommend to consistently use the semicolon as parameter delimiter.

The more critical error is a semantic one. =IF(D2:M2=""; ...;...)is obviously meant as the condition "All the cells in the range D2:M2 are blank or have the empty string as result." You wrongly assume that an equation between an array (D2:M2) and a scalar ("") will perform an implicit conjunction (AND). This is not the case. The evaluator will try to pick an element of the array in "intersection mode" and, depending on the position of the expression (In what cell?), it will most probably fail.
To get the condition correct you may put AND(D2:M2="") in the position of the condition and enter the formula for evaluation in array mode (Ctrl+Shift+Enter). To avoid the explicit array mode you may use SUMPRODUCT(D2:M2="")=COLUMNS(D2:M2) or COUNTIF(D2:M2;"")=COLUMNS(D2:M2) or probably (if "not empty" actually is meant as "containing a number" and just one number in the range is sufficient (OR / disjunction instead of AND) COUNT(D2:M2) > 0would do. (Otherwise COUNT(D2:M2)=COLUMNS(D2:M2) again.)

edit flag offensive delete link more

Comments

I think you may have inverted the logic of the first condition. COUNTIF(D2:M2;"<>")=0 or COUNTA(D2:M2)=0 would fit the original intention, or COUNT(D2:M2)=0 if only numbers are to be considered.

mark_t gravatar imagemark_t ( 2016-10-29 01:47:44 +0100 )edit

Appreciate your input. Thanks

Calcasieu gravatar imageCalcasieu ( 2016-10-29 01:56:37 +0100 )edit
0

answered 2016-10-29 00:50:28 +0100

mark_t gravatar image

updated 2016-10-29 01:55:18 +0100

Use CONCAT to join the cells to check if all are empty. Also you have one single ' instead of " after OK, and two "" instead of " after error.

=IF(COUNTA(D2:M2)=0,"",IF(SUM(D2:M2)=0,"OK","error"))

or if only numbers are to be considered:-

=IF(COUNT(D2:M2)=0,"",IF(SUM(D2:M2)=0,"OK","error"))

You also have a mix of "," and ";" for argument separators. Depending on your language settings you might have to use ";".

Edit. Note if the cells contain text the sum might still equal zero and display OK.

Edited above to remove CONCAT function, as @Lupp pointed out that is only a recent addition and will not work in older versions of LibreOffice. Also probably a bad way to check all cells are blank.

edit flag offensive delete link more

Comments

The CONCAT function is very fresh: Only implemented in V5.2 (and higjher) and under discussion in the bugzilla site.
The semicolon is specified to be accepted in all the locales. Only the comma may cause problems in those locales using it as the decimal separator. In "anglophile" locales, however, the entered semicolons will automatically be converted. (This was introduced not too long ago for bad reasons.)

Lupp gravatar imageLupp ( 2016-10-29 00:55:11 +0100 )edit

That worked great. Thanks for your help.

Calcasieu gravatar imageCalcasieu ( 2016-10-29 01:56:17 +0100 )edit

I removed CONCAT, not only because its recently added but also it seemed to be a misuse of this function.

mark_t gravatar imagemark_t ( 2016-10-29 01:56:53 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-10-28 23:49:40 +0100

Seen: 90 times

Last updated: Oct 29 '16