1. ## Multi-condition formula (2000)

Problem: I would like to choose a minimum value among 12 in a list, but only up to a number that corresponds to the price in question. For example assume I have 12 prices for a commodity that correspond to the twelve months January-December. Assume further I have a budget of a certain amount of money each month with which to buy the commodity. This amount varies each month, as does the monthly prices. Finally, I have a beginning "bucket" of money with which I can purchase the commodity. This "bucket" may or may not equal the sum of the monthly budgets. I would like to choose, in order, the cheapest monthly price and buy as much as that month's budget allotment will allow, and then progress on to the next cheapest month until my original "bucket" has been spent.
Any help?
Thanks,
Jeff

2. ## Re: Multi-condition formula (2000)

If your goal is to maximize the quantity of the commodity you buy in a year, I would suggest using the Solver add-in.

(You can install it if necessary using Tools/Add-ins...)

I have attached a demo workbook. If you open it, and select Tools/Solver..., you will see:
The cell whose value must be maximized,
The cells that can be modified,
The restrictions that have been imposed.

If you click Solve, Excel will determine the optimum solution.

3. ## Re: Multi-condition formula (2000)

Thanks, Hans, I'll try it!

4. ## Re: Multi-condition formula (2000)

Hans,
Can you help me with the attached?
Thanks,
Jeff

5. ## Re: Multi-condition formula (2000)

I don't have time today; perhaps tomorrow. Sorry. If anyone else cares to take a look ...

6. ## Re: Multi-condition formula (2000)

Hello Jeff,

One of the constraints you put in is
\$D\$28 >= \$F\$27

I think that should be
\$D\$27 >= \$F\$27

Then there is something which I'm not sure about, so I may be completely off.

In most of your calculations, you are effectively multiplying the monthly purchases by a factor based on retainage before adding it to the SCQ balance.

In cell F30, however, you are adding the monthly purchases straight to the beginning balance. This amount is then compared to the minimum inventory level for the end of October. I don't pretend to understand what you are doing in this worksheet, but my intuition says that for consistency the monthly purchases should be multiplied by the retainage factor before adding them to the beginning balance.

In that case, the Solver is able to find a workable solution.

See the attachment. Sheet1 is your worksheet with the modifications proposed above. I have added a new Sheet2 with essentially the same calculations, but laid out slightly differently - it helped me to understand what was going on.

Regards,
Hans

7. ## Re: Multi-condition formula (2000)

Hans,
Thanks a bunch for "solving" this thing for me. Actually, the 11,600,000 should be the "Maximum" inventory level on hand at the end of October, and the sum of the (1) beginning inventory level on hand at 5/31/2002, and, (2) the purchases "made" throughout the months of May-October, must be less than 11,600,000.
By the way, how do you reset the problem back to zero?
Thanks,
Jeff

8. ## Re: Multi-condition formula (2000)

Jeff,

If you mean "How can I reset the Solver Parameters", there is an "Initial values" (or similar, don't know the exact wording in English) button in the Solver dialog.

If you mean "How can I reset the starting values", select the monthly purchases cells (I5:I10) and erase the contents (or enter 0).

Regards,
Hans

9. ## Re: Multi-condition formula (2000)

Thanks, I just noticed that. Did you get the problem to work with the change I mentioned in my last e-mail? When I tried to do it, it gave me an infeasible solution!
Thanks,
Jeff

10. ## Re: Multi-condition formula (2000)

Jeff,

In fact, the change you mentioned makes the problem trivial <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

By turning around the condition on the Inventory level at the end of October, ALL restraints are now "keep something below a certain level". Your present goal is to minimize total cost. The solution is simple: buy nothing! All conditions are met, you spend \$ 0,00. Cheapskate!

Why doesn't the Solver come up with this trivial solution? Because there is no lower limit to the monthly purchases. So Excel tries negative values and concludes that the whole business gets out of hand. If you add the restraint that the monthly purchases should be >= 0, the Solver will find the trivial solution.

You will have to rethink the problem. What is your real goal?

You can strive to realize an Inventory level of 11,600,000 by the end of October and minimize the costs.
Or you can strive to maximize the number of purchases while not exceeding the Inventory level of 11,600,000.
But trying to keep the Inventory level below 11,600,000 AND minimize costs is self-defeating - it leads to 0 purchases.

11. ## Re: Multi-condition formula (2000)

Hans,
I apologize. Upon reflection, the minimum inventory level at the end of October should be no less than 11,600,000, and no greater then 12,025,138 (Cell H2, the maximum space available for the inventory).
How stupid of me!
Take another stab?
Thanks, and again I apologize for my hasty observation!
Jeff

12. ## Re: Multi-condition formula (2000)

Jeff,

In fact, the condition that inventory level shouldn't exceed 12,025,138 is not necessary - trying to minimize costs ensures that you will end up with an inventory level as near 11,600,000 as possible.

In fact, the workbook I attached earlier today does just that, I think. The solution is as you would expect since prices increase steadily:
Buy as much as allowed by the maximum monthly injection in May, June and July.
Then in August, buy less to avoid exceeding the maximum inventory level for that month.
In September, do as in May-July again.
Finally, in October buy the bare minimum to reach the minimum inventory level for that month.

Regards,
Hans

13. ## Re: Multi-condition formula (2000)

Hans,
Still having a problem with the attached. May I impose a little more on you?
Jeff

14. ## Re: Multi-condition formula (2000)

In D24 and D27 (the realized inventory levels in June and August), you include (add) the beginning level.

In F24 and F27 (the maximum inventory levels for those months), you exclude (subtract) the beginning level.

This makes the comparisons D24 <= F24 and D27 <= F27 invalid, I think.

If you remove
-BeginningSCQBalance
from the formulas F24 and F27, the Solver will find a solution.

15. ## Re: Multi-condition formula (2000)

Hans,
One more time please? The two worksheets shoul yield the same result.
Thanks,
Jeff

Page 1 of 2 12 Last

#### Posting Permissions

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