+ Reply to Thread
Results 1 to 6 of 6

Capacity constraint algorithm Excel

  1. #1
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    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!!!
    Attached Files Attached Files
    Last edited by eeeo; 08-14-2020 at 10:21 AM.

  2. #2
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    Re: Capacity constraint algorithm Excel

    Help please..

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    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))
    In the future please allow at least 24 hours before bumping your thread.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    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)

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    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.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Capacity constraint algorithm Excel

    Cell J2, flag 0/1, independent with column K

    Please Login or Register  to view this content.
    Cell K2, dependent with column J:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Solver ignoring sum constraint
    By cmeiss16 in forum Excel General
    Replies: 3
    Last Post: 04-17-2020, 03:52 PM
  2. Need Help in setting constraint for excel solver
    By piku9290dgp in forum Excel General
    Replies: 2
    Last Post: 12-04-2018, 02:20 PM
  3. [SOLVED] Excel Solver Less Than or Equal To Constraint
    By shoulddt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-04-2018, 02:53 PM
  4. capacity of excel 2007
    By rochenge in forum Excel General
    Replies: 1
    Last Post: 11-02-2016, 01:03 PM
  5. Excel Solver Constraint Help
    By BauceArj in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-14-2014, 08:47 PM
  6. [SOLVED] Excel Solver Constraint Question
    By Verna in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-19-2012, 04:33 PM
  7. Capacity of Excel File
    By malnahar in forum Excel General
    Replies: 6
    Last Post: 06-09-2009, 05:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1