[Solved] Determine running batting average

[Stonehenge]

Stonehenge
I am trying to calculate a batting average based on game entries and I am having some issues.
Basic formula is Hits/AtBats and returns a number to the third digit after the decimal.
Column A Column B Column C I am using =SUM(($B$1:B4)/($A$1:A4)) and I am not getting a correct result.

Code: Select all

   At Bats     Hits           .Avg
    4               2            .500   should be 2/4 = .500  this works
    5               2            .400   should be 4/9 = .444  why is it returning .400?? 
    4               2            .500   should be 6/13= .462  why is it returning .500??
    4               0            .000   should be 6/17= .353  why is it returning .000??

Am I using the wrong formula or function?? I wrote the formula with the $ so I could copy it down say 150 lines to calucate a running batting average through out the season but I can't get the second line to come out right! Is their something wrong with using the ":" range specifier?? Is their a different way to write this to get the correct result? I am using OpenOffice 4.1.15 on Windows 10 (22H2). If I get this to work I am going to use it for 3 other columns that display the result as .### (On Base Percentage, OPS and Slug).

Where can I read an explanation as to why this works??

Edit: Changed subject, was Writing a formula to Calculate correctly  Make your post understandable by others  -- MrProgrammer, forum moderator

[robleyd]

Try

Code: Select all

=SUM($B$2:B5)/SUM($A$2:A5)

I leave explaining the difference as an exercise for the student

[Hagar Delest]

Hi and welcome to the forum!
The SUM() is misplaced. You need it for both numerator and denominator, that is: =SUM($B$1:B1)/SUM($A$1:A1) for C1 and then drag the cell content.

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the 🖉 button if your issue has been fixed.

[Stonehenge]

Try

Code: Select all

=SUM($B$2:B5)/SUM($A$2:A5)

I leave explaining the difference as an exercise for the student

Thank you for the quick solution but where can I read an explanation as to why this works and why my original formula returned the other answer? Does it have to do with the order of calculation?

[Hagar Delest]

The problem is not with the order but with the syntax.
First a mere parenthesis doesn't represent a sum and second you use sum as the main operator when there is no point summing the whole thing.
It seems that a parenthesis only returns the last cell value.

[MrProgrammer]

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.

202405011627.ods

(12.45 KiB) Downloaded 20 times

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.