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

If you want to find the average of the last 3 numbers, you can use AVERAGE formula in excel with INDEX & MATCH functions to retrieve the output.

AVERAGE function in excel 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, names or arrays of references that contain numbers.
INDEX: Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Syntax: =INDEX(array,row_num,column_num)

array:Array is a range of cells or table.

row_num:The row number in the array from which to return the value.

column_num:It is optional. It is the column number in the array used to return the value.
MATCH function searches for a specified item in a selected range of cells, and then returns the relative position of that item in the range.

Syntax =MATCH(lookup_value,lookup_array,match_type)

lookup_value: The value you want to look for

lookup_array: The table of data contains information from which you want to return the output.

match_type: 1,0 and -1 are three options.

1(Default): It will find the largest value in the range. List must be sorted in ascending order.

0: It will find an exact match

-1: It will find the smallest value in the range. List must be sorted in descending order.
Let us take an example:

We have some numbers in column A. We want a formula that will give the result as an average of the last three numbers. • In cell B2 the formula would be
• =AVERAGE(INDEX(A2:A10,MATCH(9.99999999999999E+307,A2:A10)-3+1):A10)
• Press Enter on your keyboard.
• The function will find the average from the last 3 values. In this way we can find average in excel from the last 3 values by using the combination of Average, Index and Match functions in Microsoft Excel.

## Users are saying about us...

1. It would be good if you explained each formula step by step, eg: what the MATCH is doing & why (including what the meaning of 9.99999999E+307) means), then what INDEX is doing & why, then what AVERAGE is doing in depth so people can actually understand what is happening. 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.