Possible to pick out the lowest eight numbers in a row?

[Christian Borchgrevink-Vigeland]

Have Excel 2003. I have a row of about 30 columns width.

See http://www.puck.no/skien/SK221204resultat.gif for a picture of my sheet.

The numbesr represent points scored in a league. I want the total-column to
just add up all the points, and then deduct automatically the eight lowest
figures.

Possible?

Thanks

 

[Niek Otten]

Use the SMALL() function.
Suppose your data is in A1:A40. In B1:B8, set up a series of number from 1
to 8. In C1 enter the formula

=SMALL($A$1:$A$40,B1)

Fill down to C8.

Your formula:

=SUM(A1:A40)-SUM(C1:C8)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Frank Kabel]

Hi
try the following formula:
=SUM(A1:X1)-SUMPRODUCT(SMALL(A1:X1,ROW(INDIRECT("1:8"))))

 

[RagDyeR]

Another way:

=SUM((A1:AD1),-(SMALL(A1:AD1,{1,2,3,4,5,6,7,8})))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Have Excel 2003. I have a row of about 30 columns width.

See http://www.puck.no/skien/SK221204resultat.gif for a picture of my sheet.

The numbesr represent points scored in a league. I want the total-column to
just add up all the points, and then deduct automatically the eight lowest
figures.

Possible?

Thanks

 

[RagDyeR]

Superfluous parenthesizes:

=SUM(A1:AD1,-SMALL(A1:AD1,{1,2,3,4,5,6,7,8}))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


Another way:

=SUM((A1:AD1),-(SMALL(A1:AD1,{1,2,3,4,5,6,7,8})))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Have Excel 2003. I have a row of about 30 columns width.

See http://www.puck.no/skien/SK221204resultat.gif for a picture of my sheet.

The numbesr represent points scored in a league. I want the total-column to
just add up all the points, and then deduct automatically the eight lowest
figures.

Possible?

Thanks