 # How to Find Average of the Last 3 Non-zero Values in Microsoft Excel 2010

If you want to find the average of the last 3 non-zero values, you can use a combination of "AVERAGE", "IF", "ROW", &" LARGE" functions to retrieve the output.

AVERAGE function can be used to find the average value or arithmetic mean of values in a selected range of cells.

Syntax: =AVERAGE(number1,number2,...)

Arguments

number1, number2,……number n are numeric values.They can be numbers or names, arrays, of references that contain numbers.

The IF function checks if a condition you specify is TRUE or FALSE. If the condition returns TRUE then it returns preset value, and if the condition returns FALSE then it returns another preset value.

Syntax = IF(logical_test,value_if_true,value_if_false)

logical_test: Logical test will test the condition or criteria.If condition meets then it returns the preset value, and if the condition does not meet then it returnsanother preset value.

value_if_true: The value that you want to be returned if this argument returns TRUE.

value_if_false: The value that you want to be returned if this argument returns FALSE
ROW: Returns the row number of a reference.

Syntax: =ROW(reference)

Reference: It is a reference to a cell or range of cells.
LARGE: Returns the kth largest value in a data set. For example, the second largest number from a list of 10 items.

Syntax: =LARGE(array,k)

array: It is an array or range of cells in a list of data for which you want to find the kth largest value.

k: It is the kth position from largest value to return in the array or range of cells.
Let us take an example:

We have some numbers in column A.We want a formula that will give the average of last 3 non-zero values. • In cell B2, the formula would be
• {=AVERAGE(IF(ROW(A2:A10)>=LARGE(IF(A2:A10,ROW(A2:A10)),3),IF(A2:A10,A2:A10)))}
• This is an array formula which requires formula to be enclosed with curly brackets by using CTRL + SHIFT + ENTER. ## Users are saying about us...

1. Hi. Great tip and i want to use it but i want to use it with criteria.

I am using tables and i want to find the average of the LAST 3 values of specific criteria.

What will the formula be if next to the values i have text (i.e. Names)and i want to find the average of the last three numbers for each "Name"?

The answer will be much appreciated

• Hello Panos,

Thanks
2. • 