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.