Using solver to find a combination of numbers that equal a specific number.

[Livin404]

Hello, I'm do understand how to use Solver to get a specific number. I am wondering how can I make it so if there is no combination I can make it so I can get as close as possible without going over?

I add a simple file to my drop box.

I hope it is even possible.

closest sum 04 June 23.xlsx

Thank you,

 

[Legacy 143009]

without going over

Do you mean not exceeding the target?

 

[Livin404]

Do you mean not exceeding the target?

That is correct

 

[Legacy 143009]

It is very hard.. There can be virtually infine possibilities. Even it is hard to achieve with solver. I am surprised how you achieved it.

 

[Livin404]

It is very hard.. There can be virtually infine possibilities. Even it is hard to achieve with solver. I am surprised how you achieved it.

Well I meant the closest I can get to it without exceeding. Thank you,

 

[Legacy 143009]

This code can look for the exact target value. It is a heuristic code so, it tries everything until it achieves the target. Thus, it is very slow. I may take forever to try all combinations.
Finding the closest is nearly impossible as I told you before. the difference could be 0,01 or 1,00. It is virtually impossible to determine through all possibilities.

Option Explicit
Sub test()
  Dim count() As Variant, results As Variant, permutLen As Long, numLen As Long
  Dim permutation As Variant, r As Variant, c As Long, numbers() As Variant
  Dim result() As Variant, target As Double, minimumValue As Double, i As Integer
  target = Range("G1").Value2
  permutLen = Cells(Rows.count, 1).End(xlUp).Row
  minimumValue = Application.WorksheetFunction.Min(Range("A1:A" & permutLen))
  numbers = Range("A1:A" & permutLen)
  For i = 0 To Int(target / minimumValue)
    ReDim Preserve count(i)
    count(i) = i
  Next
  numLen = UBound(count) + 1
  permutation = numLen ^ permutLen
  ReDim result(1 To permutLen, 1 To 1)
  For r = 1 To permutation
    For c = 1 To permutLen
      result(c, 1) = count(Int((r - 1) / (permutation / (numLen ^ c))) Mod numLen)
    Next
    If mySumProduct(numbers, result) = target Then
      Range("B1").Resize(permutLen) = result
      Exit Sub
    End If
  Next
End Sub
Function mySumProduct(ByRef values() As Variant, ByRef quantity() As Variant) As Double
  Dim i As Integer
  For i = 1 To UBound(quantity)
    mySumProduct = mySumProduct + (values(i, 1) * quantity(i, 1))
  Next
End Function

 

[Alfb]

A possible solver setup may look like this. Then you also need to change the formula in cell G3 i.e,

to get the result equal to or less than G1.

 

[Alfb]

Sorry messed up the formula part should be "G3=G1-G2