google sheets large - Free Excel Tutorial

April 13, 2022April 13, 2022

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

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)

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

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")

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)…

April 10, 2022April 10, 2022

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets

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 values you want to calculate the average exceeds to 3, 5 or N numeric values then what would you do? If you would tend to do this task manually, then it would be your foolish decision because doing these kinds of lengthy tasks manually is very hard, and when it comes to N numeric values, then It would not be wrong to say that this is near to impossible to do it manually.

But don’t worry about it because after carefully reading this article, you can easily calculate the average values of the last 3, 5, and most important N numeric values within seconds.

So without any further delay, let’s dive into it;

Calculate The Average Of The Last 3 Values In Google Sheets

The General Formula is as below based on the AVERAGE function, LOOKUP function, LARGE function and IF function:

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(Values_Range),ROW(Values_Range)),{1,2,3}),ROW(Values_Range), Values_Range))

Note: this above is an array formula, you need to press “CTRL+SHIFT+ENTER” short cuts to make it as array formula.

Let’s See How This Formula Works

You can use an array formula for getting the average of the last 3 values in the range. The formula in E3 in the example is as follows:

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(Values_Range),ROW(Values_Range)),{1,2,3}), ROW(Values_Range), Values_Range))

Here “ Values_Range ” refers to the designated name range B4:B11.

Average the last 3_ 5 or N numeric values in google sheets1

This is an array formula, so input it using control + Shift + enter.

Because the AVERAGE function computes an average of numbers supplied in an array, practically all of the effort in this formula is to construct an array of the latest three numeric values in a range.

=IF(ISNUMBER(Values_Range),ROW(Values_Range))

The IF function in the formula is used to “filter” numeric numbers from the inside out.

Because the ISNUMBER function returns TRUE for numeric values and FALSE for other values (including blanks), and the ROW function produces row numbers, the outcome of this operation is an array of row numbers corresponding to numeric entries:

={4;5;FALSE;7;8;FALSE;10;11}

Average the last 3_ 5 or N numeric values in google sheets1

=LARGE({4;5;FALSE;7;8;FALSE;10;11},{1,2,3})

The above array is sent to the LARGE function, using the array constants 1,2,3 for k. LARGE ignores FALSE values and returns an array with the greatest three integers, which correspond to the last three rows with numeric values:

={11,10,8}

Average the last 3_ 5 or N numeric values in google sheets1

=LOOKUP({11,10,8}, ROW(Values_Range), Values_Range))

The above array is used as the lookup value in the LOOKUP function. The ROW function provides the lookup array, and the return array is the named range “ Values_Range “:

={435,657,356}

The array of similar values in “ Values_Range ” returned by LOOKUP is then put into AVERAGE:

=AVERAGE({435,657,356})

Average the last 3_ 5 or N numeric values in google sheets1

Calculate The Average Of The Last 5 Values IN Google Sheets

Please use the following array formula, which would assist you in calculating the last 5 values in Google Sheets:

In a blank cell, type the following formula:

=IF(COUNT(B:B),AVERAGE(INDEX(B:B,LARGE(IF(ISNUMBER(B1: B10000),ROW(B1: B10000)),MIN(5,COUNT(B1: B10000)))):B10000),"no data found")

Average the last 3_ 5 or N numeric values in google sheets1

Note: B:B is the column that holds the data you used, B1: B10000 is a dynamic range that you may stretch as long as you need, and the number 5 represents the latest n values, and then press Ctrl + Shift + Enter to make the formula as array formula to obtain the average of the last 5 numbers. See the following screenshot:

And now, when you enter new numbers underneath the old data, the average is updated as well, as seen in the screenshot:

Average the last 3_ 5 or N numeric values in google sheets1

Related Functions

Google Sheets ROW function
The Google Sheets ROW function returns the row number of a cell reference.The ROW function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Google Sheets IF function
The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Google Sheets and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value],
Google Sheets ISNUMBER function
The Google Sheets ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Google Sheets LOOKUP function
The Google Sheets LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
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 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)…
Google Sheets COUNT function
The Google Sheets COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…
Google Sheets INDEX function
The Google Sheets INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Google Sheets MIN function
The Google Sheets MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Google Sheets and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
Google Sheets INDIRECT function
The Google Sheets ROW function returns the row number of a cell reference.The ROW function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Google Sheets SUBTOTAL function
The Google Sheets SUBTOTAL function returns the subtotal of the numbers in a list or database. The syntax of the SUBTOTAL function is as below:= SUBTOTAL (function_num, ref1, [ref2])….

April 10, 2022April 10, 2022

Google Sheets LARGE Function

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 value in the array. It also can be used to get the nth largest value from a range of cells or an array.

For example, the LARGE function can find the first, second, third or nth smallest value in a list or an array.

The LARGE function is a build-in function in Google Sheets and it is categorized as a Statistical Function.

Syntax

The syntax of the LARGE function is as below:

= LARGE (array,nth)

Where the LARGE function arguments are:

array -This is a required argument. An Array or a range of cells that contains numeric values from which you want to get the nth largest value.
nth – This is a required argument. The position of the number that you want to return

Note:

If the first argument is empty, then the LARGE function returns #NUM! Error.
If nth is less than or equal to 0 or if nth is greater than the number of values in array, the LARGE function returns the #NUM! Error.
If nth is the number of values in array, then LARGE (array,1) returns the largest value, and LARGE(array,n) returns the smallest value.

Google Sheets LARGE Function Examples

The below examples will show you how to use Google Sheets LARGE Function to get the largest value form the numbers in a range.

#1 To get the largest value from a range B1:B4, just using the following Google Sheets formula:

=LARGE(B1:B4,1)

The above formula returns the largest number from a range B1:B4, it returns 113.

2# To get the second largest value from the Range B1:B4, you can use the following LARGE function:

=LARGE(B1:B4,2)

The second argument nth in the above LARGE function is 2, so it will fetch the second largest function in the range B1:B4. It returns 34.

3# To get the largest value from the range B1:B4 that contains blank cells, enter into the following formula in the Cell C1:

=LARGE(B1:B4,1)

The LARGE function will ignore blank cells in the Range. You will see that the Cell B3 is a blank cell, and it is ignored by the LARGE function. The largest value is still returned as 113.

4# To get the largest value from the range B1:B5 that contains text string, specific character and logical values. Using the following LARGE formula:

=LARGE(B1:B5,1)

The LARGE function will ignore values in the range B1:B5 that contain text string, specific character and logical value.

5# To get the largest value from the range B1:B5 that contain an error, using the below formula:

=LARGE(B1:B5,1)