Average top 100 using two conditions

[markw0411]

I have a set of data and I need to take the average of the top 100 values that meet two conditions. I am know I can use averageifs to find average based on multiple conditions and I can use average(large,row()) to find top 100. But how do I combine??

 

[Fennek]

Hello,

the "Autofilter" offers a top-n, with "visible Cells" or =aggregate() the average can be calculated

regards

 

[markw0411]

I apologize I do not understand where you are going with your recommendation. Attached is a portion of my data to better describe what I need to accomplish. Obviously there is a easy way to do this manual, but I am looking to automate as this is a big data set that is updated with new data monthly.

I need to average top 100 that have month = January and Site Status = Production

[TABLE="width: 1283"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Site Status[/TD]
[TD]Unit A[/TD]
[TD]Unit B[/TD]
[TD]Unit C[/TD]
[TD][/TD]
[TD="colspan: 3"]Average Top 100[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Production[/TD]
[TD="align: right"]1267[/TD]
[TD="align: right"]1765[/TD]
[TD="align: right"]3568[/TD]
[TD][/TD]
[TD]Unit[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Production[/TD]
[TD="align: right"]1282[/TD]
[TD="align: right"]1741[/TD]
[TD="align: right"]3552[/TD]
[TD][/TD]
[TD]Unit A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Production[/TD]
[TD="align: right"]1362[/TD]
[TD="align: right"]1727[/TD]
[TD="align: right"]3543[/TD]
[TD][/TD]
[TD]Unit B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Production[/TD]
[TD="align: right"]1405[/TD]
[TD="align: right"]1739[/TD]
[TD="align: right"]3544[/TD]
[TD][/TD]
[TD]Unit C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]