Excel 2007 : Sum of highest numbers and average of highest 3 numbers

**EsKay!** · 02-18-2010, 11:12 PM

Help needed with following:

A1 Column A Column B
A2 Units Amount
A3 650 $2,750
A4 75 $3,000
A5 350 $2,600
A6 225 $2,250
A7 475 $3,200
A8 175 $1,950

A9
A10

-In A9, I want the sum of highest 3 units from the list in column A
-In A10, I want the sum of rest of the units from the list column A
-In B9, I want weighted average of the amount in column B that corresponds to
the highest 3 units in column A.
-In B10, weighted average of the amount in Column B corresponding to the rest
of the units in A10

**DonkeyOte** · 02-19-2010, 03:43 AM

The first two are straightforward enough, ie:

A9: =SUM(LARGE(A3:A8,{1,2,3}))
A10: =SUM(A3:A8)-A9

However, the latter two calcs (weighted avg.) become more complex if you consider possibility that Unit values may appear multiple times, eg:

650
650
750
650
50
100

Assuming only the first three instances are to be included in the weighted average things become far more complex and you would be best served using a unique identifier in Column C.

To create the unique identifier (or indeed calc. without out) we need to know in what order preference would be given to equal Unit Values ?
ie would pref. be given to that with the greater Amount (ie higher unit price) or would you simply base on "which listed first" ?

Let us know.

**EsKay!** · 02-21-2010, 03:50 PM

Originally Posted by DonkeyOte

The first two are straightforward enough, ie:

A9: =SUM(LARGE(A3:A8,{1,2,3}))
A10: =SUM(A3:A8)-A9

However, the latter two calcs (weighted avg.) become more complex if you consider possibility that Unit values may appear multiple times, eg:

650
650
750
650
50
100

Assuming only the first three instances are to be included in the weighted average things become far more complex and you would be best served using a unique identifier in Column C.

To create the unique identifier (or indeed calc. without out) we need to know in what order preference would be given to equal Unit Values ?
ie would pref. be given to that with the greater Amount (ie higher unit price) or would you simply base on "which listed first" ?

Let us know.

If the unit values appear more than once, they have to be considered. e.g if there are 4 instances of 750, then we include the top 4 in columnA and corresponding amount in columnB. If weighted average is very difficult, how about simple average?

**DonkeyOte** · 02-22-2010, 05:56 AM

I'm sorry it's still not clear to me.

Using a sample dataset of (A3:B8):

Please Login or Register  to view this content.

Please outline expected results for each of: A9:B10

per your prior post it's not clear if in fact A9 should now be 750+650*3 (given the 3rd largest value appears multiple times thus all instances thereof should be included) or still 750+2*650 (ie top 3).

Expected results will allow people to validate the logic.

What you want to can be done, the specifics however depend largely on how duplicates are handled.

Please also refrain from quoting entire posts in your reply - use the New Post button or quote only those parts necessary for the thread to maintain a logical flow (ie answering out of sequence etc)

**EsKay!** · 02-22-2010, 05:58 PM

[QUOTE=DonkeyOte;2258348]I'm sorry it's still not clear to me.

Using a sample dataset of (A3:B8):

Please Login or Register  to view this content.

In the above, the 3 items added will be 750, 650 and 650 and put in A9. It does not matter which two 650s are selected (from A3, A4 or A6). But in B9, weighted average of the corresponding 3 numbers from column B, should be put. If A3, A4 and A5 are added and put in A9, then in B9, I need the weigthed average of B3, B4 and B5. If weighted average is very difficult, average is OK.

I hope this is clear now.

**DonkeyOte** · 02-22-2010, 07:16 PM

Were it me I confess I'd be looking to add a couple of additional calcs to ensure I weighted the appropriate values in my "top 3", ie:

C3: =$A3+($B3/1000000)
copied to C8

D3: =RANK($C3,$C$3:$C$8)+COUNTIF($C$3:$C3,$C3)-1
copied to D8

A9 & A10 are unchanged from before:

A9: =SUM(LARGE($A$3:$A$8,{1,2,3}))
A10: =SUM($A$3:$A$8)-$A$9

The values in C & D come into play when est. weighted averages

B9: =SUMPRODUCT(--($D$3:$D$8<=3),$A$3:$A$8,$B$3:$B$8)/SUMIF($D$3:$D$8,"<=3",$A$3:$A$8)
B10: as above but change <=3 to >3

overkill perhaps - but a safe-ish way to go about things.... this approach means in the cases of tied unit output the higher aggregate is used first.

NOTE: if the Amount (B3:B8) exceeds 1,000,000 you should adjust the divisor in the C3:C8 calcs accordingly (ie ensure divisor exceeds greatest amount value such that amt/divisor always < 1)