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

N
  • Question Question
Nested offset
Replies
3
Views
217
StephenCrump
S
C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
225
cooleomter
C
R
  • Question Question
Looking for help w Excel formula
Replies
2
Views
249
Dave Patton
D
V
  • Solved
Running totals in 2D Dynamic array
Replies
4
Views
131
djclements
djclements
K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
117
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,226,840
Messages
6,193,278
Members
453,788
Latest member
drcharle

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