Dynamic Summation

btolfree

New Member
Joined
Apr 27, 2002
Messages
8
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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
 
Upvote 0
That works nice Aladin...
Now all I have to do is figure out how that formula does what it does...
Tom
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top