Results 1 to 15 of 23
Thread: How to? (Office 2002 SP3)

20041206, 10:31 #1
 Join Date
 Aug 2001
 Location
 Bay Area, California, USA
 Posts
 963
 Thanks
 19
 Thanked 4 Times in 4 Posts
How to? (Office 2002 SP3)
Given this spreadsheet image, I want to calculate and display a value based on a 5 day moving average (last 5 days). I want this to be an automatic function, something that doesn't require daily manual adjustments ot the formula. I can't figure out how to do this, so can anyone offer a suggestion?

20041206, 10:41 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: How to? (Office 2002 SP3)
Add this ARRAY formula in B25 (confirm with ctrlshiftenter):
<pre>=AVERAGE(IF((A2:A23>(TODAY()5))*(A2:A23<=TODAY()),B2:B23))</pre>
Steve

20041207, 09:18 #3
 Join Date
 Aug 2001
 Location
 Bay Area, California, USA
 Posts
 963
 Thanks
 19
 Thanked 4 Times in 4 Posts
Re: How to? (Office 2002 SP3)
No, that doesn't work. I think the problem might be with the B2:B23 part. The average function can't handle data that isn't filled in.
My example wasn't meant to be absolute. This speadsheet will grow. Right now, there is about 200 rows of data and most of them are empty right now. But I only need the last 5 entries for this formula (in this case, B19:B23). Yesterday, it would have been B18:B22. The day before, B17:B21. Does this make sense?

20041207, 09:48 #4
 Join Date
 Jan 2002
 Location
 Putnam Valley, New York, USA
 Posts
 113
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How to? (Office 2002 SP3)
Jon Peltier has a site that discusses dynamic charting.

20041207, 10:01 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: How to? (Office 2002 SP3)
The only blanks that would affect this are the blanks that are in the "last 5 days" and they would be summed as zero. Try this  it will ignore the blanks in the cells that are in the range:
=AVERAGE(IF(ISNUMBER(B2:B23)*(A2:A23>(TODAY()5))*(A2:A23<=TODAY()),B2:B23))
Of course change all the rows (223) to the rows you want the formula to check:
=AVERAGE(IF(ISNUMBER(B2:B300*(A2:A300>(TODAY()5))*(A2:A300<=TODAY()),B2:B300))
Steve

20041207, 10:09 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: How to? (Office 2002 SP3)
Using a dynamic range is an excellent technique. My only concern would be determining the last 5 in the list, versus the requested last 5 in time:
The dynamic range technique grows the range as the list is expanded.
The dates seem to be filled in ahead of time so they can not be used to determine the end and the col B values might have internal blanks, making countA ineffective for determining the size of the data.
Steve

20041207, 12:26 #7
 Join Date
 Aug 2001
 Location
 Bay Area, California, USA
 Posts
 963
 Thanks
 19
 Thanked 4 Times in 4 Posts
Re: How to? (Office 2002 SP3)
Adding the "isnumber" function doesn't work either. Maybe a better graphic will show what I am trying to do?

20041207, 13:03 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: How to? (Office 2002 SP3)
If you put in G25 the array formula (confirm with cntrlshiftenter):
=AVERAGE(IF(ISNUMBER(G2:G23)*(F2:F23>(TODAY()5))*(F2:F23<=TODAY()),G2:G23))
In B25:
=AVERAGE(IF(ISNUMBER(B2:B23)*(A2:A23>(TODAY()5))*(A2:A23<=TODAY()),B2:B23))
It works for me. Are you remembering to confirm with ctrlshiftenter?
If you select G25 does the formula bar show:
{=AVERAGE(IF(ISNUMBER(G2:G23)*(F2:F23>(TODAY()5))*(F2:F23<=TODAY()),G2:G23))}
[Note the squiggly brackets ({}) that excel adds (you do not add them) to indicate an array formula]
If they are not there, press edit <f2> (while still in cell G25) then hold both <control> and <shift> buttons while pressing the <enter> key.
Steve

20041207, 20:10 #9
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How to? (Office 2002 SP3)
If your list starts in B2 and there are no embedded blank entries, you can try:
=average(offset(B2,count(B2:B100)1,0,min(5,count(B2:B100)),1))
If there are embedded blank entries, then you need to tell us how those should be considered in your average. Do you still want the last 5 days (even if there may be fewer than 5 data points), or do you want the last 5 observed data points?

20041207, 22:09 #10
 Join Date
 Aug 2001
 Location
 Bay Area, California, USA
 Posts
 963
 Thanks
 19
 Thanked 4 Times in 4 Posts
Re: How to? (Office 2002 SP3)
OK, this seems to work now, as long as I do the CtrlShiftEnter after modifying the formula. Why do I need to do this? Doing this seems to put what are called "POISSON" brackets around the formula.
Thanks!

20041207, 22:26 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: How to? (Office 2002 SP3)
Confirming a formula with Ctrl+Shift+Enter makes the formula into an array formula. The curly brackets { } are Excel's way to give the user a visual clue that the formula is an array formula, not a standard formula.
What is an array formula? Here is a very simple example:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>1</td><td align=right>6</td><td align=right>3</td><td align=center>2</td><td align=right>4</td><td align=right>1</td><td align=center>3</td><td align=right>2</td><td align=right>2</td></table>
If you enter the formula =SUM(A1:A3/B1:B3) in another cell as a normal formula, the result will be #VALUE. In a normal value, dividing one range of cells by another range of cells doesn't make sense. But if you enter it as an array formula, Excel will evaluate the division cell by cell, i.e. it calculates A1/B1, A2/B2 and A3/B3, and then calculate the SUM. The result is 6/3 + 4/1 + 2/2 = 2 + 4 + 1 = 7.
If you search this forum for array formula, you will find many examples, and links to other sites providing explanations and examples.

20050109, 07:43 #12
 Join Date
 Aug 2001
 Location
 Bay Area, California, USA
 Posts
 963
 Thanks
 19
 Thanked 4 Times in 4 Posts
Re: How to? (Office 2002 SP3)
Looks like something I'm doing isn't working...
Have this data representing sleep times:
Col A............................Col D
Wed 29Dec2004....6:25
Thu 30Dec2004.....7:38
Fri 31Dec2004.......7:20
Sat 1Jan2005........7:52
Sun 2Jan2005.......7:20
Mon 3Jan2005.......6:30
Tue 4Jan2005.......7:40
Wed 5Jan2005......5:35
Sat 0Jan1900........7:00
Fri 7Jan2005.........6:20
Sat 8Jan2005........7:20
Using this formula:
{=AVERAGE(IF(ISNUMBER(D4220)*(A4:A220>((TODAY()1)5))*(A4:A220<=(TODAY()1)),D4220))}
I get the following averages:
Last 5 days.............6:31
Last 10 days...........6:57
note: (The last data element isn't used until the following day as it caused problems if there was no data filled into the space).
BUT when I select the data manually and look at the builtin average function in the status bar at the bottom of the sheet, the numbers don't match.
Instead I see:
Last 5 days.............6:37
Last 10 days...........6:58
Anyone have any ideas why my calculated averages don't match Excel's?
Thanks!

20050109, 09:48 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: How to? (Office 2002 SP3)
Your excel average is including the the 7:00 from Sat 0Jan1900 which I assume you highlighted.
The formula ignores it since it is not within 5 or 10 days of the current date.
I think you want this date to be "thurs 6Jan2005" and then both calcs should give the same results
Steve

20050109, 23:11 #14
 Join Date
 Aug 2001
 Location
 Bay Area, California, USA
 Posts
 963
 Thanks
 19
 Thanked 4 Times in 4 Posts
Re: How to? (Office 2002 SP3)
Wow. Didn't catch that myself. Yes, fixing that date, fixed the problem. Thanks... (amazing how you can stare right at something and not see it)!

20050120, 06:41 #15
 Join Date
 Aug 2001
 Location
 Bay Area, California, USA
 Posts
 963
 Thanks
 19
 Thanked 4 Times in 4 Posts
Re: How to? (Office 2002 SP3)
Another problem with this spreadsheet. Seems using the formula in the graphic, Excel can't figure out the difference when times cross from one day to another. This is coulmns A, B, C & D. Must be a way to do this easily? Anyone have an idea? Thanks...