Formulas to Calculate Average of Last N Values in Excel!

Ads

Listen to the article

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

Formula to Find Average of Last N Values

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.