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

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

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.

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.

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.

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.

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!

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!

=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

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!

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

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