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

N

newbie_excel

New Member
Joined
Feb 17, 2007
Messages
23
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.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
Yogi Anand said:
How about ...

=AVERAGE(INDEX(F:F,MATCH(LOOKUP(9.9E+307,A:A),A:A)-4):F65536)
Click to expand...
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:
Upvote 0
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.
Click to expand...

No argument there Peter ... Agreed!

Following is a solution using DAVERAGE function ...

<html><head><title>Excel Jeanie HTML</title></head><body>
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>
 
Upvote 0
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
 
Upvote 0
Gerald Higgins said:
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.
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
150
kerm007
K
K
  • Solved
Average Based on Criteria
Replies
3
Views
138
Fluff
Fluff
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
191
etaf
etaf
J
  • Solved
Average the last bank balance of each day
Replies
5
Views
412
JamesonMH
J
R
  • Solved
Very Complicated Stock Formula's that Ignore 4th, 5th, 6th, etc Day Trends and Isolate 3 Day Trends
Replies
3
Views
259
rebel123
R

Forum statistics

Threads
1,214,585
Messages
6,120,399
Members
448,957
Latest member
Hat4Life

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top