Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 ctrl-shift-enter):
    <pre>=AVERAGE(IF((A2:A23>(TODAY()-5))*(A2:A23<=TODAY()),B2:B23))</pre>


    Steve

  3. #3
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    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?

  4. #4
    2 Star Lounger
    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.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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 (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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    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?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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 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

  9. #9
    2 Star Lounger
    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?

  10. #10
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    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 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!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  12. #12
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    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 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!

  13. #13
    WS Lounge VIP sdckapr's Avatar
    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 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

  14. #14
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    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)!

  15. #15
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    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...

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •