I am currently using the following array formula in Excel 2007:
=IF($R2="","",INT(AVERAGE(IFERROR(LARGE($AO2:$BH2,{1,2,3,4}),0))))

This takes the average of the top four values (treating blanks as 0) between columns AO and BH in row 2 and works well.

However, I now need to vary the top X values. It won’t always be four. It will be based on a value in another column in the row, for arguments sake BJ2. So if cell BJ2 has the value of 3, we will look at the top 3 values, if it is 10 it will be the top 10 values.

The value of BJ2 will be between 0 (which will return a value of 0) and 30.

How can I vary this within the formula?
Thanks
Martin