Dynamic lookback period/N days in Excel...? Possible?

Hi all,

I have a general problem that I'm curious if there's a smart way to solve in Excel.

Basically, I have a lot of data that I'm analyzing and I'm interested in exploring various lookback periods when calculating averages, distributions, maximum/minimum and other statistical values. For instance, I might want to have a 5 day look back period, a 20 day, 60 day or the entire sample.

Would there be an (easy) way to set up my sheets such that the lookback period can be dynamic, i.e., based on a value in a single cell or similar?

I'm attaching one sheet of stuff I'm looking at. In this sheet, I actually created charts/boxes for all the various lookback periods of interest. So, that's one way to do it, but I feel it can be a little overwhelming.

I have a lot of other sheets with similar data to create and I was wondering if it'd be easier to set it up dynamically if possible.

Thanks very much in advance!

Best,

Elijah

Hi, protonLeah,

That's great! Thanks a lot!

I just tested the Average and it seems to work, although I think the reference value in OFFSET should be A12 and not @Range, right?

Now that this seems to be possible fairly easily, the next question is if I can achieve the same with a chart? Such that I can have one chart that updates according to the value in A1?

Maybe this can be done simply by updating my Frequency count using the OFFSET function?

EDIT: It does seem like it, yes!