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.