Forum Discussion
What is this Madness? Weird Average and Sum results
What is this devil math? What am I missing? The darker green value and blue value should match. I checked for truncated decimal values, even rebuilt the sample to ensure nothing was weird about the original cells. This is not an insignificant difference for what I'm doing, and I don't understand why these aren't lining up. I'm sorry if I'm missing something simple, but please, help!
4 Replies
HelloLYorkToenniges​,
You are comparing two different calculations, so the results will not match.
The blue value is the average of the row-by-row divisions, which gives each row equal weight.
The green value is calculated by dividing the sum of one column by the sum of the other, which creates a weighted result.
These are not mathematically equivalent and will only match if all denominators are the same.
The difference you are seeing is expected behavior in Excel, not an error.
I'll be honest, I'm really embarrassed. My coworker pointed out what I was missing. Basic PEMDAS. I knew the issue had to be me, my mind was just not seeing what math I was really doing.
1/3 +2/6 +5/8 <> (1+2+5)/(3+6+8) if I saw this I'd be like, yeah, duh. So embarrassing.
There is no madness or devil math here, just a (common) misconception: "The darker green value and blue value should match."
No, they should not match. The average in the darker blue is an unweighted average of the percentages above. It would be meaningful only if each row of data had the same importance. The average in the darker green is the weighted average; it gives more weight to the larger values. The latter is almost certainly the one you want to use.
Yes, this is what we have been using, my brain was inspecting various things for accuracy and was clearly in need of a break I was refusing to give it. Thankfully we are using weighted average, Although in this particular case they are equally valued; they are people's scores on a single team, so the average of their scores is also viable; so there could be an argument for the other case. Thank you for the help. I'm so embarrassed. I can do calculus but apparently not basic math.
