nested IF change to weighted average

[Harvey Waxman]

A B C
1 871780 1205510 38%
2 12960910 943800 -93%
3 42262230 3892500 -91%
4 25,810,440 29,112,800 13%
5 21,269,500 47,123,200 122%
6 18,739,370 33,196,790 77%
7 17,040,750 26,716,000 57%
8 11,921,800 24,996,500 110%
9 7,642,580 27,285,010 257%
10 4,212,200 26,381,850 526%
11 2,102,700 19,845,700 844%
12 2,860,100 22,332,300 681%
13 1,831,200 13,108,180 616%
14 1,968,400 8,110,700 312%

With the above data B/A = C. To get the weighted average of the
percentages in column C I used this formula:

=SUMPRODUCT(A1:A14,C1:C14)/SUM(A1:A14)

This gives me a weighted average of 66% which I believe is correct. But
can you explain why the answer is the same whether I enter as an array
or not?

How does this formula actually work to produce weighted average?

Is there a better way to get the weighted average?

Thanks

 

[JE McGimpsey]

With the above data B/A = C. To get the weighted average of the
percentages in column C I used this formula:

=SUMPRODUCT(A1:A14,C1:C14)/SUM(A1:A14)

This gives me a weighted average of 66% which I believe is correct. But
can you explain why the answer is the same whether I enter as an array
or not?

SUMPRODUCT() is an array function. Entering it with CMD-RETURN or
CTRL-SHIFT-ENTER is not required, nor, if done so, does it cause the
function to calculate differently.

How does this formula actually work to produce weighted average?

Take a simple example. Assume this data:

1, 2, 2, 3, 5, 1, 3, 4

The average of these numbers is 2.625.

However, the data can be reorganized as a weighted array:

Counts Value
2 1
2 2
2 3
1 4
1 5

Note that the SUM() of the first column is the total number of data
points (8).

When the columns are multiplied:

2 x 1 = 2
2 x 2 = 4
2 x 3 = 6
1 x 4 = 4
1 x 5 = 5

Then summed, the result is 21. When divided by the number of data
points, the result is the weighted average, 21/8 = 2.625

Is there a better way to get the weighted average?

Not really.

 

[Harvey Waxman]

Note that the SUM() of the first column is the total number of data
points (8).

When the columns are multiplied:

2 x 1 = 2
2 x 2 = 4
2 x 3 = 6
1 x 4 = 4
1 x 5 = 5

Then summed, the result is 21. When divided by the number of data
points, the result is the weighted average, 21/8 = 2.625

Clear explanation. The first column is the "weight" criterion which
could be the number of units or the total value of those units.

The second column is what one tries to find the weighted average of -
could be itself an average, or some other value. Is this correct?

Are there applications of SUMPRODUCT that don't produce a weighted
average? When else might one use it?

And thank you for your attention and time.

 

[JE McGimpsey]

Is this correct?
Yes

Are there applications of SUMPRODUCT that don't produce a weighted
average?
Hundreds...

When else might one use it?

Any time you need to multiply ordered sets of values and sum the results.

Just two examples:

1) FInd out revenue in November 2008, given the following:

A B
1 1 Oct 08 100
2 12 Nov 08 120
3 5 Jul 08 60
4 27 Nov 08 50
5 30 Nov 07 100
6 2 Jan 09 40

=SUMPRODUCT(--(A1:A16>=DATE(2008,11,2), --(A1:A16<=DATE(2008,11,30),
B1:B100)

returns 170. The explanation for -- is here:

http://www.mcgimpsey.com/excel/doubleneg.html


2) Calculate variable commission rates:

http://www.mcgimpsey.com/excel/variablerate.html

 

[Harvey Waxman]

When else might one use it?

Any time you need to multiply ordered sets of values and sum the results.

Just two examples:[/QUOTE]

As always, thanks.