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

[Tom Schreiner]

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

[Tom Schreiner]

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

[Aladin Akyurek]

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

[btolfree]

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

[Aladin Akyurek]

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

[btolfree]

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?

[Tom Schreiner]

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

[Aladin Akyurek]

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