Hey everyone! I’m working on a Google Sheets project and I’m stuck. I need to figure out how to compute a rolling average for the last 3 periods, but it needs to be based on the ‘ID’ column. I’ve got my data set up like this:
ID Amount Rolling Avg (3 periods)
1 12 12.00
1 19 12.00
1 19 15.50
1 18 16.67
1 13 18.67
2 11 11.00
2 18 11.00
2 15 14.50
The ‘Rolling Avg’ column is what I’m trying to calculate. It should reset for each new ID. Any tips on how to set this up in Google Sheets? I’m pretty new to this stuff, so a simple explanation would be awesome. Thanks in advance for your help!
As someone who’s dealt with similar challenges, I can share a trick that might help. Instead of using complex formulas, I found it easier to create a helper column. Let’s call it ‘Count’. In this column, use a formula like:
=IF(A2=A1, D1+1, 1)
This counts consecutive occurrences of each ID. Then, for your rolling average, try:
=AVERAGEIF(A$2:A2, A2, B$2:B2)
This calculates the average for matching IDs, naturally resetting for new ones. It’s not perfect for exactly 3 periods, but it’s simpler and often good enough for most purposes. You can tweak it further if needed. Hope this helps streamline your process!
hey there! i’ve got a quick trick for ya. try this formula:
=AVERAGEIF(A$2:A2,A2,B$2:B2)
it’ll give u the rolling avg for each ID. it’s not perfect for exactly 3 periods, but it’s simple and works pretty well. u can tweak it if u need. hope this helps!
When calculating a conditional rolling mean in Google Sheets, one effective method is to use the FILTER function to select only the rows that share the same ID, and then apply the AVERAGE function to those filtered values.
For example, you can use the formula:
=IFERROR(AVERAGE(FILTER($B$2:B2, $A$2:A2=A2)), B2)
Place this formula in cell C2 and drag it down. This approach ensures that the average is computed only for rows with the current ID, effectively resetting the calculation when a new ID appears. Adjust the cell references accordingly if your data does not start in row 2.