Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum of ranks
I need to find the sum of the rankings in a row. Let me explain, below
you'll see a sample of my data (there are actually more columns). For each team, the stat would be ranked and that rank added to the ranking in the other categories. Team R H HR Team 1 30 51 7 Team 2 20 52 5 Team 3 31 69 8 Team 4 30 53 6 Team 5 36 58 6 Team 6 40 69 11 Team 7 25 64 2 Team 8 21 57 1 Team 9 33 60 8 Team 10 33 62 8 I tried something like this, but the totals are exactly 30 points higher than they should be for this data: {=SUM(RANK($B2:$D2,$B$2:$D$11))} I hope I've made myself understandable. Thanks, in advance, for the help. tim |
#2
|
|||
|
|||
Try:
=SUM(RANK(B2,$B$2:$B$11),RANK(C2,$C$2:$C$11),RANK( D2,$D$2:$D$11)) No need to array-enter. -- Vasant "Tim Otero" wrote in message ... I need to find the sum of the rankings in a row. Let me explain, below you'll see a sample of my data (there are actually more columns). For each team, the stat would be ranked and that rank added to the ranking in the other categories. Team R H HR Team 1 30 51 7 Team 2 20 52 5 Team 3 31 69 8 Team 4 30 53 6 Team 5 36 58 6 Team 6 40 69 11 Team 7 25 64 2 Team 8 21 57 1 Team 9 33 60 8 Team 10 33 62 8 I tried something like this, but the totals are exactly 30 points higher than they should be for this data: {=SUM(RANK($B2:$D2,$B$2:$D$11))} I hope I've made myself understandable. Thanks, in advance, for the help. tim |
#3
|
|||
|
|||
Thanks Vasant,
I guess I should have mentioned I could do it that way, but am looking for something a little more elegant (I've got 26 colums and will probably be bumping up against the character limit. Vasant Nanavati wrote: Try: =SUM(RANK(B2,$B$2:$B$11),RANK(C2,$C$2:$C$11),RANK( D2,$D$2:$D$11)) No need to array-enter. |
#4
|
|||
|
|||
How about this?
=SUM(RANK($B2:$D2,$B$2:$D$11))-COUNT($B$2:$D$11) array-entered. -- Vasant "Tim Otero" wrote in message ... Thanks Vasant, I guess I should have mentioned I could do it that way, but am looking for something a little more elegant (I've got 26 colums and will probably be bumping up against the character limit. Vasant Nanavati wrote: Try: =SUM(RANK(B2,$B$2:$B$11),RANK(C2,$C$2:$C$11),RANK( D2,$D$2:$D$11)) No need to array-enter. |
#5
|
|||
|
|||
Thanks Vasant,
got it to work with three columns, but it broke with 4...I think I'll just try the other way. tim Vasant Nanavati wrote: How about this? =SUM(RANK($B2:$D2,$B$2:$D$11))-COUNT($B$2:$D$11) array-entered. |
#6
|
|||
|
|||
Sorry; not having a good day today. Will try and think of a more elegant
solution tomorrow! -- Vasant "Tim Otero" wrote in message ... Thanks Vasant, got it to work with three columns, but it broke with 4...I think I'll just try the other way. tim Vasant Nanavati wrote: How about this? =SUM(RANK($B2:$D2,$B$2:$D$11))-COUNT($B$2:$D$11) array-entered. |
#7
|
|||
|
|||
Thanks Vasant,
Usually I can come up with something pretty quick, but this one has me stumped. thanks again for your help. Vasant Nanavati wrote: Sorry; not having a good day today. Will try and think of a more elegant solution tomorrow! |
#8
|
|||
|
|||
Hi!
This works in a single formula but requires that you use named ranges. Assume your headers: RUNS, H, HR are in the range B1:?1 Note: in your post you had "R" as a header. Excel will not accept that as a name for a range so I changed it to "RUNS". So, name all the ranges the same as the header. For example: B1 = RUNS =$B$2:$B$11 C1 = H =$C$2:$C$11 D1 = HR =$D$2:$D$11 etc etc etc Formula to sum total of ranks: =SUMPRODUCT(RANK(B2:D2,INDIRECT(B$1:D$1))) I didn't try this on 26 named ranges but I don't see why it wouldn't work. Biff "Tim Otero" wrote in message ... Thanks Vasant, Usually I can come up with something pretty quick, but this one has me stumped. thanks again for your help. Vasant Nanavati wrote: Sorry; not having a good day today. Will try and think of a more elegant solution tomorrow! |
#9
|
|||
|
|||
=SUMPRODUCT(RANK(B2:D2,OFFSET(B2,0,COLUMN($B$2:$D$ 2)-COLUMN($B$2),ROWS($B$2:$B$11))))
Tim Otero wrote: I need to find the sum of the rankings in a row. Let me explain, below you'll see a sample of my data (there are actually more columns). For each team, the stat would be ranked and that rank added to the ranking in the other categories. Team R H HR Team 1 30 51 7 Team 2 20 52 5 Team 3 31 69 8 Team 4 30 53 6 Team 5 36 58 6 Team 6 40 69 11 Team 7 25 64 2 Team 8 21 57 1 Team 9 33 60 8 Team 10 33 62 8 I tried something like this, but the totals are exactly 30 points higher than they should be for this data: {=SUM(RANK($B2:$D2,$B$2:$D$11))} I hope I've made myself understandable. Thanks, in advance, for the help. tim |
#10
|
|||
|
|||
Thanks Biff,
That worked beautifully. I got so locked into using an array formula, I forgot all about Indirect. Thanks for the help. tim Biff wrote: Hi! This works in a single formula but requires that you use named ranges. Assume your headers: RUNS, H, HR are in the range B1:?1 Note: in your post you had "R" as a header. Excel will not accept that as a name for a range so I changed it to "RUNS". So, name all the ranges the same as the header. For example: B1 = RUNS =$B$2:$B$11 C1 = H =$C$2:$C$11 D1 = HR =$D$2:$D$11 etc etc etc Formula to sum total of ranks: =SUMPRODUCT(RANK(B2:D2,INDIRECT(B$1:D$1))) I didn't try this on 26 named ranges but I don't see why it wouldn't work. Biff "Tim Otero" wrote in message ... Thanks Vasant, Usually I can come up with something pretty quick, but this one has me stumped. thanks again for your help. Vasant Nanavati wrote: Sorry; not having a good day today. Will try and think of a more elegant solution tomorrow! |
#11
|
|||
|
|||
Nice one!
Minor correction: OFFSET(B2...... Needs to be: OFFSET(B$2...... Biff "Aladin Akyurek" wrote in message ... =SUMPRODUCT(RANK(B2:D2,OFFSET(B2,0,COLUMN($B$2:$D$ 2)-COLUMN($B$2),ROWS($B$2:$B$11)))) Tim Otero wrote: I need to find the sum of the rankings in a row. Let me explain, below you'll see a sample of my data (there are actually more columns). For each team, the stat would be ranked and that rank added to the ranking in the other categories. Team R H HR Team 1 30 51 7 Team 2 20 52 5 Team 3 31 69 8 Team 4 30 53 6 Team 5 36 58 6 Team 6 40 69 11 Team 7 25 64 2 Team 8 21 57 1 Team 9 33 60 8 Team 10 33 62 8 I tried something like this, but the totals are exactly 30 points higher than they should be for this data: {=SUM(RANK($B2:$D2,$B$2:$D$11))} I hope I've made myself understandable. Thanks, in advance, for the help. tim |
#12
|
|||
|
|||
Thank you Aladin!
Aladin Akyurek wrote: =SUMPRODUCT(RANK(B2:D2,OFFSET(B2,0,COLUMN($B$2:$D$ 2)-COLUMN($B$2),ROWS($B$2:$B$11)))) Tim Otero wrote: I need to find the sum of the rankings in a row. Let me explain, below you'll see a sample of my data (there are actually more columns). For each team, the stat would be ranked and that rank added to the ranking in the other categories. Team R H HR Team 1 30 51 7 Team 2 20 52 5 Team 3 31 69 8 Team 4 30 53 6 Team 5 36 58 6 Team 6 40 69 11 Team 7 25 64 2 Team 8 21 57 1 Team 9 33 60 8 Team 10 33 62 8 I tried something like this, but the totals are exactly 30 points higher than they should be for this data: {=SUM(RANK($B2:$D2,$B$2:$D$11))} I hope I've made myself understandable. Thanks, in advance, for the help. tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Divide Ranks into two teams (mathematical guru challenge) | Excel Discussion (Misc queries) |