How to make totals over periods of time

[cg5647213]

Hi all,

New to excel and need help making a formula.
I have a large data set with a value in the columns, and a corresponding start and end date for each value.

I need to get the total exposure (value in apm multiplied by number of days) at 3 months intervals for each row. (i.e. each row is one subject and each apm {1,2,3} is a different exposure)
If two columns in one row overlap in that time period then the two totals are combined for those overlapping days for that 3 month interval. If for example there is only 4 months of exposure, the 6 month interval will include just that extra 1 month of exposure.

Hope I've explained that at all clearly and thanks so much in advance for any help, very appreciated

I'll attach a sample workbook below to explain what I mean.

[Glenn Kennedy]

Columns D & G contain data linked to files on your local PC. We see error messages. Repalce the links with the data.


I don't understand what you want. can you add some manually calculated answers and an explanation as to where they came from??!!

[queuesef]

1.) the numbers didnt show up correctly from download - due to them being linked to another file, and I do NOT have access to that file/ server, So it's hard to figure out!
2) your example should show how you get your results/ desired results
3) I would rearrange data to be more like a database(more table like): meaning- have a column for AMP/StartDate/EndDate/NumberOfDays -
4) use little known formula =DateDif(Start,End,"D") ~ where start= start date; end= end date; and the "D" represents the number of days. \\ this formula will help you get number of days between 2 periods
for number 3 above, data should look like this:
Trial# / APMNo /APM Value / StartDate / End date / NumberDays
1 /APM3 /20 /17-Dec-12 / 8-Jul-13 / formula = DateDif( ) as described above.

We may have another go at it if you HardCode or Range Value the numbers that are linked to another file.

[cg5647213]

Hi Glenn and queuesf,

Have changed that now, thanks.

Yes sorry about that, didn't explain it at all!. I have added some manually calculated answers to row 3.

So I am trying to get the total at each time point which are 3 months apart. so 3mth represents 0-3 months, 6mth represents 3-6 months and so on.

Using the DateDif function I've added the number of days for each trial, so using that for column 3.

- 3mth = 1250 (APM1) x 93 (First 3 months) = 116250
- 6mth = 1250 (APM1) x 40 (133 - 93 days which were the first 3 months) + 5 (APM2) x 49 + 20 (APM3) x 4 = 50325 (As I needed to reach 93 days it was 40 days from first trial + 49 days from second trial + 4 days from third trial to make up that period between 3 and 6 months)
- 9mth = 20 (APM3) x 93 = 1860
- 12mth = 20 (APM3) x 93 = 1860
- 15mth = 20 (APM3) x 13 (13 as APM 3 has 203 days - 4 (3-6mth), - 93 (6-9mth), -93 (6-9mth) = 13). As APM3 ends at 8-Jul-13 and APM4 starts on 5-Jul-13 there are 3 days of overlap. Therefore 15 (APM4) x 3 (overlap days), and then + 15 (APM4) x 80 (93 - 13) = 1505
- 18mth = 15 (APM4) x 93 = 1395
- 21mth = 15 (APM4) x 93 = 1395
- 24mth = 15 (APM4) x 93 = 1395
- 27mth = 15 (APM4) x 34 (396 {APM4 no. of days} - 3 {overlap days in 15mth} - 80 {rest of days in 15mth} - 93 {18mth} - 93 {21mth} - 93 {24mth}) = 510

So that's how I did it out manually, just don't know how to do out a formula which calculates that as such

Really don't know if I've explained myself at all and if I make sense! But thanks very much for replying!

[JeteMc]

The following works for the values in row 6:
1. Insert two rows (1:2) which are populated as follows
Row 1: Cell R1 is zero and cells S1:Z1 using:=R2
Row 2: Cells R2:Z2 using:=SUM(R1,93)
2. Populate the APM1 No. of Days cells using: =DATEDIF($C6,D6,"D")
3. Populate R6:Z6 using:

Please Login or Register  to view this content.

Compare the results to the original values that are copied into row 12.
Let us know if you have any questions.

[cg5647213]

Wow that's great, thanks so much

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.