# How to Average values of Top/bottom 3 of another column

1. ## How to Average values of Top/bottom 3 of another column

I have this data set. I need to calculate average of top 3 of another column.

I googled, I couldn't figured out this. And more thing is there are blank cells as well in the data.

I am attaching a sample excel sheet. Please find the attachment.

Average values in Column B based on Top 3 values of Column A

Average values in Column C based on Top 3 values of Column D

Currently I am using conditional formatting to highlight the cells of top values and manually select adjacent cells to know the average. I would to have a formula for this. Any help would be appreciated.

Here is the snapshot of the data.

DlCkLy6.png

2. ## Re: How to Average values of Top/bottom 3 of another column

The attached workbook looks nothing like the screenshot ... It has only two columns and no CF.

3. ## Re: How to Average values of Top/bottom 3 of another column

Tried this, but only returning the average of top 1 value.
=AVERAGEIFS(B2:B11,A2:A11,LARGE(A2:A11,{1,2,3}))

^Sorry
I will upload again

4. ## Re: How to Average values of Top/bottom 3 of another column

Both Average and Averageif function returning just the top value.

I tried the same with sumif function, it works for the addition part. But to average I had to manually type in number of top values with no blank cells
=(SUMIF(A2:A11,LARGE(A2:A11,1),B2:B11)+SUMIF(A2:A11,LARGE(A2:A11,2),B2:B11)+SUMIF(A2:A11,LARGE(A2:A11,3),B2:B11))/2 >>> It's 2 because there are only two cells with values.

For now I will use this until I get a better formula.

5. ## Re: How to Average values of Top/bottom 3 of another column

Perhaps the following formula will be helpful:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

6. ## Re: How to Average values of Top/bottom 3 of another column

Or try:

=AVERAGE(IF((A2:A11=LARGE(A2:A11,{1,2,3}))*(B2:B11<>""),B2:B11))

Enter with Ctrl+Shift+Enter.

7. ## Re: How to Average values of Top/bottom 3 of another column

Originally Posted by JeteMc
Perhaps the following formula will be helpful:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.
Originally Posted by Phuocam
Or try:

=AVERAGE(IF((A2:A11=LARGE(A2:A11,{1,2,3}))*(B2:B11<>""),B2:B11))

Enter with Ctrl+Shift+Enter.
Thank you guys. Both works fine.

In the first formula,
Instead of blanks in the column B, if I put hyphen (-) it returns #VALUE!
Other than that both the formula works fine.

Thank you so much.

8. ## Re: How to Average values of Top/bottom 3 of another column

Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1