Excel rolling-year calculations

Having found nothing online that helped, here is my attempt at rolling-year calculations based on certain criteria. This could be used for personnel data (days worked, off etc in any given time period – in this case a year) or product sales data (how many sold in any rolling year).

The attached excel sheet shows the rough workings (it hasn’t been cleaned or made generic etc). Your mileage may vary.


Briefly, the data needs to be ordered by unique ID (column B) and by date (column C). The value you want to use is in column E. The method is as follows:

  • Column F: returns the row number of the 1st instance of every unique ID (column B)
  • Column G: returns a range of the dates (column C) for each row, changing for each new unique ID
  • Column H: returns a range of values (column E) for each row, changing for each new unique ID
  • Column I: determines the count of instances in any 12 month period (can be altered by changing the number of days in the formula)
  • Column J: a bit of logic to determine if a criteria has been met
  • Column K: if the criteria is met a sum over the range in column H is calculated
  • Column L: computes a rolling-year total
  • Column M: another criteria check

Leave a comment if any of this needs explaining. If I have the time I’ll write a better description.

1 reply
  1. Dileep
    Dileep says:

    How to calculate the rolling retun on weekly basis(like monday to monday).

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.