Exclude Scores

[SCgrant327]

I am working on a scoring spreadsheet. I would like to know how to exclude some scores from a SUM.

Example:

You compete in 16 events, but only your top 12 places will count toward your overall score.

What Excel functions should I use to Sum only the top 'n' scores?

Thanks!

 

[Aladin Akyurek]

I am working on a scoring spreadsheet. I would like to know how to exclude some scores from a SUM.

Example:

You compete in 16 events, but only your top 12 places will count toward your overall score.

What Excel functions should I use to Sum only the top 'n' scores?

Thanks!

Control+shift+enter...

=SUM(LARGE(Range,ROW(INDIRECT("1:"&MIN(COUNT(Range),12)))))

 

[schielrn]

Depends on where the numbers are that you are trying to sum, but this would work if they are in column a1:a12. If not just tweak to modify formula.

Try:

=SUM(LARGE(A1:A16,{1,2,3,4,5,6,7,8,9,10,11,12}))

 

[jimrward]

you could use SUM and either LARGE or SMALL

SUM all 16 and remove the worst 4, if 1st place is a high score then use SMALL, if top place is low to high then use LARGE

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

Change SMALL for LARGE according to your scoring method

 

[SCgrant327]

Ok now for a bit more complicated stuff

Thanks guys,

Now I need to SUM the score based on the top 66% of your scores.

 

[Aladin Akyurek]

Re: Ok now for a bit more complicated stuff

Thanks guys,

Now I need to SUM the score based on the top 66% of your scores.

Control+shift+enter...

=SUM(LARGE(range,ROW(INDIRECT("1:"&ROUND(COUNT(range)*0.66,0)))))

 

[SCgrant327]

Re: Ok now for a bit more complicated stuff

Thanks guys,

Now I need to SUM the score based on the top 66% of your scores.

Control+shift+enter...

=SUM(LARGE(range,ROW(INDIRECT("1:"&ROUND(COUNT(range)*0.66,0)))))

OK, but the way I see it, would this not require the data to be sorted? My data cannot be sorted by score, rather it is necessary to have it entered 'naturally'...according to date and race.

 

[Aladin Akyurek]

Re: Ok now for a bit more complicated stuff

Thanks guys,

Now I need to SUM the score based on the top 66% of your scores.

Control+shift+enter...

=SUM(LARGE(range,ROW(INDIRECT("1:"&ROUND(COUNT(range)*0.66,0)))))

OK, but the way I see it, would this not require the data to be sorted? My data cannot be sorted by score, rather it is necessary to have it entered 'naturally'...according to date and race.

Sorting is not a prerequisite...

 

[SCgrant327]

Re: Ok now for a bit more complicated stuff

Thanks guys,

Now I need to SUM the score based on the top 66% of your scores.

Control+shift+enter...

=SUM(LARGE(range,ROW(INDIRECT("1:"&ROUND(COUNT(range)*0.66,0)))))

OK, but the way I see it, would this not require the data to be sorted? My data cannot be sorted by score, rather it is necessary to have it entered 'naturally'...according to date and race.

Sorting is not a prerequisite...

By your formula, it looks like I will be getting scores from cell A1 through A-something (depending on the total number of scores). I do not want this, but rather want to be able to have the HIGHEST 66% of your scores. The scores are in the same row, but are not contiguous...or sorted. The cells containing the scores are contiguous, but there may be scores of zero if you did not compete that day...

 

[SCgrant327]

Maybe a better explanation

There are 24 possible events to participate in, 6 days of 4 events. If you compete in more than 66%, then your best 66% placements will count toward an overall. If you compete in less than 66%, then you will not be considered for the overall.

So, what I need to do is calculate the sum of your best 66% scores. The scores are in the same row, from H4::AE4. If you compete in less than the total 24 races, your score will be 0 for those that you did not compete in and those will NOT count toward your 66% participation.