# Moving Average...Calculate average of last 5 days?

#### newbie_excel

##### New Member
Hi my sheet looks like this:
A B C
31-May 35 94
01-Jun 38 87
02-Jun 65 89
03-Jun 56 98
04-Jun 45 78
05-Jun 34 65
06-Jun 48 93
07-Jun 29 72
08-Jun 55 87

Then in colums D and E I have more values like in columns B and C.
In column F I have the sum of rows B to E.

I'd like a formula which will calculate the average sum of these rows in the last five days, with the average changing everyday as more rows are added.

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Gerald Higgins

##### Well-known Member
There may be better ways.
Here's one way.

Code:
``````=(vlookup(large(a1:a100,1),a1:f100,6,false)
+vlookup(large(a1:a100,2),a1:f100,6,false)
+vlookup(large(a1:a100,3),a1:f100,6,false)
+vlookup(large(a1:a100,4),a1:f100,6,false)
+vlookup(large(a1:a100,4),a1:f100,6,false))/5``````

#### Peter_SSs

##### MrExcel MVP, Moderator
Given your dates seem to be in order and not repeated, maybe this?

Excel Workbook
ABCDEFGH
1Average Last 5
231-May3594129121.2
301-Jun3887125
402-Jun6589154
503-Jun5698154
604-Jun4578123
705-Jun346599
806-Jun4893141
907-Jun2972101
1008-Jun5587142
11
Average Last 5

#### Yogi Anand

##### MrExcel MVP

=AVERAGE(INDEX(F:F,MATCH(LOOKUP(9.9E+307,A:A),A:A)-4):F65536)

#### Peter_SSs

##### MrExcel MVP, Moderator

=AVERAGE(INDEX(F:F,MATCH(LOOKUP(9.9E+307,A:A),A:A)-4):F65536)
Yogi

Apart from an infinitesimal chance the user has Excel 2007 and more than 65536 rows (), this is a little susceptible to failure from a row insertion anywhere on the sheet. Naturally, a modification to, say, F60000 should eliminate that and still have more than enough rows.

newbie

Both Yogi's and my formulas could fail if blank rows are left in the data. Gerald's is more robust in that regard (provided we fix the typo in his last LARGE function to LARGE(a1:a100,5) but is quite a "mouthful"

However, given the apparent regularity of data and layout, the INDEX/MATCH functions should be OK. In the circumstances, perhaps one of these also:

Excel Workbook
ABCDEFGH
1TotalAverage Last 5
231-May3594129121.2
301-Jun3887125121.2
402-Jun6589154
503-Jun5698154
604-Jun4578123
705-Jun346599
806-Jun4893141
907-Jun2972101
1008-Jun5587142
11
Average Last 5

Last edited:

#### Yogi Anand

##### MrExcel MVP
Yogi

Apart from an infinitesimal chance the user has Excel 2007 and more than 65536 rows (), this is a little susceptible to failure from a row insertion anywhere on the sheet. Naturally, a modification to, say, F60000 should eliminate that and still have more than enough rows.

No argument there Peter ... Agreed!

Following is a solution using DAVERAGE function ...

Excel Workbook
ABCDEFGHI
1Field1Field2Field3Field4Field5Field6
231-May3594129FALSE
31-Jun3887715147
42-Jun6589154
53-Jun5698154121.2
64-Jun4578123
75-Jun346599
86-Jun4893141
97-Jun2972101
108-Jun5587142
11
Sheet4 (2)

</body></html>

#### Gerald Higgins

##### Well-known Member
Peter - you're right about the typo - doh !

One other problem with my solution, and also - I think - with Peter's and Yogi's, is that it relies on the application being exactly as described in the OP, i.e. new lines are added each day, and the relevant date is part of the added data.

If the dates pre-exist in the spreadsheet, then my solution won't work, and I don't think Peter and Yogi's will either.

EXAMPLE - if the data looks like this, they will work
1st Jan 2008 . . . 35 . . . 94 etc

data to ADD on 2nd Jan 2008
2nd Jan 2008 . . . 36 . . . 95 etc.

HOWEVER, if the data actually looks like this, they won't work
1st Jan 2009 . . . 35 . . . 94
2nd Jan 2008

Data to add on 2nd Jan
. . . . . . . . . . . . 36. . . 95

#### Peter_SSs

##### MrExcel MVP, Moderator
If the dates pre-exist in the spreadsheet, then my solution won't work, and I don't think Peter and Yogi's will either.
The formula I suggested in H3 of my previous post would cope with that OK as it does not refer to column A. However, it does rely on the data in col F being continuous as it averages the last 5 rows, not necessarily the last 5 days

#### Gerald Higgins

##### Well-known Member
Sorry Peter, I stand corrected

Replies
2
Views
194
Replies
7
Views
195
Replies
0
Views
214
Replies
23
Views
346
Replies
5
Views
195

1,191,216
Messages
5,985,319
Members
439,957
Latest member
khaled shafy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back