Stonehenge wrote: ↑Wed May 01, 2024 7:22 pm
Where can I read an explanation as to why this works and why my original formula returned the other answer?
The formula did exactly what you asked.
=SUM(($B$1:B4)/($A$1:A4)) divided two ranges, then passed the quotient to the SUM function. Using extra parentheses to group terms is good if you are unsure of formula usage, but they were unnecessary here since : is evaluated before /. You could have written
=SUM($B$1:B4/$A$1:A4). Please refer to my
202405011627.ods attachment. Note that I have
Hits in column A and
AtBat in Column B so the Hits/AtBat ratio, say 2 and 4 for the first set, can be written in the natural order 2/4 for the quotient.
Column C shows the division of the two ranges. For the last set, C6 shows a 4-row range being divided by another 4-row range:
=A$3:A6/B$3:B6. The Calc's division operator divides
one number, the numerator, by
one other number, the denominator. But you have provided eight numbers, not two. How does the division operator know which two numbers to use?
It uses the two that are in the same row as the function. So C6 divides A6 by B6. The formula in C6 is effectively
=A6/B6. The other six numbers that you passed to the division operator (rows 3 through 5) are ignored! Division of
one numerator by
one denominator produces
one quotient. This is passed to the SUM function. The sum of a single value is just that value. The values in column D are the same as the values in column C. These match the values which your function returned.
OpenOffice also provides an advanced feature called
array context.
Your formula does not do what you want in array context either. Array context is quite useful, but it can be difficult to understand and has some quirks which, at times, produce unexpected results. I do not recommend that beginners use this feature. To activate array context, when you type a formula instead of Enter press
⇪⌘Enter (Shift+Command+Enter) on a Mac or
Ctrl+Shift+Enter on other platforms. The
array feature
does allow you to divide a multi-row range by another multi-row range and get multiple quotients. Columns E though H show
=A$3:A3/B$3:B3,
=A$3:A4/B$3:B4,
=A$3:A5/B$3:B5, and
=A$3:A6/B$3:B6. The first of these gives one quotient (column E=2/4), the second gives two quotients (columns E=2/4, and F=2/5), etc. These quotients are passed to the SUM function.
But this isn't want you want. For the last set you don't want (2/4)+(2/5)+(2/4)+(0/4), you want (2+2+2+0)/(4+5+4+4). You want the quotient of the sums, not the sum of the quotients.
Columns J though L show the correct calculation. You need to call the SUM function twice (J andk K) and divide those results (L). This gives you the quotient of the two sums.
Stonehenge wrote: ↑Wed May 01, 2024 7:22 pm
Does it have to do with the order of calculation?
Yes! You must use the correct syntax to have Calc perform the operations in the order which you need, quotient of sums versus sum of quotients.
[Tutorial] Ten concepts that every Calc user should know
[Tutorial] Order of Operations in Calc : (precedence 9) is evaluated before
/ (precedence 3)
[Tutorial] Calc formula terms
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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).