Finding the sum of a row - the lowest 3 values

Hi there,
I am trying to figure out how to find the sum of a row minus the lowest
3 scores for a tournament I am running.
Basically we have points for each race and at the end of the season we
drop the 3 worst scores. Can this be *easily* done in Excel?
Cheers,
Nathan

=SUM(A1:Z1)-SUM(SMALL(A1:Z1,{1,2,3}))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"Nathan Carter" <[email protected]> wrote in message
news:[email protected]...
> Hi there,
> I am trying to figure out how to find the sum of a row minus the lowest 3
> scores for a tournament I am running.
> Basically we have points for each race and at the end of the season we
> drop the 3 worst scores. Can this be *easily* done in Excel?
> Cheers,
> Nathan

=SUMPRODUCT(--(LARGE(2:2,ROW(INDIRECT("1:"&COUNT(2:2)-3)))))

--
HTH

Bob Phillips

"Nathan Carter" <[email protected]> wrote in message
news:[email protected]...
> Hi there,
> I am trying to figure out how to find the sum of a row minus the lowest
> 3 scores for a tournament I am running.
> Basically we have points for each race and at the end of the season we
> drop the 3 worst scores. Can this be *easily* done in Excel?
> Cheers,
> Nathan

Following Ken's good lead:

=SUM(A1:Z1,-SMALL(A1:Z1,{1,2,3}))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ken Wright" <[email protected]> wrote in message
news:%[email protected]...
> =SUM(A1:Z1)-SUM(SMALL(A1:Z1,{1,2,3}))
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------*------------------------------*------------
----
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------*------------------------------*------------
----
>
> "Nathan Carter" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there,
> > I am trying to figure out how to find the sum of a row minus the lowest
3
> > scores for a tournament I am running.
> > Basically we have points for each race and at the end of the season we
> > drop the 3 worst scores. Can this be *easily* done in Excel?
> > Cheers,
> > Nathan
>
>

LOL - Should have gone there myself - Nice pickup :-)

Regards
Ken..............

"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> Following Ken's good lead:
>
> =SUM(A1:Z1,-SMALL(A1:Z1,{1,2,3}))
>
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Ken Wright" <[email protected]> wrote in message
> news:%[email protected]...
>> =SUM(A1:Z1)-SUM(SMALL(A1:Z1,{1,2,3}))
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ------------------------------*------------------------------*------------
> ----
>> It's easier to beg forgiveness than ask permission :-)
>> ------------------------------*------------------------------*------------
> ----
>>
>> "Nathan Carter" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi there,
>> > I am trying to figure out how to find the sum of a row minus the lowest
> 3
>> > scores for a tournament I am running.
>> > Basically we have points for each race and at the end of the season we
>> > drop the 3 worst scores. Can this be *easily* done in Excel?
>> > Cheers,
>> > Nathan
>>
>>
>

Ragdyer wrote:
> Following Ken's good lead:
>
> =SUM(A1:Z1,-SMALL(A1:Z1,{1,2,3}))
>
Thanks guys, worked like a treat