Find the fewest values to equal a set total
 

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?

Find the fewest values to equal a set total
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Chris" wrote:

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?

Find the fewest values to equal a set total
 

Correction:
Formula in C7 should be
=SUM(C2:C6)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Chris" wrote:

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?

Find the fewest values to equal a set total
 

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))

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
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?

Find the fewest values to equal a set total
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"T. Valko" wrote:

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))

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
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?

Find the fewest values to equal a set total
 

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.

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"T. Valko" wrote:

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))

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
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?