Calculating Average Of The Numbers

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed on the right article.

In this article, you will get to know the smarter ways to do these cumbersome tasks in a matter of seconds.

So without any delay, let’s dive into the article,

Average Of The Numbers By Including 0

_3 Average numbers1

General Formula

Use the formula below to calculate the average of a set of numbers in Excel:

=AVERAGE(range)

Explanations for Syntax:

  •  AVERAGE: In Excel, the AVERAGE Function can calculate the arithmetic mean of a set of numbers.
  • Range: This is the input value from your worksheet.
  • Comma symbol (,): It acts as a separator that helps to separate a list of values.
  • Parenthesis (): The main Function of this symbol is to group the elements.

Explanation

Use the AVERAGE function to calculate the average of a set of numbers.

In the example, the formula in E3 is:

=AVERAGE(A3:C3)

_3 Average numbers1

which is then copied down in the table.

AVERAGE is a built-in function in MS Excel in which you supply a range of cells to average, and this function returns the result. In cases where cells are not adjacent, you can also provide individual arguments to the Function.

The AVERAGE function automatically ignores all the blank text and cells, but zero values are included.

Average Of The Numbers By Ignoring 0

_3 Average numbers2

General Formula

The formula below will assist you in calculating the average of a number that excludes zero:

=AVERAGEIF(range,"<>0")

Explanations for Syntax:

AVERAGEIF: This Function returns the average of a set of input values that satisfy a single condition or criteria. Learn more about the AVERAGEIF Function.

Range: This is the input value from your worksheet.

Comma symbol (,): It acts as a separator that helps to separate a list of values.

Parenthesis (): The main Function of this symbol is to group the elements.

Operator (>): The supplied criteria is “>0,” which means “not equal to zero.”

Explanation

The AVERAGEIF function computes the average of a set of numbers while excluding or ignoring zero values. In the example, the formula in E10 is:

=AVERAGEIF(A10:C10,"<>0")

_3 Average numbers2

The formula in E3 in the example is based on the AVERAGE Function:

=AVERAGE(A3:C3)

Because (129+299+0)/ 3 = 166, the answer is 166.

To remove the zero from the calculated average, the formula in E10 employs the AVERAGEIF function, as shown below:

=AVERAGEIF(A10:C10,"<>0") / returns 249

The provided criterion is “>0,” which means “not equal to zero”.

Data with no values

Because the AVERAGE, AVERAGEIF, and AVERAGEIFS functions all ignore blank cells (and cells with text values), there isn’t any need to provide criteria to filter out empty cells.

Average Of The Top 3 Numbers

_3 Average numbers3

General Formula

The formula below will assist you in calculating the average of the top 3 numbers:

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

Explanations for Syntax:

  • AVERAGE: In Excel, the AVERAGE Function can calculate the arithmetic mean of a set of numbers.
  • Range: This is the input value from your worksheet.
  • Comma symbol (,): It acts as a separator that helps to separate a list of values.
  • Parenthesis (): The main Function of this symbol is to group the elements.
  • LARGE: This Function returns the Nth largest value from the given range of data. Learn more about the LARGE Function.
  • Values: This is the range of cells that contain values.

Explanation

To average the top three values in a data set, use a formula based on the LARGE and AVERAGE functions. In this example, the formula in G18 is mentioned below:

=AVERAGE(LARGE(A18:E18,{1,2,3}))

_3 Average numbers3

The LARGE function is used to find the top nth value in a set of numbers. For example, the LARGE(A18:E18,{1,2,3})will return the highest value, LARGE(A18:E18,2) will return the second-highest value, and so on:

=LARGE(A18:E18,1)/ First largest value
=LARGE(A18:E18,2)/ 2nd largest value
=LARGE(A18:E18,3)/ 2nd largest value

_3 Average numbers4

In this case, we are requesting multiple values by passing an array constant 1,2,3 into LARGE as the second argument. This causes LARGE to return an array of the top three values.

=LARGE(A18:E18,{1,2,3})

returns an array similar to this:

{299,199,54}

_3 Average numbers5

This array is passed directly to the AVERAGE function:

=AVERAGE(299,199,54) / returns 184

_3 Average numbers6

The AVERAGE Function then returns the average of these values.

Because the AVERAGE Function can handle arrays natively, there is no need to enter this formula with control + shift + enter.

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 AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • 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

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that ...

Calculate Cumulative Loan Principal Payments in Excel
Calculate Compound loan principal payments excel1

How do you Calculate Cumulative Loan Principal Payments in Excel? There are a few different ways to calculate this information, but one way has been proven to be accurate more often than others. One option is to use the CUMPRINC ...

Trap Error or Replace Error by Specific Value with IFERROR function
Trap Error or Replace Error by Specific Value with IFERROR function1

We often use Excel formulas in our work life, and we may encounter this situation that the formula throws an error and finally an error like #DIV/0! Is displayed in the cell. In today’s tutorial, we will introduce you the ...

Calculate Compound Interest in Excel

This article will show you how to calculate compound interest in Excel. It will help you calculate the interest you will accrue on a given amount of money. There are several ways to calculate compound interest. One of the simplest ...

Count Attendance and Absence with COUNTIF function
Count Attendance and Absence1

In our campus life and work life, we usually record everyone's attendance. Today we will introduce you the application of Excel COUNTIF function to count the attendance. Using a week as an example, we use the following example to show ...

Find the Closest Data to the Data Provided in Excel

In our daily work, we may encounter such an issue that to find the closest value to a certain value. In fact, Excel internal functions can help us solve this problem. In today’s article, we will show you how to ...

Average Of Numbers With Multiple Criteria In Excel

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter ...

Calculate Average Of Last 5 Or N Values In Columns
Average last N values in columns1

Suppose you come across a task where you need to calculate the average of the last 2 or 3 numeric values, then what would you do? If you are new to Excel, then your first attempt might be doing this ...

Extract or Filter on The Top N Values with Criteria

Suppose that you are in a situation where you need to filter out the top n values from the list having few values with specific criteria, or you can say that with a particular condition, and I am also pretty ...

Average of Top N Values in Excel
Average of Top N Values 1

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 ...

Sidebar