Calculating Average Of The Numbers in Google Sheets

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 numbers in google sheets1

General Formula

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

=AVERAGE(range)

Explanations for Syntax:

  •  AVERAGE: In Google Sheets, 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 numbers in google sheets1

which is then copied down in the table.

AVERAGE is a built-in function in Google Sheets 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 numbers in google sheets1

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 E9 is:

=AVERAGEIF(A9:C9,"<>0")

_3 Average numbers in google sheets1

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

General Formula

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

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

Explanations for Syntax:

  • AVERAGE: In Google Sheets, 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 G15 is mentioned below:

=ArrayFormula(AVERAGE(LARGE(A15:E15,{1,2,3})))

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

=LARGE(A15:E15,1)/First largest value
=LARGE(A15:E15,2)/ 2nd largest value
=LARGE(A15:E15,3)/ 2nd largest value

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(A15:E15,{1,2,3})

returns an array similar to this:

{299,199,54}

This array is passed directly to the AVERAGE function:

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

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

  • Google Sheets AVERAGE function
    The Google Sheets AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Google Sheets AVERAGEIF function
    The Google Sheets 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])….
  • Google Sheets LARGE function
    The Google Sheets 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

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets
Average the last 3_ 5 or N numeric values in google sheets1

As an Google sheets user, you might have come across a task in which you need to calculate the average values of the last 2 numeric values, and you might have done this task manually but suppose if the last ...

Google Sheets LARGE Function
google sheets LARGE function1

This post will guide you how to use Google Sheets LARGE function with syntax and examples in Google Spreadsheet. Description The Google Sheets LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest ...

Add Workdays in Google Sheets
Add workdays no weekends in google sheets1

To add or subtract workdays days from a date, respecting holidays but assuming a 7-day week: you can use the WORKDAY.INTL function with an argument that tells it how many days should be subtracted/added as well as which specific holidays ...

Add Row Numbers And Skip Blanks in Google Sheets
Add Row Numbers And Skip Blanks in google sheets1

Do you ever have to input a list of numbers into a spreadsheet in Google Sheets, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have ...

Add Months To Date In Google Sheets
Add Months To Date in google sheets1

It is important to adjust time periods when performing financial modeling. In Google Sheets, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months ...

Add Leading Zeros in Google Sheets
Add Leading Zeros To Numbers in google sheets1

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

Add Minutes to Time in Google Sheets
Add Minutes to Time in google sheets1

Adding whole hours or decimal minutes to a given time in google sheets is a fairly simple process. You can do this by using the Start_time+TIME(0,minutes,0) and Start_time+minutes/1440 functions. In this add minutes to time guide, we will show you ...

Add Hours to Time in Google Sheets
Add hours to time in google sheets1

Adding whole hours or decimal hours to a given time in Google Sheets is a fairly simple process. You can do this by using the Start_time+TIME(hours,0,0) and Start_time+hours/24 functions. In this add hours to time guide, we will show you ...

Count Cells that are Case Sensitive in Google Sheets
counting the cells that are case sensitive in google sheets1

If you are a frequent user of Google Spreadsheets, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Average Last N Values in Google Sheets
Average last 5 values in google sheets1

Average is a valuable function in google sheets that allows you to rapidly compute the average of the past N values in a column. However, you may need to insert new numbers beneath your original information from time to time, ...

Sidebar