Errornous =SUM result in G Sheets

Russinating · 1 Oct 2019 at 14:38

I'm getting an incorrect result when using =SUM in Google Sheets. Any idea why?

It's really basic. The formula is =SUM((C2:C63)/62), so it's just an average with the C column simply being numbers. However the output of the formula is 4.84% but the correct answer is 4.629032% (nowhere near the result even if rounded up).

The column totals correctly as 287 when using =SUM(C2:C63), and using =SUM([that cell]/62) works as well.

I'm stumped, and it's making me doubt/check all the other formulas which isn't particularly productive.

chroniclard · 1 Oct 2019 at 14:46

dunno why it doesnt work but why not use =AVERAGE(C2:C63)

touch · 1 Oct 2019 at 17:11

You're not using the correct syntax. SUM adds the values of the range between the cells in the brackets after it. You use it like SUM(cell1:cell2)
If you want to use the result of that in another calculation, you dont change whats inside it's brackets, you add the calculation around it.

=SUM(C2:C63)/62

or

=(SUM(C2:C63))/6

bazzabear · 1 Oct 2019 at 17:13

Yeah, touch is right. I suspect the formula you're using is dividing each cell by 62 individually, then summing the results.

visibleman · 1 Oct 2019 at 18:21

chroniclard said:
....why not use =AVERAGE(C2:C63)

Do exactly this; makes it a little easier if you need to modify the data range etc.

Donnie Fisher · 3 Oct 2019 at 12:30

bazzabear said:
Yeah, touch is right. I suspect the formula you're using is dividing each cell by 62 individually, then summing the results.

But why would that give a different result ? If all cells are individually divided by 62 ... they surely should give the same value as all added, then divided by 62. For example, dividing by 10:

Code:

(A/10) + (B/10) + (C/10)  should be the same as  (A+B+C)/10
(2/10) + (3/10) + (2/10) = (2+3+2)/10
0.2+0.3+0.2 = 7/10
0.7 = 0.7

Whether the structure of the formala typed is easiest or not ... the result should be the same ? As so the OP is correct in his complaint ?

The only thing I can think of that the cells properties are being restricted to 2 decimal places or something similar, which when each cell division is calculated, the rounding to 2 decimal points is combining to create the errors.

skyripper · 3 Oct 2019 at 12:36

Its not dividing all the cells in that range by 62 though.

It's taking the range and performing a divide against it and then trying to sum that, which is syntactially bad.
It might be mathematically logical in your head, but you can bet the programmer of that function never coded it to work that way.

The function is expecting an input of a certain format, and the user is randomly choosing to divide it by 62 before asking the program to call that function.

Randomface74 · 3 Oct 2019 at 12:39

pebkac

Donnie Fisher · 3 Oct 2019 at 12:45

Yup - fair enough. I just tried my example above in excel and it wont let me to the calculation in the syntax the OP had. it returns !value.

james.miller · 3 Oct 2019 at 13:32

Sum has issues in excel as well, you can break it by summing only 4 numbers. That said, if you use a function in either in a way its not designed to be used then you should expect it to be incorrect.