CODEWITHSUNDEEP
excelms-officeexcel-formulaspreadsheet
PhillyStafford
PhillyStafford

Reputation: 697

Excel - Sum highest n values in horizontal cells which aren't beside each other

I have a spread sheet to calculate golf scores and handicaps. There is 8 games. Player of the year is calculated on a players top 5 scores. I need a formula which will sum the top 5 values of a players points. The players points are in the blue columns and the best 5 total points are stored in the yellow AR column.

enter image description here

Now, I am not an Excel expert but here is my attempt at a solution.

The problem is that I have to create a custom range for every player (which is not cool):

=Sheet1!$E$2,Sheet1!$J$2,Sheet1!$O$2,Sheet1!$T$2,Sheet1!$Y$2,Sheet1!$AD$2,Sheet1!$AI$2,Sheet1!$AN$2

And then I have to create a unique formula for each individual player (which is also not cool):

=LARGE(Bergin_Martin,1)+ LARGE(Bergin_Martin,2)+ LARGE(Bergin_Martin,3)+ LARGE(Bergin_Martin,4)+ LARGE(Bergin_Martin,5)

This works ok but it's not ideal if I want to add new players. Does anyone know of a formula which I can use without the need to create 1) a unique range for each player and 2) a unique formula to sum up the unique ranges?

Upvotes: 0

Views: 453

Answers (1)

Chris Haas
Chris Haas

Reputation: 55417

(comment converted to answer)

When an Excel Parameter takes an Array you can pass in a dynamically generated one by wrapping the columns in parenthesis and separating each with commas. This allows you to use the slightly shorter but draggable:

=LARGE((E2,J2,O2,T2,Y2,AD2,AI2,AN2),1) + LARGE((E2,J2,O2,T2,Y2,AD2,AI2,AN2),2) + LARGE((E2,J2,O2,T2,Y2,AD2,AI2,AN2),3) + LARGE((E2,J2,O2,T2,Y2,AD2,AI2,AN2),4) + LARGE((E2,J2,O2,T2,Y2,AD2,AI2,AN2),5)

And then as @tigeravatar pointed out, you can use an Array Formula for the second parameter of LARGE that SUMPRODUCT will apply through and calculate giving you the much shorter:

=SUMPRODUCT(LARGE((E2,J2,O2,T2,Y2,AD2,AI2,AN2),{1,2,3,4,5}))

Upvotes: 1

Related Questions