Sum of highest (x) numbers in a range of 6

Hi there! I know this is a pretty old thread by now, but I'd like to know how you changed it to work in rows?

I'm working with a fairly large spreadsheet of grades. I've weighted certain types of assessments, and now need to work out final grades per student. The tricky part comes in where I'd like to use the top 7 assessment scores (of a particular weight grouping, as illustrated below) per student to calculate their grades (some did better here, some there).

Your help would be greatly appreciated.

you could make a new column which identifies the largest N values, then sum those N values in yet another cell.

Here is an example:

Column A contains a set of scores. B contains the top 6 scores and C the sum of the top 6 scores

B2=LARGE(A, ROW()-1)

select B2 and fill down one row row each highest score. IF you want to identify the top six, then fill down 5 additional rows.

C2=SUM(B)

The only things I would change are:

1. The OP wants there to be a cell to tell how many scores to discard. Let's make it cell D1

2. The OP said only six scores. If we stick with only six scores (A1:A6), a formula for cell B1 that takes into account the chosen number of scores to discard would be:

=IF(ROW()-1<6-$D$1,LARGE(A$1:A$6,ROW()),"")

Which would be filled-down the column to B6

3. If the iPad vesion of Numbers supports hidden columns, hide column B. I'm not so sure it supports hidden columns, though

There's usually more than one way to skin a cat. An alternate method that is easier to understand and simpler than I previously suggested:

B1 = LARGE(A$1:A$6,ROW())

Fill down to B6

This will put all the scores in descending order

D1 = the number of scores to discard

C1=SUM(OFFSET(B1,0,0,6-D1,1))

This sums the scores in column B starting at B1 down to the last one you wanted to include, "6-D1" cells in all.

Since this is an old thread, already marked as solved, could you start a new thread?

SG

Will do, thanks 🙂