Help with Excel/Google Sheet formula to calculate place finishes...

992 Views | 9 Replies | Last: 7 yr ago by ABATTBQ11
CapCity12thMan
How long do you want to ignore this user?
AG
I have some data in a worksheet that is sorted by point total. I would like for the sheet to automatically calculate the place finish of each person, but be able to handle ties (as shown in the following):

https://docs.google.com/spreadsheets/d/1urf7Lpk2DbmqaxkxgKUzaZREcNolJxSpZgzieAuwrMs/edit?usp=sharing

Formula/function(s) to accomplish this?

Assume all rows have >= 0 points

TIA
AggieChemE09
How long do you want to ignore this user?
AG
=RANK(C2,$C$2:$C$16)
AggieChemE09
How long do you want to ignore this user?
AG
For Extra Credit, I added the "T" in front of ties.



=if(COUNTIF($C$2:$C$16,C2)>1,"T"&RANK(C2,$C$2:$C$16),RANK(C2,$C$2:$C$16))
CapCity12thMan
How long do you want to ignore this user?
AG
cool - thanks. I mis-read what rank does apparently.
CapCity12thMan
How long do you want to ignore this user?
AG
Well, now that I have your attention...I would like for the point total to be the sum of the TOP 5 events for each person.

I started down the path of using the LARGE function, but it got kinda ridiculous. What was throwing things is that there will be some events with no points. Perhaps if the empty cell was a 0 it would work better, but would rather leave them empty (long story).
AggieChemE09
How long do you want to ignore this user?
AG
https://docs.google.com/spreadsheets/d/1_AZwDslFBuaAoVsCOvYwTVG13QzlMkFz53Og_7DCEfc/edit?usp=sharing
CapCity12thMan
How long do you want to ignore this user?
AG
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?
Oh Four Five
How long do you want to ignore this user?
AG
=SUMPRODUCT(LARGE(D2:J2,ROW(INDIRECT("1:"&MIN(5,COUNT(D2:J2))))))

SUMPRODUCT multiplies if you include two or more arrays but will just sum the array if only one is referenced.
AggieChemE09
How long do you want to ignore this user?
AG
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?
I would love to explain, but to make that formula all I did was literally google search "sum of the TOP 5 ", then click on the top link, then use the first formula I saw. It worked.

90% of Excel is googling the correct question and then copy and pasting the formula. If you can put what you are trying to do in excel into words, it has already been done by someone.
ABATTBQ11
How long do you want to ignore this user?
AG
CapCity12thMan said:

Well, now that I have your attention...I would like for the point total to be the sum of the TOP 5 events for each person.

I started down the path of using the LARGE function, but it got kinda ridiculous. What was throwing things is that there will be some events with no points. Perhaps if the empty cell was a 0 it would work better, but would rather leave them empty (long story).


=Sumproduct(--rank(range, range)>5, range)
ABATTBQ11
How long do you want to ignore this user?
AG
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.
Refresh
Page 1 of 1
 
×
subscribe Verify your student status
See Subscription Benefits
Trial only available to users who have never subscribed or participated in a previous trial.