Sum of ranks

[Tim Otero]

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

 

[Vasant Nanavati]

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.

 

[Tim Otero]

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]

How about this?

=SUM(RANK($B2:$D2,$B$2:$D$11))-COUNT($B$2:$D$11)

array-entered.

 

[Tim Otero]

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]

Sorry; not having a good day today. Will try and think of a more elegant
solution tomorrow!

 

[Tim Otero]

Thanks Vasant,

Usually I can come up with something pretty quick, but this one has me
stumped. thanks again for your help.

 

[Biff]

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(B22,INDIRECT(B$1$1)))

I didn't try this on 26 named ranges but I don't see why it wouldn't work.

Biff

 

[Aladin Akyurek]

=SUMPRODUCT(RANK(B22,OFFSET(B2,0,COLUMN($B$2:$D$2)-COLUMN($B$2),ROWS($B$2:$B$11))))

 

[Tim Otero]

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]

Nice one!

Minor correction:

OFFSET(B2......

Needs to be:

OFFSET(B$2......

Biff

 

[Tim Otero]

Thank you Aladin!

=SUMPRODUCT(RANK(B22,OFFSET(B2,0,COLUMN($B$2:$D$2)-COLUMN($B$2),ROWS($B$2:$B$11))))