How can I make a calculator in Excel for the following items...?

[nLinked]

Lets say I have the following weight plates for my home gym:

4x 10 kg
6x 2 kg
8x 1kg
2x 20 kg

I want to make a calculator in Excel where I say I want a total of 12 kg
(for example), and it automatically assigns the available plates for each
side of my weight lifting bar (so 6 kg per side).

It has to consider that I only have those available plates. And if it can't
find a perfect match, it has to find the nearest weight.

Any ways this can be done in Excel?

Yes, I know it's easy so work out the weights on your own but I have a lot
more weights than that and it would be quite helpful to plan my
periodization routine more quickly. I'm sure it could come in helpful for
others too.

 

[Gary''s Student]

Start by listing the individual weights in a column:
10
10
10
2
2
2
2
2
2
1
1
1
1
1
1
1
1
20
20

Then you can use Solver to pick a subset that sums to a desired total. See:

http://www.tushar-mehta.com/excel/templates/match_values/index.html#Solver_Template

 

[nLinked]

Thanks that does work! All I need to specify is half the weight that I'll
need, let it solve it, and then I know I need these plates on one side, and
the same amount on the other. I'm going to look deeper into Solver.

Many thanks!

 

[Dana DeLouis]

I'm going to look deeper into Solver.

Just something to keep in mind for this particular type of problem.

To reduce the size of the Solver problem, I might use half the weights also.

wgts = {1, 1, 1, 1, 2, 2, 2, 10, 10, 20}

Note that many totals have more than 1 solution. For example, if I want 14
(total weight 28), then there are 3 solutions that total 14:

{14, {2, 2, 10}},
{14, {1, 1, 2, 10}},
{14, {1, 1, 1, 1, 10}}

My guess is that one would prefer the "Least" amount of weights to add.
If this is a requirement, then Solver becomes a little harder to use as it
requires mulitple loops.
For small problems like this, finding all the subsets of the above list
might be another option. For each total, pick the one with the least amout
of weights.

For example, for 20, pick
{10, 10},
instead of
{1, 1, 1, 1, 2, 2, 2, 10}


Note that with the numbers {1, 1, 1, 1, 2, 2, 2} summing to 10, then all
numbers between 1 and 50 can be included.

 

[Dave D-C]

The old "find the combination" problem.
Best done with recursion.

Say A1:E1 is Kg 20 10 2 1
and A2:E2 is Num 1 2 3 4
(1/2 the weights)
Then the following will find a combination
for 1/2 of the total. E.g. 15 gives
15 b 1 2 1 (which is 10+2*2+1=15)
in A4:E4

Option Explicit
Dim zTarget%, zBest%

Sub Main()
Rows(3).Resize(65534).Delete
zTarget = InputBox("Target?")
Call GetCombo(0, 2) ' kg, col
End Sub

Sub GetCombo(pKg&, pCol%)
' get weight combination routine
Dim iCol%, iNum%, CellSav
' see if this is better
If Abs(pKg - zTarget) < Abs(zBest - zTarget) Then
Rows(3).Copy Rows(4) ' best so far
zBest = pKg
Cells(4, 1) = pKg
End If
' go thru this column's weights
For iCol = pCol To 5
For iNum = Cells(2, iCol) To 1 Step -1
CellSav = Cells(3, iCol)
Cells(3, iCol) = iNum
' recursive call to next weights
Call GetCombo(pKg + Cells(1, iCol) * iNum, iCol + 1)
Cells(3, iCol) = CellSav
Next iNum
Next iCol
End Sub ' Dave D-C

There is a relatively easy speedup if you have many weights.

 

[nLinked]

Thank you for the code. I have tried pasting that into a macro and assigning
it to a button in Excel but I'm getting various errors. How can I get it to
work?

And in reply to Dana's reply, providing an answer to select the least
amounts of weights would be the best choice, yes. Unfortunately I have very
little programming knowledge but really want to get this solved.

Thanks.

 

[nLinked]

Hi Dave,

I have just got this working!

Thank you very much!

 

[nLinked]

Just to clarify, where you say:

Say A1:E1 is Kg 20 10 2 1
and A2:E2 is Num 1 2 3 4
(1/2 the weights)

Do you mean the values in the Num row should be half the quantity of each
weight that I own? So if I have two 20kg weights, I should put 1 in the Num
row?

Thanks.