• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Average Top 3 Values

You are here: Home / Excel Formulas / Excel Average Top 3 Values
Excel Average Top 3 Value
November 16, 2011 by Mynda Treacy

You may already know you can find the 3rd highest value using the LARGE function, but what if you want to average the top 3 values in a long list like the one below?

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook and follow along. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Excel AVERAGE top 3You could use a PivotTable, or a fixed array within a LARGE function like this:

=AVERAGE(LARGE(sales,{1,2,3}))

Our data range is the ‘Total Sales’ column and it’s named ‘sales’.

But what if your data range sometimes has less than 3 values?

The above formula would return an error, so we need to get a bit clever…

Now, like loads of things in Excel there are many ways to skin a cat so I’m just going to show you one way.

It uses 5 different functions! But don’t be put off as you’ll learn some great tricks in this tutorial that you can apply to other problems.

Here’s the formula:

{=AVERAGE(LARGE(sales,IF(COUNTA(sales)>3,ROW(INDIRECT("1:3")), ROW(INDIRECT("1:"&COUNTA(sales))))))}

Note: remember to enter an array formula you need to press CTRL+SHIFT+ENTER together to insert the curly brackets at the beginning and end of the above formula. You don’t type them in yourself.

“YIKES”, I hear you say. Deep breath…keep reading.

Let’s break it down and translate it into English:

{=AVERAGE(the LARGEst values in the sales range, IF(the number of values in the ‘sales’ range is >3 then average the first 3, if not count the number of values and average those).}

Effectively we’re replacing the fixed array in the first formula with functions that will calculate this array on the fly, thus allowing the number of items in the array to be less than 3.

Excel AVERAGE LARGE and INDIRECT Functions

Note: you could use the AVERAGEIF function instead of the IF and AVERAGE that I have used, but if you only have 2003 you don’t have the luxury of AVERAGEIF.

So let’s look at the functions we’ve used:

AVERAGE Function

Easy peasy lemon squeezy (as my 5 year old would say). But if you don’t know AVERAGE it’s simply =AVERAGE(then enter your data range, or the numbers you want to average separated by commas)

=AVERAGE(A1:A50) or =AVERAGE(50,65,25,30,81)

LARGE Function

Also easy peasy lemon squeezy. Click here for more on the LARGE function.

COUNTA Function

Counts the cells in a range that are not empty. Click here for more on the COUNTA function.

ROW Function

Returns the ROW number of a reference. =ROW(B12) will return 12.

INDIRECT Function

The INDIRECT function allows you to create a reference to a range of cells by referencing other cells, or in this example an array. Huh? Let’s look at an example from our formula above:

INDIRECT("1:"&COUNTA(sales))

Let’s assume there are 2 values in the ‘sales’ data range. The COUNTA will return a 2.

And you use the ampersand symbol ‘&’ to join the components of your reference together, just as I’ve done above.

The result of this section of the formula is 1:2, as you can see in the Evaluate Formula box below.

Excel Audit Formula INDIRECT Function

Ok, so now we can follow what’s actually going on in this formula I want to share one other much simpler option that only applies in Excel 2007+.

It uses the IFERROR function that is not available in Excel 2003:

=IFERROR(AVERAGE(LARGE(sales,{1,2,3})),AVERAGE(sales))

The first part of the formula is the same as our simple fixed array mentioned at the beginning, and the second part allows for an error if there are less than 3 values, by simply averaging what’s left.

For more tips like this sign up to our weekly Excel Tips & Tricks newsletter below.

Excel Average Top 3 Value

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel Formulas
Previous Post:Excel Advanced Filter Unique RecordsExcel Advanced Filter Unique Records
Next Post:Cool INDEX Function TrickCool INDEX Function Trick

Reader Interactions

Comments

  1. Rajan Ghadi

    March 19, 2021 at 6:28 pm

    A2 Cell value = A (name of person)
    from B2 to F2 I have different values of marks in 5 subjects.

    I created =topavg(B2:F2,$H$1) where ‘topag’ is recursive LAMBDA

    =LAMBDA(range,n,
    IF(n=1,LARGE(range,n),
    topavg(AVERAGE(LARGE(range,n)),n-1)))

    But its not working. I want to get average of top ‘n’ numbers from given range, which I would have normally doing as =AVERAGE(LARGE(B2:F2,1),LARGE(B2:F2,2)…….)

    Requesting your help in this LAMBDA. Not sure where I am going wrong.

    Reply
    • Mynda Treacy

      March 21, 2021 at 12:04 pm

      Hi Rajan,

      I’m not sure you need a recursive LAMBDA. Please post your question and sample Excel file with several examples which illustrate why you need a recursive LAMBDA on our forum where we can help you further: https://www.myonlinetraininghub.com/excel-forum

      Mynda

      Reply

Trackbacks

  1. Excel Average IF Excluding Outliers • My Online Training Hub says:
    October 13, 2014 at 8:00 am

    […] Another member of the Average family that you might find useful is the AVERAGE LARGE formula which finds the average of the top 3 values. […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.