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 k^{th} 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 k^{th} 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.**

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

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,

Please post your query @ http://www.excelforum.com and upload the excel file to get the immediate reply.

Thanks

Site Admin

What if i want the formula to work even if only the first cell has a number other than 0. I need it for the last 21 days. And so there will be times when there isn't 21 rows in the column that have data. but i need the formula to still give me a average of what ever is there. Hopefully that makes since.

if you have your 21 days in A2:A22 range then write this formula to get average of cell whose value is greater than 0

=SUM(A2:A22)/COUNTIF(A2:A22,">0")