Comments on: Calculating moving average in Excel

In this short tutorial, you will learn how to quickly calculate a simple moving average in Excel, what functions to use to get moving average for the last N days, weeks, months or years, and how to add a moving average trendline to an Excel chart. Continue reading →

Comments page 3. Total comments: 89

  1. Is it possible to calculate 7 Day moving average in a pivot table?

  2. Hi,

    I have a series of data going from cell B2 onwards, where B2 is the most recent. Every month I add a new column with the latest month's value. How can I create a formula which will take the average of the last x months and which will automatically take the new value I have inserted? It seems similar to the solution provided above with taking the moving average of the last N values, but I cannot seem to tailor the formula to this situation. Please help :)

    Thank you!

  3. Hi,

    Is there a way to create a spreadsheet with a formula to average 12 rolling weeks that I can also code to always put my new info in as the current week and have the data shift it's cells down by one and replace the last (or 12th) cell of data thus giving me a new rolling 12 week average?

  4. Hi, I have a similar need. I need to create a report that will show new client visits, total client visits and other data. All of those fields are updated daily on a spreadsheet, I need to pull that data for the previous 3 months broken down by month, 3 weeks by weeks, and last 60 days. Is there a VLOOKUP, or formula, or something I could do that will link to the sheet being updated daily that will also allow my report to update daily?

  5. Hello Everyone,

    Is there a way to get a sum of a number for the last 6 months?
    I want to be able to calculate the sum for the last 6 months every day. So ill need it to update every day.
    I have an excel sheet with columns of every day for the last year and will eventually add more every year.
    any help would be greatly appreciated as I am stumped!

  6. Please help with the correct formula to calculate the sum of hours entered on a moving 7 day period. For example. I need to know how much overtime is worked by an individual over a rolling 7 day period calculated from the beginning of the year to the end of the year. The total amount of hrs worked must update for the 7 rolling days as I enter the overtime hours in on a daily basis
    Thank you

    1. I am looking for something very similar! I am looking for the last 6 months! I'm so stumped!

  7. Hi, I am impressed with the vast knowledge and the concise and effective instruction you provide. I too have a query which I hope you can lend your talent with a solution as well.
    I have a column A of 50 (weekly) interval dates. I have a column B next to it with planned production average by week to complete target of 700 widgets (700/50). In the next column I sum my weekly increments to date (100 for example) and recalculate my remaining qty forecast avg per remaining weeks (ex 700-100/30).
    I would like to replot weekly a graph starting with the current week (not the beginning x axis date of the chart), with the summed amount (100) so that my starting point is the current week plus the remaining avg/week (20), and end the linear graph at end of week 30 and y point of 700.
    The variables of identifying the correct cell date in column A and ending at goal 700 with an automatic update from today's date, is confounding me.
    Could you help please with a formula? (I've been trying =IF logic with =Today and just not solving it.)
    Thank you

  8. I want to do an hourly moving average of data that has a different number of observations for each hour (2 to 6 observations).

    Can you help?

  9. any body please can help to set excl formula to get the daily moving ave of product A, B, and C for the given sample details

  10. I'm trying to create a formula to get the moving average for 3 period, appreciate if you can help pls.

    Date Product Price
    10/1/2016 A 1.00
    10/1/2016 B 5.00
    10/1/2016 C 10.00
    10/2/2016 A 1.50
    10/2/2016 B 6.00
    10/2/2016 C 11.00
    10/3/2016 A 2.00
    10/3/2016 B 15.00
    10/3/2016 C 20.00
    10/4/2016 A 4.00
    10/4/2016 B 20.00
    10/4/2016 C 40.00
    10/5/2016 A 0.50
    10/5/2016 B 3.00
    10/5/2016 C 5.00
    10/6/2016 A 1.00
    10/6/2016 B 5.00
    10/6/2016 C 10.00
    10/7/2016 A 0.50
    10/7/2016 B 4.00
    10/7/2016 C 20.00

  11. Your example 3 above (Get moving average for the last N values in a row) worked perfectly for me if the whole row contains numbers. I'm doing this for my golf league where we use a 4 week rolling average. Sometimes the golfers are absent so instead of a score, I will put "ABS" (text) in the cell. I still want the formula to look for the last 4 scores and not count the "ABS" either in the numerator or in the denominator. How do I modify the formula to accomplish this?

    1. Yes, I did notice if cells were empty the calculations were incorrect. In my situation I am tracking over 52 weeks. Even if the last 52 weeks contained data, the calculation was incorrect if any cell prior to the 52 weeks was blank.

  12. sir / mam , i want to calculate ratio of employees on the basis of "INTIME"
    so is there any specific formulas .please help!
    thank you.

  13. Hii,

    I wanted to track facebook likes on my page on daily basis. Like i want to compare today's likes with previous day's average, whether its hike or less.

  14. Hi,

    similar to CAGR which calculates compound annual growth rate in values, is there a formula for calculating the same growth between months. viz, Jan to June of the same year

  15. Wow.. Thank you so much Svetlana :)

    you made my day !!

  16. Hi .. I really liked the way it has been explained and was able to do in in a minute.. I also liked your website and will follow it regularly..

    I was working with a e excel file where i required to make a moving average of last 2 months data which were in different column i.e(D3:G2)

    Can you please let me know what modification is required in the formula so that i can make it work to calculate the same..

    Rishabh

    1. Hi Rishabh,

      Thank you for your feedback. Assuming that you need a moving average for the last 2 numbers in the same row, you can adjust the formula in this way:

      =AVERAGE(OFFSET(D3, 0, COUNT(D3:Z3)-2,1,2))

      Where D3 is the first number in the row, and 2 is the number of the last N months to be included in the average.

  17. I have 2 rows, 1st row 2015 sales Jan -Dec (per month),
    2nd row 2016 sales Jan - Dec (per month). I want to do a running average for 2016 vs 2015 (2016/2015=%). example; 2016 Jan vs 2015 Jan = %, as i add the next months sales i would need 2016 Jan + Feb vs 2015 Jan + Feb = % , and so on. Is this possible?

  18. You can use average (indirect ("b"&rows (b$1$:b1)*3-2&":"&"b"&rows(b $1$:b1)*3))

    To calculate average for column b for every 3 sets of numbers

  19. Hi nikhil,
    I can help you in this...you have to ise indirect function to achieve this task

  20. Hi,
    I have 1000 numbers starting from 1 to 1000. i want average for every 3 numbers. say from 1 to 3 it should be 2. now my next number will be 4 to 6 then 7 to 10 and so on up to 1000 numbers.

    If i use average function then it will do average for 1 to 3 then 2 to 4 then 3 to 5. it is not meeting my requirement.
    please help me to solve this case.

    1. Hi nikhil,
      I can help you in this...you have to use indirect function to achieve this task

      You can use average (indirect ("b"&rows (b$1$:b1)*3-2&":"&"b"&rows(b $1$:b1)*3))

      To calculate average for column b for every 3 sets of numbers

      ✌

  21. Hi,
    I have some daily data, with column A having dates and column B having values. I want to calculate the average of the values occurring between two specific dates, for instance between 1/1/2016 to 2/15/2016. How would I be able to do that?

  22. Hi,
    I am trying to calculate the average of a given project for the last 4 weeks, how can I do this since the cell will not always be next to each other.

  23. Hi,
    I am trying to get a moving average for the 7 previous days, the day itself and then the following 6 days (fortnight avg for a week either side of the day).
    Can you help?
    thanks

  24. Hi, I have 4 weekly figures so can work out the running total, but how do I work out an average weekly total when I'm adding new figures every week which will need to be included in the weekly figure. ie this week I need the average weekly total of 4 figures but next week it will be for 5 figures.
    Thanks

  25. I have face a problem. I want to create a sheet in excel, where 1st day(1st column contain daily score, 2nd column contains TTL score,and 3rd column contains average) as same 4th-6th column 2nd day, as same 7th-9th column. please replay me by mail,who are able to help to create it.

  26. Trying to calculate moving averages for working capital budget. ie moving averages for inventory, trade debtors and trade creditors.
    Any Help?

  27. Hi,

    I need your help on this please. I have "column A" with dates and "column B" with values which has both negative and positive values in it. i want to calculate "average" for a particular date from "columb B". Can you please help me on how to calculate average only for the positive values from "column B".

    Thank you for your help in advance.

    1. Hello, Yogi,

      Please try one of these formulas:
      =AVERAGEIF(B:B,"= 42386",A:A) or
      =AVERAGEIF(B:B,B3,A:A)

      In these formulas please replace 42386 or B3 with the needed parameter.

  28. Hi,

    I need your help on this please. I have "column A" with dates and "column B" with values which has both negative and positive values in it. i want to calculate "average" for a particular date from "columb B". Can you please help me on how to calculate.

    Thank you for your help in advance.

  29. Hi,
    I have read through several different articles looking for an answer to my problem, got lots of great tips but nothing defiant that helps me now.
    Could you please help me on this? I’m trying to create a formula that adds the total amount for a particular supplier that is more than 30 days & in another cell a formula that is more than 60 days, etc…
    I have the dates of invoices in column A, supplier’s names in column B and Total value in column C.
    Any help would be appreciated, have spent days on this & to no avail. Thanks

    1. Hi Kay,

      I think you can use the SUMIFS function. For example, to find the total for supplier "John" for orders made more than 30 days ago, you can use the following formula:

      =SUMIFS(C2:C100, B2:B100, "john", A2:A100,"<"&TODAY()-30)

      For the detailed explanation of SUMIFS' arguments and more formula examples, please see How to use Excel SUMIFS and SUMIF with multiple criteria.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)