CapCity12thMan said:
cool - was not aware of that array construct to use in LARGE (disclaimer - I don't do too much excel formula work).
the SUMPRODUCT function - that looks to do some multiplication, so I am not following how it is working...would you mind explaining?
SUMPRODUCT multiplies and sums arrays. Here's a primer.
If you had arrays of (1,2,3,4) and (5,6,7,8) and used sumproduct, you would get:
sum of(
1*5=5
2*6=12
3*7=21
4*8=32
)
= 70
Easy enough, but SUMPRODUCT works in other ways too. Consider this:
Column A
1
3
5
7
9
SUMPRODUCT(RANK(A1:A5,A1:A5)) produces
sum(
1*(understood 1 because single array)
2*(understood 1 because single array)
3*(understood 1 because single array)
4*(understood 1 because single array)
5*(understood 1 because single array)
)=15
because it returns the rank of each item in the list. SUMPRODUCT forces Rank to evaluate in its array format (notice that the second argument is a range (A1:A5) instead of a single value), and it returns an array of the rankings for everything in the range instead of the range values. We can expand this to get the top x items by using SUMPRODUCT(--(RANK(A1:A5,A1:A5)>=3,A1:A5)) which produces
sum(
1*1=1
1*2=2
1*3=3
0*4=0
0*5 =0
)=6
SUMPRODUCT forces Rank to evaluate as True, True, True, False, False in its array format because of the logical condition attached to it, and the preceeding "--" forces those true false values to evaluate to 1, 1, 1, 0, 0. The second array in SUMPRODUCT is simply the values of the original range, which get multiplied by the 1 or 0 on the condition we created with RANK. Only those values meeting the condition are multiplied by 1 and summed, while all others are zeroed out.
With this, you can see the easy solution to your question about summing the top 5 values. However, you would need a way to balance out ties. That's where using LARGE might come in.