Running total with lag between commencement and completion

K

keret

New Member
Joined
Mar 14, 2016
Messages
3
Using Excel 2007 on Windows XP.

Assume the following worksheet layout:

Code: 
[TABLE="width: 100%"]
<tbody>[TR]
[TD]1[/TD]
[TD]Commencements[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Period between commencement and completion[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Completions[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4 period assumption[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3 period assumption[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2 period assumption[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Total completions[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

What I am wanting to do is build a formula for the total completions row that only uses the takes into account the data in rows 1 and 2.

I am not sure how to even begin to tackle the problem. I guess the formula would need to test the preceding cells in row 3 to check if the number of periods corresponds to how many columns it is from the formula cell to confirm if it is relevant and, if so, add the related number of commencements in row 1.

The running total bit is easy once the initial part of resolved, but I just can't seem to get my head around how to implement the first stage.

Any thoughts/suggestions/solutions appreciated.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
keret said:
Using Excel 2007 on Windows XP.

Assume the following worksheet layout:

Code: 
[TABLE="width: 100%"]
<tbody>[TR]
[TD]1[/TD]
[TD]Commencements[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Period between commencement and completion[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Completions[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4 period assumption[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3 period assumption[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2 period assumption[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Total completions[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

What I am wanting to do is build a formula for the total completions row that only uses the takes into account the data in rows 1 and 2.

I am not sure how to even begin to tackle the problem. I guess the formula would need to test the preceding cells in row 3 to check if the number of periods corresponds to how many columns it is from the formula cell to confirm if it is relevant and, if so, add the related number of commencements in row 1.

The running total bit is easy once the initial part of resolved, but I just can't seem to get my head around how to implement the first stage.

Any thoughts/suggestions/solutions appreciated.
Click to expand...

Hi,

Maybe it's just me, but I think if you want any kind of help at all, you're going to need to explain in DETAIL what you mean and what you want to achieve.
I can't seem to make heads or tails out of what you said and the sample you showed.
 
Upvote 0
jtakw said:
... you're going to need to explain in DETAIL what you mean and what you want to achieve.
I can't seem to make heads or tails out of what you said and the sample you showed.
Click to expand...

My apologies for not being clear.

In the above worksheet layout, each column of data represents a different time period.

The "Commencements" row refers to the number of times a process is started in that period. So in the first 3 periods, the process is started 1, 1, and 3 times respectively.

The "Period between commencement and completion" row represents how many periods it is expected that the process(es) started in that period will take to complete. So in periods 1-3, each process in each period is expected to take another 4 periods to complete. This expected time to completion then decreases to 3 periods between the periods 4-6, before falling again to only 2 periods for a process to complete when started in the 7th period onward.

In the "Completions" section, I attempted to provide a guide as to when the various process will complete. So the "4 period assumption" row shows that the processes started in periods 1-3 (when the expected time to completion is 4 periods) will complete during periods 5-7. The "3 period assumption" row shows that the processes started in periods 4-6 (when the expected time to completion is 3 periods) will complete during periods 7-9. The "2 period assumption" row shows that the processes started from period 7 onwards (when the expected time to completion is 2 periods) will complete from period 9 onwards.

The "Total completions" row is simply the sum of those processes that complete in each period.

So to calculate the number of completion in, say, period 5, the formula would need to test row 2 (ie the "Periods between commencement and completion" row) to find those columns that include processes that will complete in the period (column) corresponding to each instance of the formula. In this instance, column 1 is the only column that has a number of periods in row 2 that suggests any processes will complete in period 5. Hence the number of completions in column 5 equals 1, the number of processes commenced in that period. To calculate the number of completions in column 7, the formula must sum the number of processes commenced in periods 3 and 4, because these processes will complete in period 7.

What I am ultimately trying to achieve is a running total in each period of the processes that have completed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Chris_010101
  • Solved
Mail merge with list numbers
Replies
4
Views
211
Chris_010101
Chris_010101
M
  • Question
Recursive LAMBDA for progressive SMALL calculations
Replies
1
Views
73
macfuller
M
I
  • Question
Tracking vacation requests
Replies
9
Views
440
KRice
K
A
  • Solved
3 way use button to run macro
Replies
2
Views
75
Aleytha
A
Y
  • Question
sum total in specific column when it complete 1 quantity in column H
Replies
0
Views
277
yoon
Y

Forum statistics

Threads
1,214,919
Messages
6,122,259
Members
449,075
Latest member
staticfluids

Share this page

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