10 day Rolling Average

Hi guys,

I have a table of data where the first row contains the date headers, and the first column represents lot numbers which are being sold.
The amounts sold are displayed horizontally by day, so we can reconcile our sales.

I would like to calculate the moving average of the last 10 days, and have it update that rolling average as we add in new data to the next day.

I have tried various functions using INDEX and OFFSET, but can't seem to get it to work for me.

The other versions of the dynamic moving average formulas are for a single data set, where the moving average is calculated on a separate row, rather than on the same row.

My values to calculate the moving averages are in the array E2:LR2.

I've tried this one:
=AVERAGE(INDEX(E2:LR2,LARGE(COLUMN(E2:LR2)*(E2:LR2<>""),10)):LR2)
but this does not seem to work.

E2:LR2 represents the days or the year for us to record sales data.

Any help would be greatly appreciated!!

Try:

=AVERAGE(OFFSET(E2,0,COUNT(E2:LR2)-1,1,-MIN(10,COUNT(E2:LR2))))

Posting sample.
The yellow highlighted cells are the last 10 days of input values to calculate the moving average.
As I post more information into the empty cells representing future dates, I want that 10 day to slide forward to include only the 10 most recent values averaged.

Thanks for your help!

Try array formula:

=AVERAGE(IF(COLUMN(E2:LR2)*(E2:LR2<>"")>=LARGE(COLUMN(E2:LR2)*(E2:LR2<>""),MIN(10,COUNT(E2:LR2))),E2:LR2))

This one works perfectly, and also helps with 0 values, which the previous post did not.

Thanks for your help!!

maybe try to use:

@TMS
and your text

or/and

@Phuocam
and your text

will be easier for you and for us

It was the array formula:
'=AVERAGE(IF(COLUMN(E2:LR2)*(E2:LR2<>"")>=LARGE(COLUMN(E2:LR2)*(E2:LR2<>""),MIN(10,COUNT(E2:LR2))),E2:LR2))

Posted by Phuocam

Thank you.