Hi
I need a little help. I am working with a spreadsheet in which people will be adding to a range of data by date.
I would like to have 3 different cells that calculate the following:
Last 3 Day Average
Last 5 Day Average
Last 10 Day Average
My dilema is that the last 3 Day Average is only good until a person adds to the range. Is there a way a formula can be written to determine the range of data is correct?
Here is my example layout. Please take a look at the spreadsheet if I didnt make myself clear. I am trying to write this formula in cell B2, B3, B4.
Monday
Last 3 Day Average 1363
Last 5 Day Average 1491.8
Last 10 Day Average 1610.8
Overall Average 1596.916667
Date Sales
05/04/2009 1500
05/11/2009 1555
05/18/2009 1675
05/25/2009 1755
06/01/2009 1825
06/08/2009 1694
06/15/2009 1700
06/22/2009 1645
06/29/2009 1725
07/06/2009 1844
07/13/2009 1200
07/20/2009 1045
07/27/2009
08/03/2009
08/10/2009
Thanks
excelminow
use this functions to do this task
in B2
=AVERAGE(OFFSET($B$8,MATCH(1,ISBLANK($B$9:$B$300)*1,0)-VALUE(MID(A2,LEN("last "),3)),,20))
confirm w/ mctrl+shift+enter
non-array
=AVERAGE(INDEX($B$9:$B$65536,COUNTA($B$9:$B$65536)-(VALUE(MID(A2,LEN("last "),3))-1)):$B$65536)
just press enter
place one of these formulas in B2 and copy 'em down...
Люди, питающие благие намерения, как раз и становятся чудовищами.
Regards, «Born in USSR»
Vusal M Dadashev
Baku, Azerbaijan
Works great!!! Thanks!!!
If it meets your requirements, mark the thread as "SOLVED", and don't forget to add reputation by pressing blue scales on the right of your screen
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Люди, питающие благие намерения, как раз и становятся чудовищами.
Regards, «Born in USSR»
Vusal M Dadashev
Baku, Azerbaijan
I was wondering is there an easy way with the spreadsheet that I have created to figure a 5 day average from the previous year based on todays date.
If you look at the spreadsheet I am trying to create this formula in B6 to H6.
Is it possible to do a vlookup based on todays date minus 365 days. I would like the 5 day average to be based on the two previous days from one year prior and the exact day from one year ago and the two days after that date from one year ago?
For example today is 9/16/2009 (cell A1) I am trying to get the formula in cell B6 to display this result of 3900.68. Which is a result of an average of 9/1/08, 9/8/08, 9/15/08, 9/22/08, 9/29/08 or (B10 to B14).
The result in cell C6 to be 4011.41.
Thanks
excelminow
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks