# Exclude Scores

#### SCgrant327

##### New Member
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!

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

##### MrExcel MVP
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

##### Well-known Member
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

##### Well-known Member
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

##### New Member
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.

##### MrExcel MVP
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

##### New Member
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.

##### MrExcel MVP
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

##### New Member
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

##### New Member
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.

Replies
25
Views
2K
Replies
0
Views
297
Replies
2
Views
934
Replies
1
Views
370
Replies
3
Views
208

1,191,501
Messages
5,986,922
Members
440,067
Latest member
Swatts1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back