Creating Queue Formulas

Pestomania

Active Member
Joined
May 30, 2018
Messages
330
Office Version
  1. 365
Platform
  1. Windows
Hello all!

I am working on a year long-table to understand input and outputs of different machines and the effect on down-line machinery.

I can create a manual table based on cycle times, when machine 1 will kick product to machine 2 and machine 2 will send to machine 3. But I am trying to set it so that if I change the cycle time or how much is being outputted, what does that look like for the entire year. I am stumped on how to do this. I have attached XL2BB for this.


Book2
ABCDEFGHIJKLMNOPQRST
1InputsMachine 1Machine 2Machine 3Mach 3 Overage
2Cycle Time773
3Output/week787
4Starting Inventory105
5
6
7
8
9
10
11
12
13
14
15The highlighted field should move up/down based on the "cycle time" of the previous step (this goes for any "added inventory")
16Added InvQueueAdded InvQueueThis highlighted field takes into account the current queue, plus the "added inventory" minus the amount that can be moved per week
17
187105
19105Goal:
20105Make dynamic cycle times so that the "added inventory" can move up/down
21105Make dynamic output/week so that we can see dynamic changes dependent on the machine output
22105See overages or growth in a machine that maintains queue
23105Make this to where it can be adapted to more machines and cycles like below: As you can see after Machine 4, it starts using Machine 2 and 4 a few more times. I cannot figure out how to make all of this dynamic
247105
25795
2695
2795
2895
2995
3095
31795
32785
33886
3486
3586
3686
3786
38786
39776
40787
4177
4277
4377
4477
45777
46767
47688
4868
4968
5068
5168
52768
53758
54589
5559
5659
5759
Sheet1
Cell Formulas
RangeFormula
B18B18=B2
C25C25=B18
D18,F18D18=C4
D19:D24,D30:D31,D37:D38,D44:D45,D51:D52,F19:F32D19=D18
D25,D32,D39,D46,D53D25=D24+C25-C$3
D26:D29,D33:D36,D40:D43,D47:D50,D54:D57D26=IF(C26>0,(D25+C26)-C$4,D25)
F33:F57F33=IF(E33>0,(F32+E33)-E$3,F32)



Workflow:
1682610906267.png
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
the flow is not very clear.
I am assumin that Machine2 can only produce if machine 1 has produced something. So if machine1 produces 7 item per cycle, then machine2 can only use 7 item , even if its output per week could be 8.

Why is machine 3 (cycle every three days) only doing something every 8 days in your schedule?
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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