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.

=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

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