SUM of AVERAGE or AVERAGE SUM

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.