Using Excel to calculate units based on capacity and priority by hour

[RJ209]

Hi,

I'm trying to automate Excel to plan units in columns C and E that can be produced based on capacity (B and D) and priority by hour (G to J). If possible it would also be based on the time values in columns L to N, so the time window can be turned on/off. How would this be done? The capacities in columns B and D currently reference both one another and the values in the plan (columns C and E) so I have iterative calculation turned on.

Thanks in advance for any help you can give me with this!

 

[braindiesel]

Here we go...
I changed your sheet for you a bit but this will solve your problem
Don't worry about the colour coding... that is only for the explanation

You will need a layout like this...
I moved the Yes or No to a more logical place to simplify the formula
The gray chart on the right is a named range used in the formulas below (named cakeOrdered)
Change the names from Type 1, Type2 etc to relevant labels for your world

In cell D3... the dark green one, you use the formula below and drag it to the right through the green cells
=IF($B3="Y",IF(VLOOKUP(D$2,cakeOrdered,3,FALSE)<$C3,VLOOKUP(D$2,cakeOrdered,3,FALSE),$C3),0)

In cell D4... the dark blue one, you use the formula below and drag it down through the blue cells
=IF($B4="Y",IF(SUM(D$3:D3)<VLOOKUP(D$2,cakeOrdered,3,FALSE),IF(VLOOKUP(D$2,cakeOrdered,3,FALSE)-SUM(D$3:D3)<=$C4,VLOOKUP(D$2,cakeOrdered,3,FALSE)-SUM($D$3:D3),$C4),0),0)

In cell E4... the dark orange one, you use the formula below and drag it to the right and down through the yellow cells
=IF($B4="Y",IF(SUM(E$3:E3)<VLOOKUP(E$2,cakeOrdered,3,FALSE),IF(VLOOKUP(E$2,cakeOrdered,3,FALSE)-SUM(E$3:E3)<=$C4-SUM($D4:D4),VLOOKUP(E$2,cakeOrdered,3,FALSE)-SUM(E$3:E3),$C4-SUM($D4:D4)),0),0)

Once set up, you set up the bread production similarly.

That should do it

 

[RJ209]

Thanks so much for this, I've just tried it and works perfectly! I really appreciate the help on this one

 

[braindiesel]

Glad to hear it. Thanks for letting me know