Ernesto, If Peo's Solver solution doesn't work (it usually won't with that many numbers), send me an email (reply to this post, and take out the spaces and change the dot to . ), and I will send you a workbook with a macro that can handle cases that Solver won't. HTH, Bernie MS Excel MVP "ErnestoMarti" <ErnestoMarti.1umz2a_1125504302.683@excelforum-nospam.com> wrote in message news:ErnestoMarti.1umz2a_1125504302.683@excelforum-nospam.com... > > undefined > Hello!!!! The problem that I explain below is one that i have for a > long time... hope someone can understand it...!! > Is there any option in Excel, if I have a list of numbers (for example > 20 different numbers), I want to know which combination of these > numbers are the sum of "X" number??? > > > -- > ErnestoMarti > ------------------------------------------------------------------------ > ErnestoMarti's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26836 > View this thread: http://www.excelforum.com/showthread.php?threadid=400817 >

0 |

8/31/2005 5:25:06 PM

Dana: Your first example has 6 numbers, the second 11. Harlan and my criteria were 8 numbers. Herb

Harlan, Below is a problem statement from years ago, and the code that solves it relatively quickly - a few seconds. I tried your code to solve it, but my machine locked up after a couple of minutes. Perhaps there is something in Michel's code that might be of use in the current application. 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 in descending order ' 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 <michel.claes@CREDITCOMMUNAL.BE> 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 "Harlan Grove" <hrlngrv@aol.com> wrote in message news:1125640838.220134.309410@z14g2000cwz.googlegroups.com... > Fixed the code.

0 |

9/3/2005 8:28:00 PM

Mike, There are approximately 5.94 x10^23 possible combinations of 11 numbers out of 150 numbers, compared to 1.8 x 10^6 when you are dealing with 5 out of 20. If the 20 number problem took .001 second, the 150 number problem would take 10 million years.... HTH, Bernie MS Excel MVP > I've simplified my actual real life problem by saying its 5 from 20. But its > actually 11 from about 150. Will the macro able to handle this or is there > too much number crunching involved ?

0 |

9/7/2005 4:54:43 PM

"Harlan Grove" <hrlngrv@aol.com> wrote in message news:1126113264.303190.214400@o13g2000cwo.googlegroups.com... > Only if the target value were approximately equal to a random sum > of 11 of the largest half of the numbers would there be anything > close to this number of combinations actually generated. If the > target value were approximately equal to 11 times the average of > the 150 numbers, combinations involving more than a few of the > numbers below the 30th percentile or above the 70th percentile > would have been ruled out in shorter combinations. This assumes > a reasonable variance in the 150 numbers. Harlan, I was guessing that the same sort of population statistics applied to both the 150 number set as the 20 number set, and that of the ~1sec (I may have blinked and mis-timed the routine), 99.9% was overhead. (I didn't actually time it.) Still, even if the actual calc took only 1E-9 sec, it would still take 10 years.... on a machine with unlimited memory. But I think we can agree that the problem won't be solved on his PC anytime soon. And thanks, by the way, for the code. Works sweetly. Bernie

0 |

9/7/2005 6:10:34 PM

I need hellp with a formula. In Cell E8 I need to write a formula that will search Column A rows 18 thru 32 and if it finds the date 09/16/2006 it will display the row number. Any help will be greatly appreciated. Thanks Dianne You didn't say what result you want if the date is not present. One way... A1 = some date =IF(COUNTIF(A18:A32,A1),INDEX(ROW(A18:A32),MATCH(A1,A18:A32,0)),"") If the date isn't present the formula returns blank. -- Biff Microsoft Excel MVP "Dianne" <Dianne@discussions.microsoft.com> wrote in message n...

HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

I have an excel spreadsheet with 4 columns. the first two columns contains numbers which will never change the third column starts off with 0 (zero) and is repeated for 255 rows then on the 256 row it needs to change to 1 (so basically increment by 1 every 255 rows) the fourth column starts at 0 (zero) and counts up to 255 but then needs to reset it self back to 0 on the 256 row. Help Please!!!!!:confused: ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: I...

I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

I need a little help. How do you add up the values of numbers in column which has letters next to the numbers. I have a column o numbers, but the letters "mb" are a space bar away from the numbers. Everytime I use the function to add the numbers I get a value of zero. Could anyone please help me out? Thanks in advance -- Message posted from http://www.ExcelForum.com Assuming you mean the "numbers" all end with " mb", e.g. "123 mb", you can add a "Helper" column containing a formula like this: =--LEFT(A1,LEN(A1)-3) Copy down as far as...

Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

All: I have created an excel "Workorder" form for our employees to write in what machine they are working on, what parts they used, and how long they spent on the repair... The problem... I need to print out hundreds of these forms for the men to use, and each copy of the form needs to have an unique number. Is there a way to program a cell to automatically increment by 1 every time it is printed? Again, I need to print hundreds at a time, and I need them to automatically increment by 1. TIA, Lance Place this in ThisWorkbook module: Private Sub Workbook_BeforePrint(Cancel ...

I have a report that pulls numbers from one main table and 4 linked tables. The report is fairly basic showing a product quantity x price = total. The quanity value comes from one of the 5 tables. Here's the problem. My report needs to show 5 products, or one from each table. Howver, the customer may not be a customer for a product in table 5 so it is not listed in that table...therefore, no data to pull. I was hoping the control source would display a Zero value for the quanity. It displays nothing. So I end up with: 5 x 2.00 = 10.00 7 x 1.50 = 10.50 3 x 2.00 = 6.00 7 x 3.0...

I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

Is there a way to monitor Outlook 2007 usage statistics, such as avg number of emails sent/received per day? ...

Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

Hi Folks Is it possible to set a restriction at the organisation level to manage the total number of recipients a message can be sent to by a user, while still allowing a user to send to distribution lists which contains more users that the organisation restriction? Example: In Exchange manger we set a value of 20 under global settings / message delivery\defaults\recipients limit. We then create a DL that contains 30 users. Is it possible to allow the user to send to the DL of 30 users while maintaining a global recipient limit of 20? Why do we want to do this: We want to stop pe...

I have a finite number of values that represent length (right now I have less than 20 values). Can Excel take these values and list all possible combinations of the values? Any pointers appreciated. Stephen R. Stephen, No. There are 2,432,902,008,176,640,000 possible ways to combine 20 different values. That is, unless you have another limitation, like only using 4 at a time.... HTH, Bernie MS Excel MVP "Stephen R" <nospam@nospam.com> wrote in message news:u9ZiYhOeFHA.1456@TK2MSFTNGP15.phx.gbl... > I have a finite number of values that represent length (right...

I have a list of numbers that I need a quick and easy way to convert to a negative number. I have tried highlighting the numbers and using a custom format to add the negative sign and parenthesis but when you add the column up; it is not recognizing the numbers as negative. Any suggestions? Why not create a helper column to do it? "sparker" wrote: > I have a list of numbers that I need a quick and easy way to convert to a > negative number. I have tried highlighting the numbers and using a custom > format to add the negative sign and parenthesis but when you add t...

I have a line chart (looks like an S curve graph) with two rows of data below & this chart looks exactly like I want it to. Period % Complete Dec-04 7% Jan-05 9% Feb-05 10% Mar-05 12% Apr-05 14% May-05 19% Jun-05 22% Jul-05 23% Aug-05 24% Sep-05 26% Oct-05 28% Nov-05 32% Dec-05 36% Jan-06 40% Feb-06 45% Mar-06 50% Apr-06 55% May-06 59% Jun-06 63% Jul-06 66% Aug-06 71% Sep-06 75% Oct-06 78% Nov-06 81% Dec-06 85% Jan-07 87% Feb-07 89% Mar-07 91% Apr-07 93% May-07 94% Jun-07 94% Jul-07 95% Aug-07 95% Sep-07 95% Oct-07 95% Nov-07 95% Dec-07 95% Jan-08...

Is it possible to have more than 1024 characters in a cell? I have tried to use char(10) and char(13) however while the formula allows me to copy and paste more than 1024 characters it limits my statements to 100 characters after 1024. Is there any way to go around this and be able to have as many characters as one wish in a cell. Thanks You might search on "limits" in Excel help for this and other limits. A cell can hold a text entry of a max of 32k characters. A formula can have about 1000 characters. -- Jim "Dajana" <Dajana@discussions.microsoft.com>...

I have a number of identically structured worksheets. I wish to treat them as alternative realities, assigning a probability of occurrence to each. I want to combine all this into a probabilistically "average" worksheet. Anybody have a good way to do this? Thanks mike Mike - You can set up one spreadsheet with the spreadsheets listed and the weighting you want to assign to each. Then set up another spreadsheet that will contain the results. Copy any header / row information from one of the spreadsheets, then for each cell you want 'averaged', enter a f...

In a Contract on Contract Line item on the Administration Tab, there is a field for a serial number. I can't seem to find how to use Advanced Find to find a specific Serial Number. Anyone know a way in CRM 1.2? Is it possible to search for a serial number in 1.2 and if not, is it in 2.0? Thanks! Shauna ...

Although the dollar amount is correct, the number of shares displayed in my portfolio view doesn't increase when I buy additional shares. When I look at the register, there is dark line above the last two transactions�the ones that are not being reflected in the portfolio. As a test, I added 500 shares to the register, and the number of shares in the portfolio view still didn't change. What's wrong and how do I fix it? Thanks, Steve A dark line usually indicates a transaction dated in the future. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support....

Hi everyone, does anyone know how to do this? I hve a column of figures some positive and some negative. I want to format them so taht th negative numbers are expressed in (brackets) and also so that th column itself is decimal aligned, so even if I don't use decimal place the brackets "stick out". I know Word does this in tables, but I can' seem to make Excel do it. Help! Thanks, Vicky. :confused -- VickyJone ----------------------------------------------------------------------- VickyJones's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2448 ...

I am trying to accumulate the number of cells within a range of worksheets that have a non-zero value and have not been successful. I understand that I have limited functions available when using a 3-D reference - the only ones that I can see that are applicable are SUM, COUNT, and COUNTA. However, I do not want to summarize the values so that basically leaves me with COUNT and COUNTA I have tried: =COUNT('03-16:03-30'!B11) as well as =COUNTA('03-16:03-30'!B7>0) (as well as many other variations) will no luck coming up with the expected results. Basically, I am k...

I have Table 1 with Field IDTable1 [autonumber] [and other fields] and Table 2 with Field IDTable2 [autonumber] and Field GRV set to text [and other Fields] Is the following posibble: Can I automatically generate a number that can be used in GRV field of Table 2 consisting out of a combination of IDTable1 and IDTable2 written as IDTable1/IDTable2 in the GV field of Table 2 I want this GRV number as a unique number identifying Table 1 and Table 2 Thanks for the help You can, but shouldn't. All data in a table must identify the key. Foreign Keys, which are the keys f...

Can a conditional formula study a column of numbers and enter the highest or lowest in the result cell? If yes how do you do it? Hi ? =MAX(A:A) =MIN(A:A) -- Arvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) "Betternotpeak" <Betternotpeak@discussions.microsoft.com> wrote in message news:E985944A-A68E-4A3A-A3B3-1F81ED5826F5@microsoft.com... > Can a conditional formula study a column of numbers and enter the highest > or > lowest in the result cell? If yes how do you do it? thanks "Arvi Laanemets" wrote: > Hi > &g...

I have a formula that gives me the number of weeks in a period. How ca I get the fractions of a week to round up instead of down. For example, if the formula returns 15.1 weeks, I want the value to b 16 weeks -- Message posted from http://www.ExcelForum.com Hi have a look at the ROUNDUP function -- Regards Frank Kabel Frankfurt, Germany "barch78 >" <<barch78.14cd8v@excelforum-nospam.com> schrieb im Newsbeitrag news:barch78.14cd8v@excelforum-nospam.com... > I have a formula that gives me the number of weeks in a period. How can > I get the fractions of a wee...