Sum of ranks

How about this?

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

array-entered.

--

Vasant

"Tim Otero" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

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

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" <[email protected]> wrote in message
> news:%[email protected]...
>
>>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" <[email protected]> wrote in message
news:%[email protected]...
> 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

Nice one!

Minor correction:

OFFSET(B2......

Needs to be:

OFFSET(B$2......

Biff

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> =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 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.
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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

How about this?

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

array-entered.

--

Vasant

"Tim Otero" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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

Nice one!

Minor correction:

OFFSET(B2......

Needs to be:

OFFSET(B$2......

Biff

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> =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" <[email protected]> wrote in message
> news:%[email protected]...
>
>>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!
>>>
>
>
>

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

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

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" <[email protected]> wrote in message
> news:%[email protected]...
>
>>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" <[email protected]> wrote in message
news:[email protected]...
> =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

=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

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" <[email protected]> wrote in message
news:%[email protected]...
> 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!
>>

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!
>

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

--

Vasant

"Tim Otero" <[email protected]> wrote in message
news:[email protected]...
> 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,

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.
>

How about this?

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

array-entered.

--

Vasant

"Tim Otero" <[email protected]> wrote in message
news:[email protected]...
> 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,

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.
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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

Nice one!

Minor correction:

OFFSET(B2......

Needs to be:

OFFSET(B$2......

Biff

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> =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" <[email protected]> wrote in message
> news:%[email protected]...
>
>>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!
>>>
>
>
>

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