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

 

[btolfree]

I mean to have E1 always be the current date and have it increment automatically with each new day.

 

[btolfree]

Today's Date -> 16-Apr Flying Time In Previous X Days -> 7 9.00 <- Total Hours
Flying Time In Previous X Days -> 30 #REF!
Flying Time In Previous X Days -> 90 #REF!
Flying Time In Previous X Days -> 365 #REF!

Date Flight Hours
1-Apr 5.0
2-Apr 4.0
3-Apr 6.0
4-Apr 8.0
5-Apr 3.0
6-Apr 6.5
7-Apr 3.0
8-Apr 9.0
9-Apr 10.0
10-Apr 3.5
11-Apr 7.5
12-Apr 8.0
13-Apr 6.0
14-Apr 8.0
15-Apr 5.0
16-Apr 4.0
17-Apr
18-Apr
19-Apr
20-Apr
21-Apr
22-Apr
23-Apr
24-Apr
25-Apr
26-Apr
27-Apr
28-Apr
29-Apr
30-Apr
I know this doesn't show up to well, but like the above. I moved the cells and, unfortunately, the total for the past 7 days does not work correctly now. It is calculating the past 2 days.

 

[Yogi Anand]

Hi btolfree and Aladin:

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.

Hi Aladin:
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.

Regards!

Yogi Anand

 

[Aladin Akyurek]

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'm sorry but this is a superfluous observation, as I noted myself under which condition the formula I suggested apply.

regards,

Aladin

 

[btolfree]

If I use =NOW() to pull the current date to use in the calculation, it loses it's reference and does not calculate the hours.

 

[Yogi Anand]

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

 

[Aladin Akyurek]

On 2002-04-28 21:42, btolfree wrote:
Today's Date -> 16-Apr Flying Time In Previous X Days -> 7 9.00 <- Total Hours
Flying Time In Previous X Days -> 30 #REF!
Flying Time In Previous X Days -> 90 #REF!
Flying Time In Previous X Days -> 365 #REF!

Where did you put Today's Date -> 16-Apr? In D2?

We still want 7 in E1, 30 in F1, 90 in G1, and 365 H1?

Aladin

 

[btolfree]

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

 

[Aladin Akyurek]

On 2002-04-28 21:27, TsTom wrote:
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

That's right Tom. I knew you'd figure out.



Aladin

 

[Legacy 98055]

I did not figure it out Aladin...

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

Thanks,
Tom