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.

img1

  • 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.

img2

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. :)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube