Need an Excel formula to determine how numbers should be distributed based on specific criteria

Posted on 2011-04-20
Last Modified: 2012-05-11
This is one of those tasks that seems next to impossible to me.  From my perspective, there are just too many variables working against finding an algorithm for what I’m trying to accomplish.  Nevertheless, if any of you experts have any viable solutions, I’d be grateful.  It would save me a lot of manual work.

Please refer to my attached spreadsheet.  You’ll see there are no formulas in the cells, but it will give you a good idea of what I’m trying to accomplish.  I’m looking for a the right formula that will generate the data in my “Finished Results” table, based on the following three things called out on my spreadsheet:

1.      What was original proposed.
2.      Minimum TOTAL quantity required.
3.      Maximum Allowable Qty by Location.

Question by:KP_SoCal
    LVL 24

    Accepted Solution

    How did you get to your third and final tables? There could be many solutions couldn't there?
    LVL 19

    Assisted Solution

    I think it would be wise to split the question in two steps :

    1) determine total amounts
    2) determine how to split this over locations

    for 1 :

    using the MAX fomula one can combine the proposed and minimum quantity values :

    Proposed qty incl min. item 1 = max(E3; H3)
    Proposed qty incl min. item 2 = max(E4; H4)
    Proposed qty incl min. item 3 = max(E5; H5)

    although this does not take into account the apperent total item number that seems to be 300.

    This leads to a number of questions :

    can you describe the business rules that you need to follow, such as :
    - the total number of items on all locations = 300
    - at least 100 items of type item2
    - location 2 cannot contain any item1 items

    try to formulate a 'rule' for how you want the items to be divided over item types and locations.

    If you do not have these business rules, there is little for us to contribute.


    Author Closing Comment

    I'm going to close this question.  I'm close to piecing this together on my own.  No fault of yours, I realize my posting was not very clear.

    However, I still wanted to award both of you points for at least responding. ;-)

    Take care.
    LVL 24

    Expert Comment

    That is very generous of you, thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    The greatest common divisor (gcd) of two positive integers is their largest common divisor. Let's consider two numbers 12 and 20. The divisors of 12 are 1, 2, 3, 4, 6, 12 The divisors of 20 are 1, 2, 4, 5, 10 20 The highest number among the c…
    Iteration: Iteration is repetition of a process. A student who goes to school repeats the process of going to school everyday until graduation. We go to grocery store at least once or twice a month to buy products. We repeat this process every mont…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now