Sum X Values of a range where X can change

[markadam]

I am banging my head against the wall here. I recall seeing somewhere where this could be done, b ut can't find it again. Here is my problem:

1) I would like to sum (or average) the top X numbers from a range.
2) I realize that this can be done with the Large function and an array list, and I have done that, however, I would like more flexibility than that.
3) Ideally, I would like to input a value for x in a cell and then the formula would refer to the value and select that many from the range.

i.e. I have rows 5-20, each with 10 values. I would like to average the top 5 values of each row. I can enter 5 in cell C1 and it will return the average of the top 5 numbers for each row (think grade book with 10 assignments). So each student has an average grade based upon their best performance. Then, without changing any of the formulas, I just enter the number 7 in cell C1 and the averages now use the top 7 values instead of top 5 and I do not have to make any changes to the averaging formula at the end of each row.

Anyway, if you could please point me in the right direction, I would appreciate it.

Thanks :-) Have a great day.

mark

[daffodil11]

=SUM(LARGE(A1:A10,ROW(INDIRECT("1:"&C1))))

=AVERAGE(LARGE(A1:A10,ROW(INDIRECT("1:"&C1))))

These are both array formulas, confirmed with Ctrl+Shift+Enter

The A1:A10 is probably an incorrect range, but you can adjust it to whatever is applicable to your spreadsheet.