How to? (Office 2002 SP3)

[ibe98765]

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?

[sdckapr]

Add this ARRAY formula in B25 (confirm with ctrl-shift-enter):
<pre>=AVERAGE(IF((A2:A23>(TODAY()-5))*(A2:A23<=TODAY()),B2:B23))</pre>


Steve

[ibe98765]

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?

[Eowyn]

Jon Peltier has a site that discusses dynamic charting.

[sdckapr]

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 (2-23) to the rows you want the formula to check:

=AVERAGE(IF(ISNUMBER(B2:B300*(A2:A300>(TODAY()-5))*(A2:A300<=TODAY()),B2:B300))

Steve

[sdckapr]

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

[ibe98765]

Adding the "isnumber" function doesn't work either. Maybe a better graphic will show what I am trying to do?

[sdckapr]

If you put in G25 the array formula (confirm with cntrl-shift-enter):
=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 ctrl-shift-enter?
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

[chipshot]

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?

[ibe98765]

OK, this seems to work now, as long as I do the Ctrl-Shift-Enter 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!

[HansV]

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.

[ibe98765]

Looks like something I'm doing isn't working...

Have this data representing sleep times:
Col A............................Col D
Wed 29-Dec-2004....6:25
Thu 30-Dec-2004.....7:38
Fri 31-Dec-2004.......7:20
Sat 1-Jan-2005........7:52
Sun 2-Jan-2005.......7:20
Mon 3-Jan-2005.......6:30
Tue 4-Jan-2005.......7:40
Wed 5-Jan-2005......5:35
Sat 0-Jan-1900........7:00
Fri 7-Jan-2005.........6:20
Sat 8-Jan-2005........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 built-in 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!

[sdckapr]

Your excel average is including the the 7:00 from Sat 0-Jan-1900 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 6-Jan-2005" and then both calcs should give the same results

Steve

[ibe98765]

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)!

[ibe98765]

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...