#### mystery number solution

```I have a number of values that are \$s . I also have a sum of some of these
numbers. I need a formula to figure out which numbers add to my total. I do
not know how many numbers will equal my total. TIA

--
Keven
```
 0
keven (4)
7/21/2005 8:46:02 PM
excel.misc 78881 articles. 5 followers.

4 Replies
442 Views

Similar Articles

[PageSpeed] 41

```I've written software that will do this.  You have a column of numbers-
let's call it the pool- and a subset of numbers in the pool add up to

```
 0
CycleZen (674)
7/21/2005 9:11:43 PM
```Keven,

I can't think of a mathematical answer to this, looks like a trial and error
type of thing.

My approach would be to sort your numbers into order.
Take the largest number away from your total and see if the remainder is
larger or the same as the smallest number.
If not, then the largest number can be discarded.
If it is larger than the smallest number, find the largest number that is
the same as or next smaller to your remainder.

Sounds complicated but an example might help!

Total =10
Numbers = 9,8,6,4,3.

Total - largest number 10-9=1
1 is smaller than the smallest number in the list so you can discard 9

Next try
Total - largest number 10-8=2
2 is smaller than the smallest number in the list so you can discard 8

Next try
Total - Largest number 10 - 6 = 4
4 is bigger than the smallest number in the list so:-
Look through the list for the number which is = to 4 or is the next smallest
4 is in the list, so you've found the answer.

You could use recursion to get the answer for more complex examples.

I have assumed that there is only one possible answer to each problem.
If there is more than one answer, this method would only find the first
solution.

I've never tried using recursion in VBA and don't even know if it's
possible.
I would have preferred to use a lower level language like C++ for this, even
though I'm less than fluent in it.

Hope that helps
Henry

"Keven" <Keven@discussions.microsoft.com> wrote in message
>I have a number of values that are \$s . I also have a sum of some of these
> numbers. I need a formula to figure out which numbers add to my total. I
> do
> not know how many numbers will equal my total. TIA
>
> --
> Keven

```
 0
7/21/2005 9:25:38 PM
```Keven wrote:
> I have a number of values that are \$s . I also have a sum of some
> of these numbers. I need a formula to figure out which numbers add
> to my total. I do not know how many numbers will equal my total.

In general, there is no closed-form formula for this -- only
alogithms.  And of course, there can be multiple answers, unless
you restrict the solution space further (e.g, "find the largest
numbers that sum to the desired total").  In fact, it would be
good if you can restrict the solution space.  Otherwise, the
algorithm must look at all possible sums of 1, 2, ... up to N
numbers.  That can be a staggering number, depending on the size
of N (although some combinations will be truncated once an
overflow occurs).

I would not think Excel is the best tool for solving this problem,
although I guess VBA can be used to solve any problem that
requires a programming language.

(Hmm, does VBA support recursion?)

```
 0
joeu2004 (766)
7/21/2005 9:55:10 PM
```My software- VBA based, no recursion- uses a brute force algorithm to
test every possible combination.  joeu2...makes a very good point about
limiting the solution space: every time the number pool increases by
one, the number of combinations doubles.

```
 0
CycleZen (674)
7/21/2005 10:12:31 PM

Similar Artilces:

Numbers beginning with 0
Hi all! I'm having trouble figuring out what to do for a client. She has a speradsheet of addresses and unfortunately one postcode is 0200 (australian postcode). Of course when adding it into excel it obviously chops off the 0 and simply sets it to 200. When mail merging it is causing all sorts of grief and simply changing the format of the cell from number to text is apparently not helping. Is there a way to prevent excel from taking away the zero in a number formatted cell? Thanks in advance! How is she "adding" the post code to XL? If typing it in, preformat the c...

Excel cells should take number format and/or optional text eg Dec.
I usually pre-set number formats and other limitations on forms to ensure that I get the information that I need. Often, however, the date is vague and requires text, e.g., Nov 2005 instead of 11/1/05, which is Excel's default if you type in "Nov 2005." I can alleviate the issue on my own work, but my forms need to remain locked when being used by others. My suggestion is that an option be offered so a cell could take either/or text or number in the case of dates. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the ...

Sort House Numbers
How can I get excel to sort house numbers as I would like, i.e 1,2,2a,3,4,5,5a,5b, et. etc. So far I've ailed miserably. Any help out there? TIA Mike G Hi Mike, You will have to separate the digits into a separate cell. Extraction of a Group of Digits and Dashes, from postings by Harlan Grove Extract the First Set of Digits (#DigitsFirstID) http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm#DigitsFirstID -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvp...

Mysterious Retained Earnings account
Other than the obvious GL table, where else would one assign account numbers? My GL table DOES NOT have my Quickbooks retained earnings account number ANYWHERE in it; however, RMS continues to post to that account. WHY WHY WHY? Where could it be? Jennifer This is a multi-part message in MIME format. ------=_NextPart_000_0135_01C7F005.BCB88320 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Jennifer, IANAA, but from Help; This section describes how to export General Ledger (GL) accounting data = from Store Operations to a file, and ...

Checking cell for Number
I have about 200 numbers in column A. I would like to enter any number in Column B. In column C I would like, it to show if the number is used or free. can this be done with a if statement? Thanks Try... =IF(COUNTIF(\$A\$1:\$A\$200,B1),"Used","Free") If you have a list of numbers in Column B that you'd like to check, enter the above formula in C1, and copy and paste the formula to your other cells in Column C. Hope this helps! In article <E5CA9196-6FCF-492C-B488-1B7E431720CF@microsoft.com>, Lee <Lee@discussions.microsoft.com> wrote: > I have abou...

Format page number in Words Document
Hi, Appreciate if you can help me here. I am trying to number my pages in my document a bit differently using the header and footer but I can't seem to get around doing it. Normally the page numbers will run concurrently in the header or footer as in page 1,2,3,4...etc But what intend to do is add page 5 (a) after page 5 and before page 6. However when I do that under the header or footer page 5 becomes page 5 (a) How do I format this under the header or footer? Thanks You will need to use the following field construction { IF { PAGE } > 5 { IF { PAGE } &...

Count number of records after filtering
Hello, Probably a question that's easy to answer but I have been searching for hours now & it's driving me rather crazy: I have applied a filter to a form and now I want to know how many records I have got as a result, or better, I want to know if there are any records left - how do I do that? Tried so far: If Form.Recordset.Count = 0 Then ... If Iserror(Form.RecordSet.Count) Then ... If EOF(Recordset) Then ... but that all does not work. Thanks, Gerwin Gerwin: An easy way is to add a hidden text box, txtCount say, to the form, with a ControlSourc...

table of numbers 1 thru 52 pre sql2k5
Hi all, What's the easiest way to generate a single column of numbers in a table prior to sql 2k5? thanks, rodchar Check out: http://msdn.microsoft.com/en-us/library/aa175802(SQL.80).aspx -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "rodchar" <rodchar@discussions.microsoft.com> wrote in message news:C3DD455A-8DC5-42CD-884D-EB67EF6A4B5A@microsoft.com... Hi all, What's the easie...

comparing columns of numbers
Hi If you have 2 columns of numbers and you want to find the numbers that are in one columns but not the other, and you type say A10=B10 then when you find a figure that is in one column but not the other you insert/delete a line and then drag the formula down again to keep checking. Is there any better formula I can use or anyway I can do it so I don't have to keep dragging down the formula Public Sub ProcessData() Const TestCol1 As String = "A" Const TestCol2 As String = "B" Dim i As Long Dim LastRow As Long With Application .ScreenUpdating = Fal...

formatting selected characters or numbers in each cell within a range of cells
I am sending this to several Excel newsgroups, because I am not sure which one if the most appropriate. I apologize in advance for the multiple postings. I have MS Excel 2000 (version 9.0.3821 SR-1). How can I format only a selected character or number within each cell in a specific range of cells? For instance if I have a cell containing �1234234�, how can I format it so that only the 2�s are bold or are colored? Obviously, I have a range of cells, and I want to format all the 2�s occurring in any of the cells as either bold or colored. Conditional formatting does not seem to all...

Find number of weekdays and wekend days given a total number of da
Hello, I have a question for the gurus here. I am working on a spreadsheet where in column A I have Total # of Vacation days. Example: # Vac Days --- # Weekdays --- # Weekend days 75 55 20 44 32 12 25 19 6 I figured this out by simply creating a list like this 1 Weekday 2 Weekday 3 Weekday 4 Weekday 5 Weekday 6 Weekend 7 Weekend And so on... And then doing a countif to figure out how many of either on...

Excel default formatting of numbers
I have a CSV file which is generated by an inhouse application. One of the columns in this file has a four digit code, which are text but made of numerical digits. Some of the codes start with one or more zeroes, ie 0012, 0013, 0014 etc. The problem is when my users open the file in Excel the column has been formatted as numbers and the leading zeroes have been trimmed, so 0013 becomes 13. I do not want my users to have to play around with formatting. Also even if I do select the column and change the formatting to text the leading zeroes are still missing. Please could anyone help me with a ...

Store numbering
Does RMS care if stores are sequentially numbered? I.E. if i have 5 stores can I number them 1,13,14,15,21? Or does that mess with the licensing? -- Pat pat@arms wrote: > Does RMS care if stores are sequentially numbered? I.E. if i have 5 stores > can I number them 1,13,14,15,21? Or does that mess with the licensing? The StoreCode is not sequentially numbered... I use 120, 250, 377.. 686 ... the code of main supplier of stores to identify electronic-invoice, orders and others bye antonio ...

can you reassign week numbers
want to assign week 1 to the starting date for each new group is this possible? The groups will be around for about 14 weeks each, so would want each group to have 14 weeks of information Any ideas? No, week numbers are calculated using an intrinsic function. What you can do is store the date that is the beginning date of the first week. Then, you can use the datediff function to calculate the weeks from the beginnig to a given date. -- Dave Hargis, Microsoft Access MVP "Pat" wrote: > want to assign week 1 to the starting date for each new group is this > possible? ...

Errors rebuilding solution
I am running into errors when I attempt to rebuild a project. To test, download and unzip this zip file (download DotNetEvents.zip to your PC from this link: https://developer.intuit.com/QuickBooksSDK/Resources/Samples.asp?id=96#DotNetEvents%20(VB.NET)%20(qbfc)%20(desktop) to your machine (say to your Desktop) and add the solution file to VS.NET 2003. If you BUILD the solution, all works fine. But if you REBUILD the solution at any time, the following errors are found in the WrapperCOMEXE.idl: 1) error MIDL2337 : unsatisfied forward declaration : IQBEventCallBack [Coclass 'MyWrapper...

formula to add a number to a long string **
I'm not quite sure how to best describe this: I need a formula that will take the example below and return the "final result" Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578 Is there a way to do this? This seems to work: =LEFT(A1,LEN(A1)-3)&(RIGHT(TEXT(A1,"0"),3))+23 HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "MMangen" wrote: > I'm not quite sure how to best describe this: > > I need a formula that will take the e...

Return only the numbers contained in a field
Hello, I would like some help please building a query that will return the numbers only contained in a field: Table "PurchaseOrder" has a "Memo" field that usually has the following data: "Sales Order 7540:" or "Sales Order 34:". How can I create a query that will show me all the Sales Orders and their linked Purchase Orders (PurchaseOrder.RefNumber) where the SalesOrder.RefNumber equals only the numbers in PurchaseOrder.Memo field? Thanks in advance, Brett Welllll. You can but it might not be pretty, accurate, or fast. Actually I'd expect ugly...

SOP Quick Print
We cannot print more than one copy of the Packing Slip with SOP Quick Print. In Quick Print setup, we have the Destination set to Printer. We want 2 copies of the Order and 2 copies of the Packing Slip. We get the 2 copies of the order but, for some reason, only 1 copy of the Packing Slip prints. We can get multiple copies of every document EXCEPT the packing slip. Even if we print JUST the packcing slip, it still won't print more than one copy. Any ideas? "Elaine" wrote: > We cannot print more than one copy of the Packing Slip with SOP Quick Print. > In Q...

converting numbers to time
I have a list of numbers: 900 1030 830 etc. I need these in the format: 09:00:00 10:30:00 08:30:00 etc I have tried text to columns but without the 0 before 830 the fixed width line doesnt fall right. Very stuck on this so any help would be gratefully recieved (otherwise I have alot of work on my hands!!) Assumes C5 has 900 and Time format set to 00:00:00 HTH Rob =TIME(INT(C5/100),MOD(C5,100),0) "FishandChips" <FishandChips@discussions.microsoft.com> wrote in message news:41FD6501-94DA-4944-93CE-B382DDA05126@microsoft.com... >I have a lis...

pick a number from 1 to 10
hey all, i want to create an asp.net web page that allows internet users to pick a number from 1 to 10. how do i manage users picking the same number? (if 2 users somewhere happen to be selecting the same number and the same time). Is this a concurrency issue? thanks, rodchar hi Rodchar, On 19.12.2009 19:01, rodchar wrote: > i want to create an asp.net web page that allows internet users to pick a > number from 1 to 10. You may use ten links or buttons. But what should happens after they "picked" a number? > how do i manage users picking the same nu...

Counting number of worksheets in a Excel 2007
Dear Sir, I am using MS office 2007, and want to know what is command/formula to count the number of worksheets in a Excel. I deals with some taxation matter wherein I need to have hundreds of sheets in a file. Thank in advance. From within Excel code, Worksheets.Count will return the number of worksheets in the workbook. -- Rick (MVP - Excel) "Y D" <Y D@discussions.microsoft.com> wrote in message news:B2C3A4B9-22F4-4643-8850-BD340C4659C9@microsoft.com... > Dear Sir, > I am using MS office 2007, and want to know what is command/formula to >...

Find cells containing a specified number of characters
I have a spreadsheet with only 1 column of data, but 18,000 lines. Is there an easy way to find all cells that contain exactly 12 characters? I tried searching for ???????????? and ************ but it finds every cell. If you use Data->Filter->Advanced Filter you can use the 12 ?s as your filter. You need a column header - let's say it is Values, and let's say your data is in column A Then in C1 type Values and in C2 type ???????????? Then select a cell in column A and go to Advanced Filter. Set C1:C2 as the criteria range, and clikc on OK "jdanker" wrote:...

Text to Number format
I have copied a column of numbers to excel and now cannot apply any formulas because the numbers are text. How can I convert the text to numbers? Thanks. -- Janet In an un-used cell enter 1 (as a true number). Copy this cell and paste/special/multiply onto the cells you would like to convert. -- Gary''s Student - gsnu201001 "Janet" wrote: > I have copied a column of numbers to excel and now cannot apply any formulas > because the numbers are text. How can I convert the text to numbers? > Thanks. > -- > Janet perfect timing, I jus...

Merging GP Vendor Numbers
We have a situation with a vendor that has two vendor numbers. Both of his vendor numbers have history attached. Is there a way to merge the two numbers into one? Yes there is. You can use the Professional Services Tools Library to do so, specifically the Vendor Combiner tool, however, this may require you to register the tool or work with your partner to do so. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "BFogle" wrote: > We have a situation with a vendor that has two vendor numbers. Both of his ...

Add fax number to RM Statements
Under Tools >> Routines >> Sales >> Statements, the report uses the "Statement To" code to pick up the customer information, but the fax number is not included. When locally modified and the fax number is added, it is the fax number from the main customer maintenance screen and not the fax number from the address record associated with the "Statement To" data. Great Plains indicates that under the current design, the fax number can not be included. We suggest that this be changed to include the fax number within the report design that is associated ...