Having trouble trying to sum absolute values

[Macrohard]

I’m coming at this as a complete spreadsheet newbie.

This is a screenshot of what I’m trying to do, using a formula that I’ve been told should allow me to accomplish what I want to: https://i.imgur.com/B3tka4L.png

The cell in the top left (in blue) is B3, the bottom right (in red) is C15. What I’m trying to do is to get the result of the following formula: “|(B3 - C3)| + |(B4 - C4)| + |(B5 - C5)| … |(B15 - C15)|” in other words, I want to subtract C3 from B3, take the absolute value of that and add it to the absolute value of C4 subtracted from B4, add it to the absolute value of C5 subtracted from B5, and so on up to row 15.* I want one cell that gives me the sum of the absolute value of all the sums.

As far as I’ve been able to discern from reading whatever I can find and asking around, the formula in the screenshot should do that. But, as you can see, I get a #VALUE! error. More specifically, it seems that Calc has a problem with the numbers in column B and only the numbers in column B, despite those being exactly the same as the numbers in column C, albeit in a different order.

My first question is - is this formula correct? Will it perform the calculation that I want it to? The second question is why am I getting this error message? I’ve tried typing the formula in by hand, copy & pasting it, and using the Function Wizard to build it from ready-made components, and all give exactly the same result.

*For now. I’m eventually going to want to input hundreds of figures.

[erAck]

Seeing the screenshot with the Function Wizard you’re almost there, just enable the Array checkbox before you OK. Or alternatively edit the formula, append a blank and remove it again (to mark the text modified), and close it with Shift+Ctrl+Enter instead of Enter, which creates an array formula.

Background and explanation: The minus operator (and ABS() as well) expects scalar parameters. Feeding that a range in non-array context attempts to create an implicit intersection of once cell of the range(s) and the formula cell position (which in your case there is no intersection hence the error). In array mode the ranges are converted to arrays if a scalar parameter is expected and the operations are performed as an iteration over all values of the argument.

[Macrohard]

That’s worked. Thank you very much.

[mariosv]

Please, if the answer solves the question click .