Ask Your Question

LibreOffice 6 update - Calc sumproduct(abs(range)) not working [closed]

asked 2018-07-05 12:59:12 +0200

Alex314159 gravatar image


I regularly use this kind of call: =SUMPRODUCT(ABS(Intraday.K$1:K$99885))

which before I upgraded to version 6 (and in Excel) gives you the sum of absolute values. It now gives #VALUE. Is this a bug / is there a workaround? Thanks, regards,

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-07-05 17:43:34.619497


Unexpectedly, to answer this, one needs not only the formula, but also the contents of Intraday.K$1:K$99885, or a sample document. :-)

First, I'd check if some of the cells contain text instead of numbers, and in OptionsCalcFormulaDetailed Calculation Settings, Custom is set, with Conversion from text to number generating $VALUE error.

Mike Kaganski gravatar imageMike Kaganski ( 2018-07-05 13:17:27 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-07-05 14:12:09 +0200

erAck gravatar image

Ensure that the range does not contain a Text type (string) cell, even if it may look like a number. You can adjust the on-the-fly text conversion settings under Tools -> Options -> Calc -> Formula -> Detailed Calculation Settings -> Custom -> Details -> Contents to Numbers -> Conversion from text to number, but I'd rather suggest to convert the data instead, see this FAQ.

edit flag offensive delete link more


my apologies - there was a cell with text in, which didn't seem to bother previous versions but broke after update. Thank you very much!

Alex314159 gravatar imageAlex314159 ( 2018-07-05 14:55:54 +0200 )edit

Some earlier versions (5.3 or so) erroneously did not propagate an error in array/matrix context (which SUMPRODUCT forces to its arguments) under some circumstances, this might had been the reason why you didn't see it before.

erAck gravatar imageerAck ( 2018-07-05 17:42:57 +0200 )edit

Question Tools

1 follower


Asked: 2018-07-05 12:59:12 +0200

Seen: 143 times

Last updated: Jul 05 '18