1. ## Minimum Values (2000)

Column A, Rows 1-100 contain quantities of a product I have purchased. Column B, Rows 1-100 contain the price per item of the product in Column A. Column C, Rows 1-100 contain the extended total cost of each product (i. e., the quantity times the price). What I need is a formula that will tell me and calculate the least expensive mix of a given quantity. For example, if the total quantities in Column A equal 1,000,000, I would like a formula that would calculate and tell me the cheapest 750,000 of the 1,000,000 quantities.
Thanks,
Jeff

2. ## Re: Minimum Values (2000)

I think that this would require a macro. A few questions:

1- Is it guranteed that there will always be at least one mix that will add up to the desired quantity? If not, what answer do you want?

2- I assume that the product must be bought in the quantity in column A. In other words, you can't buy a smaller amount to get the last amount needed to make the desired quantity?

3- Can the quantity in column A be bought multiple times? For example, if A1 contained 375,000, could it be bought twice to get 750,000?

Could you upload an example sheet that we could work with?

3. ## Re: Minimum Values (2000)

Thanks for replying, Legare. Let me clarify my problem, and maybe at the same time answer some of your questions. First of all, all of the "product" in Column A are homogeneous; that is, if there are 1,000,000 in number of product, any of the 1,000,000 can be used/substituted in getting to the 750,000 desired result. Second, none of the quantities in the cells in A1:A100 come anywhere near either 750,000, much less 1,000,000; nonetheless, the minimum quantity in each such cell is 1,000, and as a further requirement, since each cell represents a different "lot" or "package", I would prefer if the solution did not divide up any such amounts. Third, since each cell in A!:A100 represents a different "lot" or "package", each may only be used once. Finally, keep in mind I have already purchased these 100 lots, and am seeking only to come up with the 750,000 "cheapest" or least expensive ones from the total of the packages at A1:A100.
I hope I haven't confused you.

4. ## Re: Minimum Values (2000)

Does it HAVE to be EXACTLY 750,000. What if there are NOT any combinations that give you 750,000 items? Is there an accepatable range in the # of items and you take a minimum WITHIN that range? What if NONE are in the that range?

Steve

5. ## Re: Minimum Values (2000)

Steve,
Each of the cell entries in A1:A100 contain at least 1,000 units of product, and in some cases more, but always in multiples of 1,000. As to whether it has to be "exactly" 750,000, the answer is no, but should be +/- 1-2% at most.
I think I see where you and Legare are going-regression. I didn't think of that angle; I was thinking more along the lines of a simple VLookup, combined with a summing argument that adds all of the least expensive items!
So much for my idea!
Thanks,
Jeff

6. ## Re: Minimum Values (2000)

So if there is a SMALLER total value at -2% (735,000 items) than one at EXACTLY 750,000, or a SMALLER value at +2% (765,000 items) than at EXACTLY 750,000, you want the EXACT 750,000 value or the other the value?

And if you do NOT want the SMALLEST in the range, what are you really asking for? Is there some type of weighting for number and the cost values?

Also do you have an example dataset to play with?

Steve

7. ## Re: Minimum Values (2000)

I see. Why don't we just assume I need exactly 750,000. Wouldn't a "solution" then pik the least expensicve items until the total quantity reached 750,000, regardless of the price?

8. ## Re: Minimum Values (2000)

Try this:
In D1 enter 0
In E1 enter:
<pre>=D1*A1</pre>

In F1 enter:
<pre>=E1*B1</pre>

Copy D1:F1 to D2:F100
Enter in H1: 750000
Enter in H2:
<pre>=SUM(E1:E13)</pre>

Enter in I2:
<pre>=SUM(F1:F13)</pre>

Now setup the solver:
Tools - solver
Target cell: I2
Equal to: MIN
By changing cells:
\$D\$1:\$D\$100
Subject to constraints:
\$D\$1:\$D\$100 >=0
\$D\$1:\$D\$100 <=1
\$D\$1:\$D\$100 =integer
\$H\$2<=\$H\$1*1.01
\$H\$2<=\$H\$1*0.98

<options>
Tolerance to 0%
<Solve>

This should find a "possible solution" though it might not be the best solution. changing column D to various patterns of 1,0 might vary the results.

Steve

9. ## Re: Minimum Values (2000)

Another method (since solver can give "squirrely results" sometimes) is to use the "Brute force" method. There are "only" 10,000 possible permutations of results, so you won't even come close to filling the sheet!

Not sure if excel can handle all the calcs, i did NOT set it up. It works "in theory" (i have doe this with smaller datasets):

In a new worksheet:
Starting in C3 and going to CX3 fill the cols in row three with the numbers 1-100.
in C1:
=INDEX(Sheet1!\$B\$1:\$B\$100,C3)
in C2:
=INDEX(Sheet1!\$A\$1:\$A\$100,C3)
Then fill in the patterns of binary number:
Col C is: alt 0,1: 01010101...
Col D is: 2 x0,2 x1 00110011...
col E is: 4x0, 4x1 :11110000
Col Fis: 8x0, 8x1
Col Gis: 16x0, 16x1
Then 32, then 64, etc, etc

In A4:
=SUMproduct(\$C\$2:\$CX\$2,C4:Cx4)
In B4:
=SUMProduct(\$C\$2:\$CX\$2,C4:CX4,\$C\$1:\$CX\$1)
in A1: 750000
In B1:
=MIN(IF(A4:A10003=\$A\$1,B4:B10003))

You will get a 0 in B1 if it finds no solution, so you can then vary the value in A1 till you find a "hit".

This is probably faster than a VB solution, though it might be sluggish with so many calcs.

Steve

10. ## Re: Minimum Values (2000)

Thanks, Steve, I'll give both methods a try tonight!

11. ## Re: Minimum Values (2000)

Since you never posted an example workbook, I could not customize this to your setup. The VBA code below expects the information you described to be on an sheet name Sheet1. It also expects the target quantity to be in cell D1 on that sheet. The code will turn the rows used to get the least expensive target quantity red. The code will get as close to the target as possible without exceeding the target.

<pre>Option Explicit
Public Sub FindBest()
Dim I As Long, J As Long, lTarget As Long, lLastRow As Long
Dim lQty() As Long, dCost() As Double, lRow() As Long
Dim dWk As Double, lWk As Long
lLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
ReDim lQty(0 To lLastRow)
ReDim dCost(0 To lLastRow)
ReDim lRow(0 To lLastRow)
lTarget = Worksheets("Sheet1").Range("D1")
With Worksheets("Sheet1").Range("A1")
For I = 0 To lLastRow
lQty(I) = .Offset(I, 0).Value
dCost(I) = .Offset(I, 1).Value
lRow(I) = I
Next I
For I = 0 To lLastRow - 1
For J = I + 1 To lLastRow
If (dCost(J) = dCost(I)) And (lQty(J) > lQty(I)) Then
dWk = dCost(J)
dCost(J) = dCost(I)
dCost(I) = dWk
lWk = lQty(J)
lQty(J) = lQty(I)
lQty(I) = lWk
lWk = lRow(J)
lRow(J) = lRow(I)
lRow(I) = lWk
Else
If dCost(J) < dCost(I) Then
dWk = dCost(J)
dCost(J) = dCost(I)
dCost(I) = dWk
lWk = lQty(J)
lQty(J) = lQty(I)
lQty(I) = lWk
lWk = lRow(J)
lRow(J) = lRow(I)
lRow(I) = lWk
End If
End If
Next J
Next I
dWk = 0
For I = 0 To lLastRow
If dWk + lQty(I) <= lTarget Then
Range(.Offset(lRow(I), 0), .Offset(lRow(I), 2)).Interior.ColorIndex = 3
dWk = dWk + lQty(I)
If dWk = lTarget Then Exit For
End If
Next I
End With
End Sub
</pre>

12. ## Re: Minimum Values (2000)

Legare,
Here is the file. I can't seem to get it to work.
Jeff

13. ## Re: Minimum Values (2000)

Legare,
I am confused, either I do not understand the question (or maybe I don't understand your code) but I don't see how this will give what Jeff is requesting. It might some of the time but I do not think always.

Your code loads the arrays to match the data in cols A and B and also keeps track of the rows.
It then sorts the arrays essentially by Col B (per item cost) and if a tie by total quantity.
It then adds the first item (the lowest per item cost) and goes down the list adding items as long as the total count of items is <the target.

This will find a solution, (possibly with < target quantities) but if there IS a solution equal to target quantity it MIGHT NOT find it. It will NEVER find this solution if the FIRST value selected is NOT part of the solution. In your scheme the lowest per item object is ALWAYS part of the solution. Yours will find a LOWER total cost perhaps but it might not meet the constraint of the target number of objects. It is much easier to find a lower value if you use less total objects.

If I am missing something, let me know, I have been wracking my brain with it and I find holes in the solution.
Steve

14. ## Re: Minimum Values (2000)

The attached workbook contains the VBA code adjusted to fit your workbook's format. You need to put the target quantity in cell Z1 then run the macro.

15. ## Re: Minimum Values (2000)

My code works on the theory that the combination of the lowest cost items will be the lowest combined cost. It will break down if all of the lowest quantity rows also have the lowest cost, and therefore the code might not get to the quantity closest to the target quantity. In that case, you would still get the lowest cost, but there might be a solution that would be closer to the target quantity, but at a higher cost.

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
•