Average of Top N Values in Excel

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average properly based on different situations.

In this article, we will introduce you the way to calculate average of the top N numbers from a range. To approach our goal, except the main AVERAGE function, we also apply LARGE function.

EXAMPLE

Average of Top N Values 1

In this case, we want to calculate the average of the top three scores from 5-rounds competitions for each student. To calculate the average of top N values, we need the help of LARGE function.

SOLUTION

To create a formula to get average in this case, we need to know:

1) Find out the top three values by LARGE function.

2) Calculate average of the top three values by AVERAGE function.

The general formula is =AVERAGE(LARGE(Range,{1,2,…,N})).

FORMULA with AVERAGE & OTHER FUNCTIONS

In C2, we input the formula =AVERAGE(LARGE(B2:F2,{1,2,3})). After typing, press Enter, then average “98” of the top three scores is returned.

Average of Top N Values 1

For Kara, five scores are saved in range B2:F2, the top three values are 100, 96 and 98, so the average is (100+96+98)/3=98, the formula returns correct result.

FUNCTION INTRODUCTION

The main function in this formula is “AVERAGE”, it returns the average of the top three values by our demands; “LARGE” is used for searching for the top three values among all given values.

 a. LARGE function returns the Kth largest number from a given set of numbers or a range reference.

Syntax:

=LARGE(array,k)

 Example:

Average of Top N Values 1

//If we input 2 for, only the second top value is returned;

//If we want to returning more than one top value, we can set an array constant as the function’s second argument;

//If we input {1;2} for k, 1 and 2 are separated by “;”, the top two values are saved in the same column;

//If we input {1,2} for k, 1 and 2 are separated by “,”, the top two values are saved in the same row.

 

b. AVERAGE function returns the average of numbers from a given range reference.

Syntax:

=AVERAGE(number1, [number2], …)

 

Example:

Average of Top N Values 1

//For arguments “number1, number2,…”, they can be a set of numbers, or an array of numbers like {1;2;3}.

 FORMULA EXPLANATION

=AVERAGE(LARGE(B2:F2,{1,2,3}))

//This formula contains 2 functions, we explain functions from inside to outside.

a. For LARGE(B2:F2,{1,2,3}), range B2:F2 provides all scores from 5 rounds competitions; array constant {1,2,3} is the second argument “k” value, it determines the count of top numbers returned by LARGE.

In this case, B2:F2 is {}, LARGE returns below array:

LARGE({100,90,95,96,98},{1,2,3}) -> {100,98,96}

b. Array {100,98,96} from last step goes into AVERAGE calculation now.

=AVERAGE({100,98,96})

So, we get 98 properly after above two steps.

Related Functions


  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel LARGE function
    The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…

 

Related Posts
Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative ...

Sort Positive Numbers and Negative Numbers by Absolute Values

If both positive numbers and negative numbers exist in the same column, when sorting them by absolute values, we can sort them with the help of ABS function and helper column. In this article, we will show you the way ...

Get Employee Information by VLOOKUP

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today, in this article, we will show you the way to apply VLOOKUP to retrieve employee information. I hope this article will help you in ...

VLOOKUP with Two Lookup Tables

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 ...

VLOOKUP with Multiple Lookup Values

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP Data by Date

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Workbook
VLOOKUP - Retrieve Data from Another Workbook 1

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Worksheet
VLOOKUP - Retrieve Data from Another Worksheet 3

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

Case Sensitive Lookup with SUMPRODUCT and EXACT

Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios ...

Sidebar