How Do I Do An Average Formula In Excel Excel Tip – Calculating A Rolling Or Moving Average Using The OFFSET Function

You are searching about How Do I Do An Average Formula In Excel, today we will share with you article about How Do I Do An Average Formula In Excel was compiled and edited by our team from many sources on the internet. Hope this article on the topic How Do I Do An Average Formula In Excel is useful to you.

Excel Tip – Calculating A Rolling Or Moving Average Using The OFFSET Function

Calculating moving averages can be a really useful way of looking at trends in your data, and we can easily set up a formula in Excel to always look at the last 3 or 6 or any number of months in your data.

They are one of the most used and popular indicators. The best place to start is to understand the most basic form of the simple moving average (SMA). No matter how long or how short a moving average you are looking to plan and track, the basic math remains the same time and time again.

Let’s consider an example. I want to know the last 3 months of average sales of my Beanie Hats…every last three months though my new monthly sales data is added to my spreadsheet.

I entered the number of months I want to use for the moving average in cell G6. (This way I can easily change the number of months I want to look at in my formula – I may want to look at 6 months or 9 months). The formula in G5 (where I want the result of my formula to display) reads –

=POWER(OFFSET(C7,COUNT(C:C)-G6,0,G6))

Date rolls

June-14 50

July-14 65

Aug-14 35

September-14 87

Oct-14 99

November-14 89

Dec-14 150

January-15 250

Feb-15 257

Mar-15 146

April 15 150

My dates are in Column B and my values ​​are in Column C and start in row 7.

Let’s break this down and see what Excel does.

First, the OFFSET function returns a list in Excel, and we want this to be the last 3 (or however many are defined in our G6 cell). OFFSET takes the following arguments −

reference, rows, calls, length, width

So, we will tell the OFFSET function to create a new range with the first cell being 7 cells below C4 (the first volume cell), and continue 3 cells down. How do you know how to start 7 cells down?

By passing COUNT(C:C)-G6 as a reference, COUNT(C:C) returns the number of cells containing numbers in range C.. in this example 10. Subtract 3 as we need the last 3.

After that…

Wrap everything up in AVERAGE. Now that we have a moving average calculated automatically by Excel, we can easily change the number of months by changing the value of Cell G6.

Video about How Do I Do An Average Formula In Excel

You can see more content about How Do I Do An Average Formula In Excel on our youtube channel: Click Here

Question about How Do I Do An Average Formula In Excel

If you have any questions about How Do I Do An Average Formula In Excel, please let us know, all your questions or suggestions will help us improve in the following articles!

The article How Do I Do An Average Formula In Excel was compiled by me and my team from many sources. If you find the article How Do I Do An Average Formula In Excel helpful to you, please support the team Like or Share!

Rate Articles How Do I Do An Average Formula In Excel

Rate: 4-5 stars
Ratings: 7913
Views: 57457346

Search keywords How Do I Do An Average Formula In Excel

How Do I Do An Average Formula In Excel
way How Do I Do An Average Formula In Excel
tutorial How Do I Do An Average Formula In Excel
How Do I Do An Average Formula In Excel free
#Excel #Tip #Calculating #Rolling #Moving #Average #OFFSET #Function

Source: https://ezinearticles.com/?Excel-Tip—Calculating-A-Rolling-Or-Moving-Average-Using-The-OFFSET-Function&id=9055606