Help!! Looking to find the largest 6 numbers in one column and sum

[tila11523]

Hello again I am having a hard time looking for a function for my needs. I am trying to find the largest 6 numbers in the Month Numbers column for each person in the attached file and sum the data (for the largest 6 in the month column) in the OT Hours column and the Instance column and divide the two (OT Hours) and (Instance). If the associate only has 5 numbers in the month number column, then I would still need to sum the OT hours and the Instances and divide the 2. Any help would be greatly appreciated. Even if I have to flip the data another way using pivot so a function would work better that would be fine. I just cant figure it out

[tila11523]

I figured out a way to get what I wanted. I flipped the data to where the Month numbers were my column headers and my Unique ID's are my row lables. I then had one tab with the instances as my totals by Month/ID and another with my OT hours as my totals by Month/ID. I then used this function =IFERROR(SUM(OFFSET(B2,0,MAX(0,LARGE(COLUMN(B2:BB2)*(B2:BB2<>""),1)-7),1,MIN(6,LARGE(COLUMN(B2:BB2)*(B2:J2<>""),1)-1))),0) and since it is an array I had to Ctrl+Shift+Enter. I then looked up by ID each sum of Instance and OT hours, divided them and TaDa!! However, if anyone has a better way I am always open to making my life easier or helping someone else out

[Glenn Kennedy]

There is an easier way to fdo this... using your original data layout... change the big 6 to another number, if a number other than the largest 6 is required...

=SUMPRODUCT((RANK($F$2:$F$37,$F$2:$F$37,TRUE)>(COUNT($F$2:$F$37)-6))*$C$2:$C$37)