[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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

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.

  • 2
2 Solutions
How did you get to your third and final tables? There could be many solutions couldn't there?
Arno KosterCommented:
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.

KP_SoCalAuthor Commented:
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.
That is very generous of you, thanks!

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now