Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Formula to find equal values with blank cells | Excel Discussion (Misc queries) | |||
Selecting values from a range that equal a specific total | Excel Worksheet Functions | |||
How to:Find the row of the first cell not equal to several values | Excel Worksheet Functions | |||
find all employees whose total salaries equal a given amount | Excel Discussion (Misc queries) | |||
Find the combination of numbers that when added equal a reqired total?? | Excel Worksheet Functions |