Dynamic Summation

[btolfree]

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

 

[Aladin Akyurek]

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

 

[Legacy 98055]

Now understood...
Thanks Aladin
Tom

 

[Aladin Akyurek]

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?

 

[btolfree]

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

 

[Aladin Akyurek]

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

 

[Aladin Akyurek]

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

 

[Yogi Anand]

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

 

[Aladin Akyurek]

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

 

[Yogi Anand]

Thanks Aladin!

Regards

Yogi Anand