SUM of AVERAGE or AVERAGE SUM

[J_Mendez92]

This is my problem (Let see if i can get some answers):

In the first example with equal cost, 

The SUM of (Cost * 1+SUM(Margin)) is Equal to the SUM (Cost) * (1+SUM(AVERAGE(Margin)))

But if you change even a decimal, then the case is not TRUE, like the second example..

[#2]

@J_Mendez92 

"If a picture is worth 1000 words, an Excel file is worth 1000 pictures". (wink)

Please attach an Excel file that demonstrates the problem, or provide a link to an Excel file that you uploaded to a file-sharing website that does not require a log-in.

At the very least, please show the formulas that you use.  The actual Excel formulas, copy-and-pasted from the Formula Bar.  Not some vague abstraction, as you wrote.

In general, the sum of averages is not the same as the average of the sum.  And the latter is the correct answer.

But the devil is in the details.  I'll see what I can intuit from the dearth of data that you provided.

In the meantime, you are more likely to get a dispositive answer quickly if you fill in the blanks.

[#3]

This is the link for the file:

https://1drv.ms/x/s!AtEbVfy8H2wIsVX0xp0Q2nUMhTs4?e=43NbZk

[#4]

@J_Mendez92 

For this concrete case in theory E1 and E9 shall be the same

it looks like Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Learn

[#5 · Accepted solution]

@J_Mendez92  wrote: ``This is the link for the file``

Perfect!  Thanks.

But please unmark my previous reply as "best response", if you can.  It is not (yet) a response to your question(s).

-----

To clarify, I believe you are asking two questions.

The following image shows what you posted previously, followed by the formulas that we find in your Excel file.  See the attached Excel file for details.

For clarity, I have moved the Conditional Formatting formulas into rows 5 and 13, where they can be seen and discussed.

Formulas:

A1: =SUM(A2:A3)

B1: =AVERAGE(B2:B3)

E1: =AVERAGE(E2:E3)=SUM(B1:D1)

F1: =SUM(1,B1:D1)*A1

E2: =SUM(B2:D2)

F2: =A2*(1+E2)

F5: =SUM(F2:F3)

G5: =F5=F1

-----

Your thread title asks if the sum of averages is the same as the average of the sums.

As I explained previously:  in general, no.

But your example demonstrates the exception to the rule, namely:  when the divisor of each average is the same.

This is demonstrated in the yellow-highlighted formulas in E1 and E9.  For example (E1):

AVERAGE(E2:E3) = SUM(B1:D1)

The algebra is:

e2 = b2+c2+d2

e3 = b3+c3+d3

average(e2,e3) = (e2+e3)/2 = (b2+c2+d2+b3+c3+d3)/2

sum(b1,c1,d1) = (b2+b3)/2 + (c2+c3)/2 + (d2+d3)/2 = (b2+b3+c2+c3+d2+d3)/2

Rearranging terms in the last expression, we can see that they are the same.

-----

However, that has nothing to do with the real question you ask, namely:  why does F1 = F5, but F9 <> F13?

The difference is because A2 = A3, but A10 <> A11.

(PS....  That difference can be large.  For example, set A10=10 and A11=20; thus, A9=30.  Then we can see a large difference between F9 and F13, which might be less confusing to the casual reader.)

This is demonstrated by the formulas in G5 and G13.  They are effectively (G5):

SUM(F2:F3) = SUM(1,B1:D1)*A1

The algebra is:

f2 = a2*(1+b2+c2+d2)

f3 = a3*(1+b3+c3+d3)

sum(f2,f3) = a2*(1+b2+c2+d2) + a3*(1+b3+c3+d3)

And that is the correct sum to use.

a1*sum(1,b1,c1,d1) = (a2+a3) * (1 + (b2+c2+d2+b3+c3+d3)/2)

relying on the average equality exception demonstrated above.

In general when a2<>a3, we can see that the last formula is unrelated to the sum(f2,f3) formula.

But in the exceptional case when a2=a3, we can show that the two formulas are equivalent by replacing a3 with a2:

sum(f2,f3) = a2*(1+b2+c2+d2) + a2*(1+b3+c3+d3)

= a2 * (2 + b2+c2+d2+b3+c3+d3)

= 2 * a2 * (2 + b2+c2+d2+b3+c3+d3) / 2

=(a2+a2) * (1 + (b2+c2+d2+b3+c3+d3)/2)

=a1*sum(1,b1,c1,d1)

-----

I hope you followed the algebra.

To summarize:

1. Both E1 and E9 are TRUE because of the exceptional case when each average has the same divisor.

2. F1 = F5 because of the exceptional case when a2=a3.

[#6]

Yes, it's really do