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

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
774
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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,
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
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,
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
A possible solver setup may look like this. Then you also need to change the formula in cell G3 i.e,
Code:
to get the result equal to or less than G1.
 

Attachments

  • mrexcel.jpg
    mrexcel.jpg
    70.8 KB · Views: 30
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top