Finding 'optimum' values in a range

[Leen Smit]

Hi there!

Can any one here help me with the following problem :

I have several (harddrive)images, of different sizes. These images need to
backup to DVD-roms. So what i've done is that i've placed all the images
sizes in a sheet, and now i'd like excel to find out which image-sizes add up
to 4.7 GB - or as close as possible to this. (if i simply burn 2 images on
DVD i'll need to many DVD's)

I've tried several different ways of adding the images sizes and conditional
format the sheet, to find which size is closest to 4.7GB.
The problem with just adding the values is that i do find out which images
add up to 4.7GB, but i still have to figure out by hand which images its
actually adding up, thus still costing me too much time (i might as well just
calculate them by hand then..)

The sheet looks like this (failry stright forward)
A b
Server MB
Server1 250
Server2 1600
Server3 900
Server4 1800
Server5 1700
Server6 2400
Server7 1200
Server8 1500
Server9 2200
Server10 1300
Server11 1200
Server12 1600
Server13 600
Server14 1000
Server15 1200
Server16 1800
Server17 1500
Server18 1000
Server19 1100
Server20 500
Server21 1800
Server22 700
Server23 1000
Server24 1400

The output would ideally give me an summed size, and specify which images it
added.
Is what im trying to do possible???

Any help would be greatly Appreciated!!

Kind regards,

Leen Smit

 

[Bernie Deitrick]

Leen,

Send me an email (or post a valid address) and I will send you a workbook
that will find the values that sum up to a specified value. It will work
with the example numbers that you posted. From that example, here is one
soultion for the groups:

Server18 1000
Server23 1000
Server3 900
Server22 700
Server13 600
Server20 500

Server7 1200
Server11 1200
Server15 1200
Server19 1100

Server2 1600
Server12 1600
Server17 1500

Server21 1800
Server8 1500
Server24 1400

Server6 2400
Server10 1300
Server14 1000

Server9 2200
Server16 1800
Server1 250

Server4 1800
Server5 1700

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Leen,

You should have it soon.

HTH,
Bernie
MS Excel MVP

 

[icestationzbra]

hi bernie,

i was eagerly waiting for a solution to be posted for this problem i
order to gain some insight into the algorithm for solving such issues.

could you share the solution with the rest of us?

i would appreciate your benevolence.

thanks,

mac

 

[Bernie Deitrick]

mac,

I will post the code tomorrow - I'm home now and don't have that spreadsheet
on this machine.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

mac,

Here's a previous post that covers the algorithm. The code that I found was
originally distributed by Michel Claes, though I don't know who actually
developed it.

HTH,
Bernie
MS Excel MVP

'Start of paste....

Below is a copy of a previously posted message, which you can use to find
your solution.

HTH,
Bernie

'I was asked by a colleague to find the combination of certain numbers
'which will add up to a specific value. The numbers I was given were:
'
' 52.04;57.63;247.81;285.71;425.00;690.72;764.57;1485.00;1609.24;
' 3737.45;6485.47;6883.85;7309.33;12914.64;13714.11;14346.39;
' 15337.85;22837.83;31201.42;34663.07;321987.28
'
' (21 numbers in ascending order)
'
' I am trying to get a combination so that it adds up to 420422.19.
'
' On a sheet, put the following
' B1 Target 420422.19
' B2 number of parameters 21
' B3:B23 all parameters
' 321987.28
' 34663.07
' 31201.42
' 22837.83
' 15337.85
' 14346.39
' 13714.11
' 12914.64
' 7309.33
' 6883.85
' 6485.47
' 3737.45
' 1609.24
' 1485
' 764.57
' 690.72
' 425
' 285.71
' 247.81
' 57.63
' 52.04
' Start find_sol, it will put "1" or "0" in C3:Cx if you sum the
' parameters with a "1", you will have the best solution.
' It takes about 12 seconds on my very slow P133.
' The solution is
' 1 1 0 1 0 0 1 1 1 0 0 1 1 0 0 1 1 1 1 0 0
' Regards.
'
' Michel.
' Michel Claes <[email protected]>


Option Explicit

Global target As Double
Global nbr_elem As Integer
Global stat(30) As Integer
Global statb(30) As Integer
Global elems(30) As Double
Global best As Double

Sub store_sol()
Dim i As Integer
For i = 1 To nbr_elem
Cells(i + 2, 3) = statb(i)
Next i
End Sub

Sub copy_stat()
Dim i As Integer
For i = 1 To nbr_elem
statb(i) = stat(i)
Next i
End Sub

Sub eval(ByVal total As Double, ByVal pos As Integer)
If pos <= nbr_elem Then
stat(pos) = 0
eval total, pos + 1
stat(pos) = 1
eval total + elems(pos), pos + 1
Else
If (Abs(total - target) < Abs(target - best)) Then
best = total
copy_stat
End If
End If
End Sub

Sub find_sol()
Dim i As Integer
best = 0
target = Cells(1, 2)
nbr_elem = Cells(2, 2)
For i = 1 To nbr_elem
elems(i) = Cells(i + 2, 2)
Next i
eval 0, 1
store_sol
End Sub