Adding 7 highest numbers
 

Hi all

This sounds like it should be easy but for the life of me I cant work it
out.

What I have is a row of numbers and I have to add the 7 highest value
numbers out of a possible 9 otherwise just add what ever is in the row

for instance
row 1 contains 94, 96, 91, 92, 95, 90, 96, 98 total should equal 662
row 2 contains 96, 97, 93, 93, 96, 91, 97 total should equal 663
row 3 contains 95, 95, 92, 94, 94, 92 total should equal 562

any help would be gratefully appreciated

Stu

=IF(COUNT(A1:H1)<7,SUM(A1:H1),SUM(LARGE(A1:H1,{1,2 ,3,4,5,6,7})))

"Stu" wrote:

Hi all

This sounds like it should be easy but for the life of me I cant work it
out.

What I have is a row of numbers and I have to add the 7 highest value
numbers out of a possible 9 otherwise just add what ever is in the row

for instance
row 1 contains 94, 96, 91, 92, 95, 90, 96, 98 total should equal 662
row 2 contains 96, 97, 93, 93, 96, 91, 97 total should equal 663
row 3 contains 95, 95, 92, 94, 94, 92 total should equal 562

any help would be gratefully appreciated

Stu

One way:

=SUM(LARGE(A1:I1,ROW(INDIRECT("1:"&MIN(7,COUNT(A1: I1))))))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

or if there could be 0 entries:

=IF(COUNT(A1:I1)=0,"",
SUM(LARGE(A1:I1,ROW(INDIRECT("1:"&MIN(7,COUNT(A1:I 1)))))))
(one cell and still an array formula)

Stu wrote:

Hi all

This sounds like it should be easy but for the life of me I cant work it
out.

What I have is a row of numbers and I have to add the 7 highest value
numbers out of a possible 9 otherwise just add what ever is in the row

for instance
row 1 contains 94, 96, 91, 92, 95, 90, 96, 98 total should equal 662
row 2 contains 96, 97, 93, 93, 96, 91, 97 total should equal 663
row 3 contains 95, 95, 92, 94, 94, 92 total should equal 562

any help would be gratefully appreciated

Stu

--

Dave Peterson

If your amounts are in columns A through I, try using this:

H1:
=SUM(LARGE(I1:INDEX(A1:I1,LARGE(IF(A1:I1<"",COLUM N(A:I)),MIN(7,COUNT(A1:I1)))),ROW(INDIRECT("1:"&MI N(7,COUNT(A1:I1))))))

Note: To commit that array formula hold down [Ctrl] and [Shift] when you
press [Enter]

Then copy that formula down.

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Stu" wrote:

Hi all

This sounds like it should be easy but for the life of me I cant work it
out.

What I have is a row of numbers and I have to add the 7 highest value
numbers out of a possible 9 otherwise just add what ever is in the row

for instance
row 1 contains 94, 96, 91, 92, 95, 90, 96, 98 total should equal 662
row 2 contains 96, 97, 93, 93, 96, 91, 97 total should equal 663
row 3 contains 95, 95, 92, 94, 94, 92 total should equal 562

any help would be gratefully appreciated

Stu

On Mon, 10 Oct 2005 13:35:30 GMT, "Stu" wrote:

Hi all

This sounds like it should be easy but for the life of me I cant work it
out.

What I have is a row of numbers and I have to add the 7 highest value
numbers out of a possible 9 otherwise just add what ever is in the row

for instance
row 1 contains 94, 96, 91, 92, 95, 90, 96, 98 total should equal 662
row 2 contains 96, 97, 93, 93, 96, 91, 97 total should equal 663
row 3 contains 95, 95, 92, 94, 94, 92 total should equal 562

any help would be gratefully appreciated

Stu



You could use the **array** formula:

=SUM(LARGE(rng,ROW(INDIRECT("1:"&MIN(7,COUNT(A1:I1 ))))))

where rng is where your numbers are entered.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.


--ron

[snip]
Thanks to all

Stu