Function that gives the SUM minus 4 lowest numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Damar - 29 Sep 2006 18:16 GMT

Ok The SUM for each row is easy, How do I get each row to drop the lowest
value for the SUM,

Reply to this Message

NickHK - 29 Sep 2006 18:22 GMT

Damar,
=SUM(A1:F1)-MIN(A1:F1)

NickHK

"Damar" <Damar@discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:6A76F14A-FE93-45A3-B225-6892A16FD733@microsoft.com...

> Ok The SUM for each row is easy, How do I get each row to drop the lowest
> value for the SUM,

Reply to this Message

Jim Thomlinson - 29 Sep 2006 18:49 GMT

If the actual question if more like the title and you want to exclude the 4
lowest values then...

=SUM(A1:A10)-SMALL(A1:A10, 1)-SMALL(A1:A10, 2)-SMALL(A1:A10,
3)-SMALL(A1:A10, 4)

Signature

HTH...

Jim Thomlinson

> Damar,
> =SUM(A1:F1)-MIN(A1:F1)
[quoted text clipped - 4 lines]
> > Ok The SUM for each row is easy, How do I get each row to drop the lowest
> > value for the SUM,

Reply to this Message

NickHK - 29 Sep 2006 18:56 GMT

Jim,
Ahh, missed the 4 aspect. Thought it was too easy.

NickHK

"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> ¼¶¼g©ó¶l¥ó·s»D:116911C5-2E53-4ECB-B457-DDF4FCA60929@microsoft.com...

> If the actual question if more like the title and you want to exclude the
> 4
[quoted text clipped - 13 lines]
>> > lowest
>> > value for the SUM,

Reply to this Message

Tom Ogilvy - 29 Sep 2006 20:10 GMT

=sum(A1:A10)-SUM(SMALL(A1:A10,{1,2,3,4}))

entered normally might be more enticing and is functionally equivalent.

Signature

Regards,
Tom Ogilvy

> If the actual question if more like the title and you want to exclude the 4
> lowest values then...
[quoted text clipped - 10 lines]
> > > Ok The SUM for each row is easy, How do I get each row to drop the lowest
> > > value for the SUM,

Reply to this Message