Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 How to average top or bottom 3 values in Excel?

In Excel, we can quickly and easily calculate the average of a range of cells by using the AVERAGE function. Sometimes, we need to average the largest or smallest n values in a list of cells in random order, how can we get the average of the top or bottom n values in Excel?

Average top or bottom 3 values with formulas

Average excluding top or bottom 3 values with formulas

Average excluding top and bottom 3 values with formula


Count and sum cells based on background color / font color / conditional formatting:
Kutools for Excel’s Count by Color can quickly count and sum cells based on background color, font color. With it, you can count and sum by conditional formatting color as well.
doc count color

arrow blue right bubble Average top or bottom 3 values with formulas


In this example, I will calculate the average of largest or smallest 3 numbers, the following formulas may do you a favor.

To get the average of the largest 3 values, please enter this formula:

=AVERAGE(LARGE(A2:A20,ROW(1:3))), (A2:A10 is the data range that you want to average, 1:3 indicates the number of the largest values you need, if you want to average the largest 10 values, you just need to change it as 1:10. ), and then press Ctrl + Shift + Enter keys together to get the correct result. See screenshot:

doc-average-top-3-1

To average the smallest 3 values, you can apply this formula: =AVERAGE(SMALL(A2:A20,ROW(1:3))) (also, if you want to average the smallest 10 values, change 1:3 to 1:10), and remember to press Ctrl + Shift + Enter keys to return the right result.


arrow blue right bubble Average excluding top or bottom 3 values with formulas

Sometimes, you may need to calculate the average of other numbers excluding the top or bottom 3 values, this also can be solved with formulas in Excel.

To calculate the average numbers which exclude the top 3 values, please type this formula:

=AVERAGE(IF(A2:A20<LARGE(A2:A20,3),A2:A20))( A2:A10 is the data range that you want to average, the number 3 indicates the number of the largest value, if you want to average ignoring the largest 10 values, you just need to change 3 to 10), and then press Ctrl + Shift + Enter keys to get the result you want.

doc-average-top-3-2

To average the numbers excluding the bottom 3 values, please type this formula: =AVERAGE(IF(A2:A20>SMALL(A2:A20,3),A2:A20)) (the number 3 indicates the number of the smallest value, if you want to average ignoring the smallest10 values, you just need to change 3 to 10), and should press Ctrl + Shift + Enter keys after entering this formula.


arrow blue right bubble Average excluding top and bottom 3 values with formula

If you need to average the numbers which exclude both the top and bottom 3 values at the meanwhile, I can also talk about a formula for you.

Enter this formula: =AVERAGE(IF(A2:A20>SMALL(A2:A20,3),IF(A2:A20<LARGE(A2:A20,3),A2:A20))), (A2:A10 is the data range that you want to average, the number 3 indicates the number of the largest and smallest value, if you want to average ignoring the largest and smallest 10 values, you just need to change it to 10), then press Ctrl + Shift + Enter keys together to return the result.

doc-average-top-3-3


Related articles:

How to average last 5 values of a column as new numbers entering?

How to average every 5 rows or columns in Excel?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.