Saturday, October 31, 2015

yogi_Compute Row By Sum Of Upto 5 Largest Scores From A Maximum Of Last 10 Scores

Google Spreadsheet   Post  #1981
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Oct-31-2015
post by  Timothy Kerrigan:!topic/docs/d_7RUaQb8Vw;context-place=forum/docs
Defining a dynamic range
I need help defining a range. The SS has 25 rows, and up to 55 columns of scores. There is a row for each individual, and their scores are recorded in columns by date. The individuals do not have scores in every column. I want to compute the average of the largest 5 of each individual's last 10 scores.

I am using the formula: =Average(Large(G5:Z5,1),Large(G5:Z5,2),Large(G5:Z5,3),Large(G5:Z5,4),Large(G5:Z5,5))

As I add scores, the range will change. It will be a lot of work to change them manually. I am looking for a way define a range from column G, to the column that has the column that has the 10th score, for each row.
in the following solution I used the sample data from my fellow Top Contributor Chris Hicks -that he used while providing a response to question by Timothy - Thanks Chris for setting up the spreadsheet so nicely