[Solved] Unexpected result for SUM with multiplication

[peanutaxis]

Hi,

Anyone know what it going on with the sum plus multiplication in the attached? I can't work it out.

Thanks,
p

Calc.jpg (28.77 KiB) Viewed 3680 times

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (robleyd, Moderator).

[robleyd]

Code: Select all

=SUM(E5:E14)*2

[Tutorial] Order of Operations in Calc

[peanutaxis]

Thanks, but why does it come up with the result of 4?

[Zizi64]

Thanks, but why does it come up with the result of 4?

I suppose that your formula will be interpreted as

Code: Select all

=SUM(E5;E14*2)

Read: two operand only (but not a range), and a semicolon)

[Hagar Delest]

=SUM(E5;E14*2) would have returned 6 (2 + 2 x 2).

There was a similar topic not long ago. The syntax is not the one expected by AOO in the case of an operation on the range address. It seems that AOO/LO take the last cell to proceed with the operation.

I tag the topic as solved since it was a mere syntax error.

[MrProgrammer]

Thanks, but why does =SUM(E5:E14*2) come up with the result of 4?

You asked Calc to perform the multiplication first. That operator multiplies one multiplicand by one multiplier. Your multiplier is 2. But your multiplicand is a range of ten cells. How does Calc know which one of them to use? It picks the one in the same row as your formula in G14. So it uses E14, which has a value of 2. 2 times 2 is 4. The other values in E5 through E13 were ignored. The single value 4 was passed to SUM. The sum of a single number is just that number. Thus the formula result is 4.

=SUM(E5:E14)*2

SUM(number1; number2; ... number30)
• number1 to number30 are up to 30 numbers or ranges of numbers whose sum is to be calculated.

In this formula the SUM function is performed first because Calc can't multiply numbers until it knows their values. Unlike the multiplication operator, the SUM function does support a range. The sum of E5 through E14 is 20. 20 times 2 is 40, the result of the formula.

=SUM(E5;E14*2) would have returned 6 (2 + 2 x 2).

Yes, if the formula uses a semicolon instead of a colon. The multiplication operator is performed first because the SUM function needs to know what numbers are to be summed. E14 times 2 is 4. The SUM function then adds number1 (E5 with value 2) to number2 (that product with value 4). The result of the formula is 6.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[cwolan]

Thanks, but why does =SUM(E5:E14*2) come up with the result of 4?

You asked Calc to perform the multiplication first. That operator multiplies one multiplicand by one multiplier. Your multiplier is 2. But your multiplicand is a range of ten cells. How does Calc know which one of them to use? It picks the one in the same row as your formula in G14. So it uses E14, which has a value of 2. 2 times 2 is 4. The other values in E5 through E13 were ignored. The single value 4 was passed to SUM. The sum of a single number is just that number. Thus the formula result is 4.

@MrProgrammer
Is that a case of intersection (mentioned e.g. in AOO Help)?
⠀
Example:
⠀

interEXA.png (53.21 KiB) Viewed 3462 times

[Hagar Delest]

But your multiplicand is a range of ten cells. How does Calc know which one of them to use?

User not aware of the syntax I guess (would not be the first ime).

It picks the one in the same row as your formula in G14.

Ah, that was it, thanks. Same row that looks like the default intersection mentioned by cwolan when there is nothing to define an intersection.

[Alex1]

If you enter your original formula as an array formula, i.e. with Ctrl+Shift+Enter, it will show 40 as the result.

[MrProgrammer]

Is that a case of intersection (mentioned e.g. in AOO Help)?

Same row that looks like the default intersection mentioned by cwolan when there is nothing to define an intersection.

Yes. It is explained in Using Arrays → Functions not expecting array parameters → If it is a range → For a single row or a single column range. In this context, the multiplication operator can be regarded as a function which does not expect array parameters. E5:E14 is a single column range, so the intersection is made with the current cell's row. If the intersection of a range and the current cell's row/column is null, or if the intersection is more than one cell, the result is #VALUE!. Otherwise the intersection is a single cell whose value is the value of the range in non-array context.

If you enter your original formula as an array formula, i.e. with Ctrl+Shift+Enter, it will show 40 as the result.

Fine for you and me. I knew that but didn't mention it because array formulas are an advanced topic. I do not recommend that beginners use them since they can be difficult to understand and have many quirks and issues, some of which are mentioned at the bottom of the link above. I know of cases where array formulas behave strangely and have helped people in topics who have bumped into them. The best way for a beginner to perform this particular calculation is with =SUM(E5:E14)*2, not an array formula. The question in this topic suggests to me that peanutaxis does not understand many of the very basic Calc concepts (like when ranges are allowed in formulas) and doesn't need any additional complexity now.

I will concede that there are some situations where an array formula is the only reasonable way to perform a calculation, but this is not one of those. Multiplication distributes over the addition from SUM and can be moved to the outside. If the operator inside the function were addition instead of multiplication =SUM(E5:E14+2) would either need an array formula, =SUMPRODUCT(E5:E14+2), or SUM() and a helper column with the incremented values. For a beginner I would suggest the latter, even though I wouldn't normally use a helper column myself. This is a case of Do as I say, not as I do. I didn't know, until creating the link, that the original appearance of that phrase is the New Testament.