How to? (Office 2002 SP3)

Topic

New Reply

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?

Reply | Quote

Replies

Add this ARRAY formula in B25 (confirm with ctrl-shift-enter):

=AVERAGE(IF((A2:A23>(TODAY()-5))*(A2:A23<=TODAY()),B2:B23))

Steve

Reply | Quote

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?

Reply | Quote

Jon Peltier has a site that discusses dynamic charting.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Jon Peltier has a site that discusses dynamic charting.

Reply | Quote

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()-5))*(A2:A300<=TODAY()),B2:B300))

Steve

Reply | Quote

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

Reply | Quote

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()-5))*(A2:A23(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 (while still in cell G25) then hold both and buttons while pressing the key.

Steve

Reply | Quote

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!

Reply | Quote

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:

	A	B
1	6	3
2	4	1
3	2	2

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.

Reply | Quote

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:

	A	B
1	6	3
2	4	1
3	2	2

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.

Reply | Quote

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(D4:D220)*(A4:A220>((TODAY()-1)-5))*(A4:A220<=(TODAY()-1)),D4:D220))}

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

Excel is not clairvoyant – it just looks at the two times and it has no idea that one of them might be on the next day, unless you specify that yourself. Try

=(IF(B51=0;””;C51-B51+(C51<B51))

If the time in C51 is earlier than that in B51, that in C51 is on the next day, so we have to add one day. The formula uses the fact that TRUE=1 and FALSE=0 to do this.

Reply | Quote

Of course this assumes that the day is only 1 day later

ibe98765,
In many instances, I have found, that it is best to not separate date and time. Excel easily lets you put both together in a cell and format it to display both (eg) “mmm d, yyyy h:mm AM/PM” as a format will display something like:”Jan 20, 2005 7:45 AM”

Then when you do the math, it won’t matter if it is the same day, or multiple days later.

Steve

Reply | Quote

> Of course this assumes that the day is only 1 day later

Certainly. Since the OP only records the time, that seemed a reasonable assumption. If the second time could be more than one day after the first time, there is no way to tell how many.

I agree that not separating the day and time is MUCH better.

Reply | Quote

This worked for me because the times computed are for sleep time, so they will never be farther apart than 1 day. Strangely, the one instance where this formula didn’t work (so far) was when the first time (column was exactly 12:00AM.

I’ve never thought of keeping the date and times together. Will such a format still allow an average to be computed? Remeber, I am using the array formula from above.

Reply | Quote

Yes, calculations will still work. Most times using date/times will simplify calculations since you can subtract them directly to gt time difference

Date/TIme are stored as numbers in excel. They are in units of days. The “integer part” of the date/time is the “date portion”. It is the number of days past Jan 1, 1900. The “decimal part” is the fraction of the day that is past (eg): 0 = Midnight, 0.25 = 6AM, 0.5 = Noon, 0.75 = 6PM, 1= Midnight of next day.

Any date/time subtraction gives the number of DAYS elapsed. You can format this using format cells to give hrs/min/secs in a variety of formats (though it still retains the units of days), or you can manipulate yourself by multiplying by factors. Multiply the days by 24 to give hours, 24*60 to give mins, etc

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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(D4:D220)*(A4:A220>((TODAY()-1)-5))*(A4:A220<=(TODAY()-1)),D4:D220))}

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!

Reply | Quote

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!

Reply | Quote

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()-5))*(A2:A23(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 (while still in cell G25) then hold both and buttons while pressing the key.

Steve

Reply | Quote

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

Reply | Quote

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()-5))*(A2:A300<=TODAY()),B2:B300))

Steve

Reply | Quote

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?

Reply | Quote

Add this ARRAY formula in B25 (confirm with ctrl-shift-enter):

=AVERAGE(IF((A2:A23>(TODAY()-5))*(A2:A23<=TODAY()),B2:B23))

Steve

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

If you insist on having the average of the last 5 values in column B in the same column, say, in B25 as your exhibit shows:

=AVERAGE(OFFSET($B$2,MATCH(9.99999999999999E+307,$B$2:OFFSET(INDEX(B:B,ROW()-1),0,0))-5,0,5))

You can enter this formula in a cell just below the last record indexed by the last date.

If you’re willing to calculate the average of the last 5 values in column B outside A:B, say, in C2…

=AVERAGE(INDEX($B$2:$B$65536,MATCH(9.99999999999999E+307,$B$2:$B$65536)-4):INDEX($B$2:$B$65536,MATCH(9.99999999999999E+307,$B$2:$B$65536)))

If so desired, the MATCH bit can be entered in C1… Thus:

C1:

=MATCH(9.99999999999999E+307,$B$2:$B$65536)

C2 becomes then:

=AVERAGE(INDEX($B$2:$B$65536,C1-4):INDEX($B$2:$B$65536,C1))

Reply | Quote

Just out of curiosity. You frequently use 9.99999999999999E+307 in your formulas. Do you have code that delivers that to the appropriate cursor point – or is there an Excel “function” (or shortcut thereto) that generates the characters? Or maybe you do it by hand every time, after all.

Reply | Quote

I admit I generate it by counting 9’s while typing.

But one can copy it from the Help file:

Excel specifications and limits > Calculation specifications

Reply | Quote