Average Funtion in Excel for top 5 values

[saiyamvora13]

So I would like to find the Average in a range A7:A30. Here is what I would like to get

• Average of Top 5 values
• Average of Top 2-6
• Average of Top 10.

The range is Dynamic and Sometimes in the range their could be less than 5 values.

I have used AVERAGE(LARGE(A7:A30,{1,2,3,4,5})) to get the top 5. But if I have less than 5 values I get an error.

• So if there are 3 values I would like the formula to realize that there are less than 5 values and get the average of top 3
• Sometimes I might have a value as 0. can you give me an option where it would exclude the 0 while taking average and an option that would include 0?

All help is highly appreciated.

 

[shg]

	A​	B​
6​	2​	A6: {=AVERAGE(IFERROR(LARGE(IF(A7:A30 <> 0, A7:A30), {1,2,3,4,5}), FALSE))}
7​	1​
8​	2​
9​	3​
10​	0​
11​	0​
12​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
25​
26​
27​
28​
29​
30​

The curly braces mean that the formula MUST be confirmed with Ctrl+Shift+Enter

 

[saiyamvora13]

Thanks alot SHG, this works beautifully.

 

[shg]

You're welcome.