To calculate the average for the last N values in Excel, you can use the **AVERAGE** function together with the **OFFSET** and **COUNT** functions. Sometimes, we want to get the average value of the given array, at that time, we suggest you use the AVERAGE function. This page will show the simple formulas to** calculate the Average of last N values in Excel**. Get an official version of** MS Excel** from the following link:

https://www.microsoft.com/en-in/microsoft-365/excel

## Jump To

## General Formula

- Here, we come up with a simple formula to calculate the average of the last N values in the Excel worksheet.

**=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))**

## Syntax Explanation

In this section, we provide the explanation for the above-given formula, so that you can understand it more clearly.

**AVERAGE**– This function returns the arithmetic mean of given input numbers. Read more on the**AVERAGE Function**.**OFFSET**– In Excel, this function will return a reference to a range constructed in parts which is a starting point, a row, and a column offset, and a final height and width in rows and columns.**COUNT**– The Excel**COUNT Function**will count the number of cells that contain numbers and returns the output in number.**Range**– It represents the input value from your worksheet.**Comma symbol (,)**– It is a separator that helps to separate a list of values.**Parenthesis ()**– The main purpose of this symbol is to group the elements.

## How to Apply Formula to Calculate Average?

Here we will combine multiple functions of Excel such as **AVERAGE**, **COUNT**, and **OFFSET **to find out the average of the** **last N values in the Excel spreadsheet. For instance, we will need to calculate the average of all the marks scored by a student in each subject. Then we can apply the above-mentioned formula to find the average of those N values.

The following image will show you a **list of students** and **their corresponding scores** in each subject. Finding the average will let us know the overall performance of the student in an examination.

After that, you can **use **the **below-mentioned formula** in any cell where you want to get the output result.

**=AVERAGE(OFFSET(C4,COUNT(C4:F4),0,-3))**

Once you have entered the formula, **click **on the **Enter **button to execute it and get the result. In order to fill down the below cells, you have to drag down the first cell in the range.

If the above-given scenario is not enough to understand this formula and want to learn this with a simple example, kindly move to the upcoming section.

## Alternate Examples

Here we come up with a simple example to know how to apply this Excel formula in the spreadsheet.

- In the below example image, you can see the
**fruit sales details**in the**range B3:D7**. And we want to get the**average of the prices**given in**column D**.

- Then, enter the given formula in a cell where you want to get the result.

**=AVERAGE(OFFSET(D2,COUNT(D:D),0,-3))**

- Finally, you will need to
**click**the**Enter**button to get the result, as shown in the below screenshot.

## Conclusion

So far, we have learned various scenarios and examples to know how to apply Excel **formulas to calculate the average of the last N values in Excel**. If you have any doubts regarding this article, kindly let us know in the below **comment **section. For more updates keep in touch with our website **Geek Excel **and **Excel Formulas**.

## Video Tutorial

The following video will let you know how to apply the AVERAGE formula in the worksheet in order to find the average of the last N values crisply.

**Read Ahead:**

- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?