Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Legare Coleman

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Minimum Values (2000)

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

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Minimum Values (2000)

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

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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