[Solved] Unexpected result from =SUM((I3:I14)/12)

[Dronz]

I notice that the Calc formula =SUM(I3:I14)/12 produces what I want/expect (the average of 12 cells - the sum of 12 cells, divided by 12), but: =SUM((I3:I14)/12) produces a strange smaller number (it seems to divide by 12x12, not by 12) - does anyone here understand why?
I notice that =SUM((I3:I14)) produces the full total of the 12 cells.

[FJCC]

Code: Select all

=SUM((I3:I14)/12)

throws an error in OpenOffice. Are you using LibreOffice?
It makes sense that the formula doesn't work because (I3:I14)/12 isn't valid syntax in a regular formula. You can make it work by using an array formula. To do that, type in the formula and finish by pressing CTRL + Shift + Enter. But that seems like a needless complication instead of putting the 12 outside the parentheses of the SUM function.

[Dronz]

=SUM((E3:E14)/12) does the same thing for me in both OpenOffice Calc, and LibreOffice Calc. No error. Though my OpenOffice version is 4.1.7 - maybe it got changed to be an error in later versions?

I see though that =SUM(SUM(E3:E14)/12) gives the expected value.

I guess I don't really get what the "SUM" part does, exactly. Maybe if I studied the documentation about its meaning and syntax, it would make more sense.

[FJCC]

The SUM() function causes the values in the range E3:E14 to be added together. The outer SUM() in SUM(SUM(E3:E14)/12)has to effect because it gets the single value produced by SUM(E3:E14)/12.

[Dronz]

Ok, though in practice, that redundant syntax does change the outcome somehow.

[karolus]

There are 12 cells in E3:E14!?
you maybe want to use:

Code: Select all

=AVERAGE(E3:E14)

[MrProgrammer]

=SUM((I3:I14)/12) produces a strange smaller number … does anyone here understand why?

Read my explanation in this topic. There the formula was =SUM(E5:E14*2), but I suspect that your situation is the same. I have two posts in that topic. The important one is the first of them.
[Solved] Unexpected result for SUM with multiplication

If you need any additional assistance attach a spreadsheet demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). Explain which cell in your attachment has an unexpected value, then tell us what you think the value should be and why. I will not help further unless you attach a spreadsheet document with an explanation.

=SUM((E3:E14)/12) does the same thing …

You said =SUM((I3:I14)/12) in your first post. I am ignoring any discussion about E3:E14.

=SUM((I3:I14)/12) throws an error in OpenOffice.

You are probably getting a #VALUE! error. To show Dronz' situation (no error, but unexpected value) put the formula in any of the rows from 3 to 14 and not in column I. Then the intersection (explained in my link) will be a single cell and Calc can divide it by 12.

You maybe want to use: =AVERAGE(E3:E14)

=AVERAGE(I3:I14) might be better than =SUM(I3:I14)/12 because the intent is clearer. Using AVERAGE, it's not necessary to count the cells to know that there are 12 of them. The AVERAGE function averages the numbers in those cells. Empty cells and text are ignored. If Dronz really wants to always divide by 12, even when some of the cells are empty, I would use =SUM(I3:I14)/ROWS(I3:I14) to avoid counting the cells.

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.

[Rizwan Akhtar]

=SUM(I3:I14)/12 sums all cells, then divides by 12 (the average).
=SUM((I3:I14)/12) divides each cell by 12 first, then sums effectively dividing the total by 12 twice.
Use =AVERAGE(I3:I14) or the first formula for the average.

[MrProgrammer]

=SUM((I3:I14)/12) divides each cell by 12 first, then sums …

Then =SUM((I3:I14)/12) would be the same as =SUM(I3:I14)/12.
In high school mathematics we learn that the distributive property of division over addition says that dividing each cell by 12 and summing

• I3/12 + I4/12 + I5/12 + I6/12 + I7/12 + I8/12 + I9/12 + I10/12 + I11/12 + I12/12 + I13/12 + I14/12

is equal to summing the cells and dividing the result by 12

• I3+I4+I5+I6+I7+I8+I9+I10+I11+I12+I13+I14 divided by 12

… effectively dividing the total by 12 twice.

This is wrong. =SUM((I3:I14)/12) does not divide by 12 twice. The unexpected value is explained in the first link in my first post above.

[Lupp]

I notice that the Calc formula =SUM(I3:I14)/12 produces what I want/expect (the average of 12 cells - the sum of 12 cells, divided by 12), ...

You needn't count the elements extra. There is the AVERAGE() function.

but: =SUM((I3:I14)/12) produces a strange smaller number (it seems to divide by 12x12, not by 12) ...

I would think this is impossible. See attached example, cell I17.

... - does anyone here understand why?

Yes.
I didn't study every post in this thread thoroughly.
Anyway the attached example contains the needed explanations. It was made with AOO 4.1.7.
If your version shows different results, it is buggy.

aoo_113108_AverageRelated.ods

(14.63 KiB) Downloaded 11 times