Finding the Average in a set column using only the Top "Variable" amount of cell values

[TheSticks23]

I will try to explain this the best I can, I have been working very hard on trying to figure the solution out to this.
I have a column full of data, with 500 values filling 500 cells of course.
I need the ability to find the Average Value of these cells only using X # of the largest values.

Lets say my list looks like this

6.78
7.00
8.51
5.53
8.26
8.26
10.71
1.51
7.46
8.60
4.70

<tbody>
</tbody>

Then on another place on this excel sheet (H2) I have the Value 5 (This is always changing, could be 3 the next day)
I need a formula that will find the Average value of the Top 5 in that list. (or if the next day the top 3)
I know you can set up =Average(Large(A1:A10,{1,2,3,4,5})), But I need the ability to automate the {1,2,3,4,5} based off the number in Cell H2.

Any help would be greatly Appreciated.
Thanks gurus!
~TheSticks23~

 

[gsistek]

Try:
=AVERAGE(LARGE($A$1:$A$10,ROW(OFFSET($A$1,0,0,$H$2))))

Entered with CTRL-SHIFT-ENTER, not just ENTER.

 

[Vidar]

Or maybe:
=SUMPRODUCT(--(A1:A10>=LARGE(A1:A10,H2)),A1:A10)/H2

 

[gsistek]

Or maybe:
=SUMPRODUCT(--(A1:A10>=LARGE(A1:A10,H2)),A1:A10)/H2

This formula also side-steps the volatile OFFSET function. Well done!

 

[Vidar]

Not so sure if it works ok. I tried to calculate it manually while changing the number of duplicate values.
Both our formulas give strange results sometimes. I recommend to test them with manually calculations before
implementing the formula in the workbook.

 

[TheSticks23]

This formula also side-steps the volatile OFFSET function. Well done!

Wow, that worked perfect, thank you so much. I have been struggling since Wednesday on this.
You guys Rock!

 

[TheSticks23]

Wow, that worked perfect, thank you so much. I have been struggling since Wednesday on this.
You guys Rock!

After further review, I do not think either of these formula's are working as intended. I was very excited at first and may have jumped the gun.

 

[TheSticks23]

I will try to explain this the best I can, I have been working very hard on trying to figure the solution out to this.
I have a column full of data, with 500 values filling 500 cells of course.
I need the ability to find the Average Value of these cells only using X # of the largest values.

Lets say my list looks like this

6.78
7.00
8.51
5.53
8.26
8.26
10.71
1.51
7.46
8.60
4.70

<tbody>
</tbody>

Then on another place on this excel sheet (H2) I have the Value 5 (This is always changing, could be 3 the next day)
I need a formula that will find the Average value of the Top 5 in that list. (or if the next day the top 3)
I know you can set up =Average(Large(A1:A10,{1,2,3,4,5})), But I need the ability to automate the {1,2,3,4,5} based off the number in Cell H2.

Any help would be greatly Appreciated.
Thanks gurus!
~TheSticks23~

I should have Said Largest values in Column, not top values. Sorry for any confusion.