Excel Formula to Calculate Moving Average with Rolling Window Size implementation guide Correctly
I've been working on this all day and I've hit a wall trying to I recently switched to I've searched everywhere and can't find a clear answer... I'm trying to create a moving average calculation in Excel where the window size changes based on a condition. My current setup uses the following formula: ```excel =AVERAGE(A1:A10) ``` However, I want the range to dynamically adjust based on the value in cell B1. For example, if B1 contains the number 5, I want the formula to calculate the average of the last 5 entries in column A. I've tried using the INDIRECT function like this: ```excel =AVERAGE(INDIRECT("A" & (COUNTA(A:A)-B1+1) & ":A" & COUNTA(A:A))) ``` But it returns a `#REF!` behavior when B1 is greater than the number of entries in column A. I've also looked into using the OFFSET function: ```excel =AVERAGE(OFFSET(A1, COUNTA(A:A)-B1, 0, B1, 1)) ``` This seems to work for smaller datasets, but for larger ones, it slows down the performance significantly. I also noticed that if B1 is empty or zero, it throws a division by zero behavior. I'm using Excel 365 and I'm curious if there’s a better approach for handling this moving average calculation, especially to avoid performance optimization and errors. Any suggestions would be greatly appreciated! For context: I'm using Excel on Ubuntu. What am I doing wrong? This is my first time working with Excel stable.