Which division is correct? Google Sheets or Calculator?

Question

C16
Google Sheets -> 73%
Calculator -> 73%

F16
Google Sheets -> 72%
Calculator -> 74%

Row 16 is the sum of rows 1-15.

C16 and F16 are the sum of rows 1-15, then divided by 15.

F16 Formula: =SUM(F1:F15)/15

https://docs.google.com/spreadsheets/d/1VhHmNuffurWeXju8-Ftrj_Aw5CqxjmOiuKH97V0XqeY/edit?usp=sharing

Answer 1

Google Sheets calculates =218 / 294 to 0.741496598639456 which is quite precisely the same result as the calculator screenshot shows. So the explanation for the different result that is shown in the spreadsheet screenshot is that it depicts an incorrect calculation method.

F16 Formula: =SUM(F1:F15)/15

The formulas in F1:F15 each divide one number by another. The formula in F16 effectively averages those sub-results. But the average of sub-results is not the same as the average of the total.

The error in the calculation method may not become apparent when the values in columns D:E are close to each other, but it is easy to see when the values are more diverse. Consider this example:

row #	column D	column E	column F
row 13	99	1	99 =D13 / E13
row 14	99	1	99 =D14 / E14
row 15	2	98	0.02040816327 =D15 / E15
row 16	200 =sum(D1:D15)	100 =sum(E1:E15)	13.20136054 =sum(F1:F15) / 15
correct result			2 =D16 / E16

To get the correct result, put this formula in cell F16:

=D16 / E16

See Why is an average of an average usually incorrect?

Answer 2

Everything is "correct" insofar as neither the calculator nor Sheets is making a mistake. Also, both sheets and the calculator can return either result if you ask them.

You are performing completely different mathematical calculations. If you look at my second example below you will see that the approach you choose can have a much larger impact depending on your data.

Let's say you have a restaurant and want to know what percentage a diner "typically" tips. You could average the tip percentage of each diner, that would be your 72.3% / 40% results.

If you wanted to know how the total of all tips collected compares to the total of all sales, that would be your 74.1 / 23.2% results

If those numbers are very close it doesn't matter. If they were quite different one might try to find out why.

In the case of the second example large bills represent only 1/3 of the "diners" however the percentage tipped on those is 20% vs 50% for small bills which skews the results of both calculations so it is important to understand the "purpose" of the calculation.

array_a: {18, 18, 20, 17, 15, 18, 16, 16, 9, 13, 4, 18, 11, 7, 18}
array_b: {24, 23, 24, 21, 22, 21, 21, 22, 18, 17, 12, 22, 15, 11, 21}

218 = SUM(array_a)
294 = SUM(array_b)

74.1% = 218/294
74.1% = SUM(array_a)/SUM(array_b)
72.3% = AVERAGE(INDEX(array_a/array_b))

An example in more detail:

array_a: {1, 5, 20}
array_b: {2, 10, 100}

=AVERAGE(INDEX(array_a/array_b))
=AVERAGE({1/2, 5/10, 20/100}) 
=AVERAGE({50%, 50%, 20%})
=40%

=SUM(array_a)/SUM(array_b) 
=SUM({1, 5, 20})/SUM({2, 10, 100})
=26/112
=23.2%