Maximum Numbers from a series of Columns
 

Hi,

I have to add up the highest 5 numbers from a series of 8 columns.
Some rows will have 5 different numbers, some 6, some 7 or 8. Is there
a way to find the 5 highest from that series, and then have another
equation to add them up?

Thanks,

Mark

Maximum Numbers from a series of Columns
 

With
A1:H8 containing randomly dispersed numbers and blanks

This formula returns the sum of the 5 highest numbers in that range
=SUM(LARGE(A1:H8,{1,2,3,4,5}))

Note: if there may be less than 5 numbers overall let us know so we can trap
that (with a more complicated formula).

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hi,

I have to add up the highest 5 numbers from a series of 8 columns.
Some rows will have 5 different numbers, some 6, some 7 or 8. Is there
a way to find the 5 highest from that series, and then have another
equation to add them up?

Thanks,

Mark

Maximum Numbers from a series of Columns
 

That's awesome. Except you are correct, in that I will need to be able
to work the formula on rows that have less than 5 entries. OTherwise,
I receive a #NUM! error message if there are more than 3 blank
columns. But that's a GREAT start.

And looking to the future, I will actually need to do this for a
series of 10 or 11 columns, not the 8 that I specified in the first
message. I'm not sure that makes a difference, though.

Mark

On Feb 20, 5:49 pm, Ron Coderre
wrote:
With
A1:H8 containing randomly dispersed numbers and blanks

This formula returns the sum of the 5 highest numbers in that range
=SUM(LARGE(A1:H8,{1,2,3,4,5}))

Note: if there may be less than 5 numbers overall let us know so we can trap
that (with a more complicated formula).

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

" wrote:
Hi,

I have to add up the highest 5 numbers from a series of 8 columns.
Some rows will have 5 different numbers, some 6, some 7 or 8. Is there
a way to find the 5 highest from that series, and then have another
equation to add them up?

Thanks,

Mark

Maximum Numbers from a series of Columns
 

Ok....so you need to sum the 5 largest numbers in a single row.
AND....there may be less than 5 numbers.

With number and/or blanks in A1:H1

Try something like this:
I1:
=IF(COUNT(A1:H1),SUMPRODUCT(LARGE(A1:H1,ROW($A$1:I NDEX(A:A,MIN(5,COUNT(A1:H1)))))),"no numbers")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

That's awesome. Except you are correct, in that I will need to be able
to work the formula on rows that have less than 5 entries. OTherwise,
I receive a #NUM! error message if there are more than 3 blank
columns. But that's a GREAT start.

And looking to the future, I will actually need to do this for a
series of 10 or 11 columns, not the 8 that I specified in the first
message. I'm not sure that makes a difference, though.

Mark

On Feb 20, 5:49 pm, Ron Coderre
wrote:
With
A1:H8 containing randomly dispersed numbers and blanks

This formula returns the sum of the 5 highest numbers in that range
=SUM(LARGE(A1:H8,{1,2,3,4,5}))

Note: if there may be less than 5 numbers overall let us know so we can trap
that (with a more complicated formula).

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

" wrote:
Hi,

I have to add up the highest 5 numbers from a series of 8 columns.
Some rows will have 5 different numbers, some 6, some 7 or 8. Is there
a way to find the 5 highest from that series, and then have another
equation to add them up?

Thanks,

Mark

Maximum Numbers from a series of Columns
 

Once again, that's amazing to me. I couldn't figure all that out if
you gave me 5 years and a manual. Thanks very much, it works fine. I
just have to figure out how to adjust when/if I add any additional
columns or rows to the sheet. When I do, it doesn't work, but I can
work on that and figure it out.

Thanks again!!!

Mark

On Feb 20, 6:29 pm, Ron Coderre
wrote:
Ok....so you need to sum the 5 largest numbers in a single row.
AND....there may be less than 5 numbers.

With number and/or blanks in A1:H1

Try something like this:
I1:
=IF(COUNT(A1:H1),SUMPRODUCT(LARGE(A1:H1,ROW($A$1:I NDEX(A:A,MIN(5,COUNT(A1:H1)))))),"no numbers")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

" wrote:
That's awesome. Except you are correct, in that I will need to be able
to work the formula on rows that have less than 5 entries. OTherwise,
I receive a #NUM! error message if there are more than 3 blank
columns. But that's a GREAT start.

And looking to the future, I will actually need to do this for a
series of 10 or 11 columns, not the 8 that I specified in the first
message. I'm not sure that makes a difference, though.

Mark

On Feb 20, 5:49 pm, Ron Coderre
wrote:
With
A1:H8 containing randomly dispersed numbers and blanks

This formula returns the sum of the 5 highest numbers in that range
=SUM(LARGE(A1:H8,{1,2,3,4,5}))

Note: if there may be less than 5 numbers overall let us know so we can trap
that (with a more complicated formula).

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

" wrote:
Hi,

I have to add up the highest 5 numbers from a series of 8 columns.
Some rows will have 5 different numbers, some 6, some 7 or 8. Is there
a way to find the 5 highest from that series, and then have another
equation to add them up?

Thanks,

Mark