Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Dynamic Summation

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Lakewood, Colorado
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am looking for a way to create a table whereby I enter a date and have the function look at the column next to that date (with a value for hours) and sum the hours for that date plus the six previous dates.

    Like this:

    Date Hours
    4/1 5.0
    4/2 4.5
    4/3 7.5
    4/4 9.0
    4/5 10.5
    4/6 0.0
    4/7 6.0
    4/8 5.5
    4/9 4.0

    I would enter the date (4/8) and it would calculate the sum of the hours for 4/8 backward 7 days. If I change the date, it would recalculate back the correct number of days no matter what date I entered. I also need to set up for the previous 30 days, 90 days and 365 days.

    Anyone have any ideas?

    Thanks

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-28 20:41, btolfree wrote:
    I am looking for a way to create a table whereby I enter a date and have the function look at the column next to that date (with a value for hours) and sum the hours for that date plus the six previous dates.

    Like this:

    Date Hours
    4/1 5.0
    4/2 4.5
    4/3 7.5
    4/4 9.0
    4/5 10.5
    4/6 0.0
    4/7 6.0
    4/8 5.5
    4/9 4.0

    I would enter the date (4/8) and it would calculate the sum of the hours for 4/8 backward 7 days. If I change the date, it would recalculate back the correct number of days no matter what date I entered. I also need to set up for the previous 30 days, 90 days and 365 days.

    Anyone have any ideas?

    Thanks
    Assume your sample data is in A1:B10 including labels.

    In E1 enter: 7 [ your number of days criterion ]
    In D2 enter: 4/8 [ your criterion date ]

    In E2 enter:

    =SUM(OFFSET($B$2,MATCH(D2,A:A,0)-E1-1,0,E1,1))

    Note. The formula does not check whether there are at least E1 days in the data area.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi.
    I can help you but...
    ...perhaps this can be accomplished with several lookup and reference functions(which I know very little about)
    If you'd like, E-mail the sheet to TsTom@Hotmail.Com along with any other details that you feel would help me help you.
    Tom

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That works nice Aladin...
    Now all I have to do is figure out how that formula does what it does...
    Tom

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-28 21:03, TsTom wrote:
    That works nice Aladin...
    Now all I have to do is figure out how that formula does what it does...
    Tom
    If stuck, just call me in.

    Aladin

  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    Lakewood, Colorado
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks!! It works great. Now maybe I should figure out a way to check to make sure there is data in those cells. This program is to calculate pilot flying time to ensure I am under the maximum number of flying hours for a 7 day, 30 day, 90 day and 365 day period. I can enter my flight hours and have it keep track. Then when I need to check my previous time period, I can plug in the end date and the number of days in the past to check and verify my hours.

    Thanks again

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-28 21:16, btolfree wrote:
    Thanks!! It works great. Now maybe I should figure out a way to check to make sure there is data in those cells. This program is to calculate pilot flying time to ensure I am under the maximum number of flying hours for a 7 day, 30 day, 90 day and 365 day period. I can enter my flight hours and have it keep track. Then when I need to check my previous time period, I can plug in the end date and the number of days in the past to check and verify my hours.

    Thanks again
    If you know what to do in case the data is insufficient wrt the days criterion, it is not that hard to incorporate it in the OFFSET formula.

    Aladin

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    Lakewood, Colorado
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Now that this appears to work so well, I am thinking of a way to automatically calculate the time periods and have it update itself on a daily basis. So the date field would automatically increment itself and the new calculation would then adjust the flight times automatically. Is this possible?

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Help me here Aladin...

    OFFSET($B$2,9-E1-1,0,E1,1)

    9, in the above is the value returned by the nested match function...

    The answer is correct for the overall formula, and therefor, in my case, it must be returning this Range B3:B9

    How is the Offset function returning B3:B9 ?
    E1 = 7
    9-7-1 = 1

    Thanks for the help...
    Tom

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-28 21:25, btolfree wrote:
    Now that this appears to work so well, I am thinking of a way to automatically calculate the time periods and have it update itself on a daily basis. So the date field would automatically increment itself and the new calculation would then adjust the flight times automatically. Is this possible?
    Do you mean by time periods what goes in E1?

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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