# 10 day Rolling Average

1. ## 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!!

2. ## Re: 10 day Rolling Average

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. ## Re: 10 day Rolling Average

Try:

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

4. ## Re: 10 day Rolling Average

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.

5. ## Re: 10 day Rolling Average

Try array formula:

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

6. ## Re: 10 day Rolling Average

Try ... C2:
Formula:
`Please Login or Register  to view this content.`

7. ## Re: 10 day Rolling Average

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

8. ## Re: 10 day Rolling Average

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

9. ## Re: 10 day Rolling Average

maybe try to use:

@TMS

or/and

@Phuocam

will be easier for you and for us

10. ## Re: 10 day Rolling Average

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)