Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 29 of 29

Thread: Dynamic Summation

  1. #21
    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 22:22, TsTom wrote:
    I did not figure it out Aladin...

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

    Thanks,
    Tom
    Have a look at the OFFSET function.

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

    means

    get the array of values that start at

    9-E1-1 down frm $B$2,

    0 columns to the right (effectively the same column where $B$2 is)

    the height is E1 number of cells

    1 says: data area is 1 column width.

    Aladin

  2. #22
    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

    Now understood...
    Thanks Aladin
    Tom

  3. #23
    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 22:19, btolfree wrote:
    7 is in E1, 30 in E2, 90 in E3, 365 in E4. The date {entered in a date format and not -now()} is in B4 (I moved it for readability).
    The date in B4? In B we have the hours! And which date did you try, still 4/8?


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

    Default

    I think I am getting it from your explanation to Tom. I kept trying to insert rows at the top (for a header and some documentation information) and every time I did this, the calculation would fail. When I put everything back beginning in the first row, it would work. I was trying to figure out how to move it all down (to add a header and titles) and still get it to work, but could not figure it out. I think your explanation to Tom may have the answer for me. I now have my dates in 'A', my hours in 'B' and I have my reference date in D1. I then put the 7 in G1, the 30 in G2, the 90 in G3 and the 365 in G4. My calculated values go in H1, H2, H3 and H4 respectively. I was then trying to work out a way to freeze frames and still see my calculations as this will run for a year's worth of data.

    Thanks again

  5. #25
    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 22:41, btolfree wrote:
    I think I am getting it from your explanation to Tom. I kept trying to insert rows at the top (for a header and some documentation information) and every time I did this, the calculation would fail. When I put everything back beginning in the first row, it would work. I was trying to figure out how to move it all down (to add a header and titles) and still get it to work, but could not figure it out. I think your explanation to Tom may have the answer for me. I now have my dates in 'A', my hours in 'B' and I have my reference date in D1. I then put the 7 in G1, the 30 in G2, the 90 in G3 and the 365 in G4. My calculated values go in H1, H2, H3 and H4 respectively. I was then trying to work out a way to freeze frames and still see my calculations as this will run for a year's worth of data.

    Thanks again
    If you understand that -1 bit, you'll be indeed OK. Even that can be computed automatically. If you fix your data area and adjust that bit, such computation will not be necessary.

    The formula (now adapted to your latest layout), which is now in H1

    =SUM(OFFSET($B$2,MATCH(D1,A:A,0)-G1-1,0,G1,1))

    can be controlled not to compute a result when there isn't enough data as follows:

    In E1 enter:

    =MATCH(D1,A:A,0)

    In H1 enter:

    =IF(E1>=G1,SUM(OFFSET($B$2,E1-G1-1,0,G1,1)),"")

    Aladin

  6. #26
    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 22:04, Yogi Anand wrote:
    Hi Aladin:
    No offence intended -- I did state that you had indicated that limitation, however, I proposed a solution that would not have that limitation. My post stands on its own and is not intended to be a criticism of your proposed solution.

    Regards!

    Yogi Anand
    You did not state that I had indicated that limitation as you can see from your own post that I reproduce here:


    Your formula will break down if the data for the range of date and the number of days, say 7 in this case does not exist. I would suggest using the following DSUM function:
    If the date say, 4/8 is entered in cell D2, and the number of days 7 is entered in cell E1 ...

    =DSUM($A$1:$B$10,"Hours",$F$2:$F$3)
    where F2 is a blank cell and F3 has the following formula:
    =AND(A2<=$D$2,A2>$D$2-$E$1)

    This formula does not breakdown if there is no data for the range going back 7 days, 30 days, or 365 days as OP would need to have.


    However, your solution is expensive, which I'm more concerned about:

    $A$1:$B$10, which is the 1st arg of DSUM, is not dynamic. When you create a dynamic name range for that, you'll have to have an OFFSET formula in addition to the DSUM formula.

    What I proposed is a single dynamic formula that suffices for the task. So, a Dfunction is not always the fastest route to Rome as some headquarters would have you to believe.

    Regards,

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-28 23:29 ]

  7. #27
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi btolfree and Aladin:
    Here is another formulation that seems to be not adversely impacted by non-availabilty of data for certain days:

    =SUMPRODUCT((B2:B10)*(A2:A10<=$D$2)*(A2:A10>$D$2-$E$1))

    Regards!

    Yogi Anand

  8. #28
    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 23:21, Yogi Anand wrote:
    Hi btolfree and Aladin:
    Here is another formulation that seems to be not adversely impacted by non-availabilty of data for certain days:

    =SUMPRODUCT((B2:B10)*(A2:A10<=$D$2)*(A2:A10>$D$2-$E$1))

    Regards!

    Yogi Anand
    Yogi,

    That's still an expensive, non-dynamic formula.

    Aladin

  9. #29
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin!

    Regards

    Yogi Anand

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
  •