# Solver

##### Active Member
Does anyone know how to make Solver take into account a ratio when determining profit maximization. I have four items and the ratio of Item 1 to 2 should be 7 to 5.

Any ideas?

This message was edited by Adrae on 2002-10-24 10:18

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Mark W.

##### MrExcel MVP
You just integrate that ratio into your model. Care to share your model for discussion purposes?

##### Active Member
Sure - here is an example:

1. Four juices are produced - OJ, GJ, PJ, All-in-One
2. The All-in-One contains equal parts of OJ, GJ, PJ
3.Contraints: 100 quarts of OJ; 75 quarts of GJ; 50 quarts of PJ
4. Cost per quart: \$2 for OJ; \$1.60 for GJ; \$1.40 for PJ
5. Prices: \$1 for OJ; \$.90 for GJ; \$.80 for PJ; \$1.10 for All-in-One
6. GJ should be no more than 30% of total output
7. the ratio of the number of OJ quarts produced and number of PJ quarts produced should be 7 to 5.

It's this last that is giving me headaches. I'm not at all sure how to incorporate it.

Any ideas?

Thanks

#### Mark W.

##### MrExcel MVP
What's the Unit of Measure on which the Sale Price is based?

##### Active Member

Oh right, the price and costs are on a per gallon.

Thanks

#### Mark W.

##### MrExcel MVP
Is the Cost per Quart or Gallon? See #4 above. What about the UOM of the constaints?
This message was edited by Mark W. on 2002-10-24 14:44

Cost per gallon.

#### Mark W.

##### MrExcel MVP
Oh right, the price and costs are on a per gallon.

Thanks

Hey, with the Price and Cost listed above you'd be better off not producing anything!!! You're losing \$\$\$ on everything you produce.
This message was edited by Mark W. on 2002-10-24 14:47

##### Active Member
Sorry - I've gotten confused. I should have put everything in quarts.

As it is:

PRICE is per QUART
COST is per GALLON
UOM of constraints is QUARTS

In Solver, I had converted everything to quarts, so instead of what I listed above for cost, let's use the following:

\$.50 for OJ
\$.40 for GJ
\$.35 for PJ

Do you know how I can incorporate the ratio of 7 to 5?

#### Mark W.

##### MrExcel MVP
I did this in a hurry... my stomach is growling. Your production appears to be constrained by GJ. Also, I wasn't sure how to interpet the constraint listed as #6 above since GJ is also a component of All-in-One. Use with caution!!! ...But this should get you started.
Juice.xls
ABCDEFGHIJKL
1JuiceLimitQuantityPriceCostProfitModel:
2OJ500\$1.00\$0.50\$0.00\$115.00
3GJ7525\$0.90\$0.40\$12.503
4PJ500\$0.80\$0.35\$0.00TRUE
5All-in-One150\$1.10\$0.42\$102.50TRUE
6Total175\$115.00TRUE
730%ofTotal52.5TRUE
8TRUE
9100
10
Sheet2

This message was edited by Mark W. on 2002-10-24 15:14

Replies
4
Views
822
Replies
0
Views
337
Replies
0
Views
850
Replies
0
Views
432
Replies
5
Views
418

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,545
Messages
5,832,380
Members
430,128
Latest member
ojl987

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back