Find the fewest values to equal a set total

[Chris]

I have 5 parts with a thickness of 1, 3, 5, 8, and 10. I need to figure out
the fewest parts that will equal a total thickness of any given number. If I
need a total thickness of 235, what's the smallest combination of 10s, 8s,
5s, 3s, and 1s that will get me to this total? (23 tens, and 1 five). I need
to plug in any number for the total thickness and figure out the fewest
possible parts to get me there (and the quantities of each). Can this be done
in Excel and how?

 

[Luke M]

We can use the Solver Add-in for this task. First, install the add-in by
going to Tools - Add-ins, select the solver.

Now, to set things up. In A2:A6, place your values of 10, 8, 5, 3, 1. We'll
designate B1 as your "goal", which is 235 in this case.
In C2, input this formula:
=A2*B2
Copy down to C6.
In B7, input this formula:
=SUM(B2:B6)
In C7:
=SUM(C2:C7)

Everything's setup here, time to seutp the solver. Under Tools, select
Solver (now one of the choices).
For target cell, input B7.
For equal to, choose "min".
By changing cells: B2:B6

Now, to setup the constraints. Click 'add', for cell reference input B1,
choose the equal symbol, and for constraint input C7
click "add"
For cell reference, input B2, and from the middle dropdown, select "int"
(as you can't have a partial part I assume)
click add.
Repeat the last two steps for cells B3:B6. Once done, hit ok.

From the main solver dialogue, click options. Place a check in the "assume
non-negative". Hit 'ok'.

You're good to go now! Hit Solve, and XL will find the answer for you.

 

[Luke M]

Correction:
Formula in C7 should be
=SUM(C2:C6)

 

[T. Valko]

Here's another one...

A1 = total thickness needed = 235

C1:G1 = 10, 8, 5, 3, 1

Enter this formula in C2:

=IF(A1<C1,0,INT(A1/C1))

Enter this formula in D2 and copy across to G2:

=IF(D1>$A1-SUMPRODUCT($C1:C1,$C2:C2),0,INT(($A1-SUMPRODUCT($C1:C1,$C2:C2))/D1))

 

[Luke M]

While that comes close Biff, its not always accurate. A quick example is a
total thickness of 16. Least amount of pieces would be two 8's, yet your
setup gives one 10, one 5, and one 1.

 

[T. Valko]

Argh!

Well, it's accurate but I guess it doen't always meet the condition of least
number of pieces. I didn't consider that.