Capacity constraint algorithm Excel

1. Capacity constraint algorithm Excel

Hello Guys.
I have Excel with Columns:
• Period - I have 2 period
• Model - Model Number
• Line - Line that creates the model
• #Value - Number of units for each model to create
• AggValue- Aggregation of units (#Value) in the line per Period
• Line Option - Single / Multi
• Capacity - Each period has a different Capacity
• Flag - 1/0 - how algorithm line need to be (What do i expect to see in Flag Calc column)
• RemainCapacity - show the Remaining Capacity (What do i expect to see in RemainCapacity Calc column)

assumptions:
the order of the table is Permanent.
it's mean if a new row will enter its will follow the some rule
1. Line Option "single" will come first and the #value will be sorted from High to Low
2. Line Option "Multi" will come second the #value will be sorted from High to Low as well

you can open any columns but:
• It is not possible to make a calculation beased on other row
• if the calculation beased on other row its must to be Aggregation and it can be (sum/min/max/count)
like AggValue its sum Aggregation of #value per Period.

thanks!!!  Register To Reply

2. Re: Capacity constraint algorithm Excel  Register To Reply

3. Re: Capacity constraint algorithm Excel

I believe that turning the range into a table will overcome the restriction of "It is not possible to make a calculation beased on other row".
The formula for the flag could be: =IF(OR(F2="Single",E2<G2,D2<K1),1,0)
The formula for could be: =IF(AND(M1>0,G2-SUMIFS(D\$2:D2,A\$2:A2,A2,J\$2:J2,1)<0),M1,G2-SUMIFS(D\$2:D2,A\$2:A2,A2,J\$2:J2,1))
Let us know if you have any questions.  Register To Reply

4. Re: Capacity constraint algorithm Excel

thanks JeteMc.
in the flag formula there is a way to do this without k7
=IF(OR(F8="Single",E8<G8,D8<K7),1,0)  Register To Reply

5. Re: Capacity constraint algorithm Excel

It may help if you could tell or show us a scenario where the formula produces an issue.
Let us know if you have any questions.  Register To Reply

6. Re: Capacity constraint algorithm Excel

Cell J2, flag 0/1, independent with column K Cell K2, dependent with column J:   Register To Reply