Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding amounts that sum to a number
I have a LARGE spreadsheet of items that I am trying to reconcile for an
entire month range. The issue that I am having - the bank statement may show one amount ($1,000), where the spreadsheet may have 4 random amounts that equal $1,000. I need to be able to find the items that equal a specified number without having to manually add up several amounts and hope for a match. Any suggestions? I have tried Conditional Formatting, Conditional Sum, and the Solver add-in. None of these will give me what I need - considering that I have about 400 items in a month. Thank you all so much for taking the time to read this! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding amounts that sum to a number
Try these instructions for Solver, from a reply by B. R. Ramachandran to a
similar question: Supposing the set of numbers is in A2:A11. Enter 1 in each cell in B2 to B11. Place the single number in C2. Enter the following formula in some other cell (say C3) =SUMPRODUCT(A2:A11,B2:B11)-C2 and click ENTER. Now you are going to use the Solver (the "Solver" add-in should be installed for this) to find the combination of numbers in Column A whose total would be equal to the single number you have entered in C2. For that, "Tools" -- "Solver" -- in "Solver Parameters" window, "Set Target Cell" $C$3 "Equal To" "Value of" 0 "By Changing Cells" $B$2:$B$11 "Subject to the Constraints"-- click "Add" -- enter $B2$2:$B$11, select "bin" from the popdown list (This would add a constraint which would read as "$B$2:$B$11=binary") Click "Solve" The solver would find the solution by changing some of the 1's in Column B to 0's. The set of Column A numbers for which Column B is 1 (and not 0) is the solution for your problem. If the solution is satisfactory, click "Keep Solver Solution". Note that if more than one solution is possible, Solver would find the first solution. To add a nice touch, maybe you can use Conditional Formatting to highlight the numbers in Column A for which Column B is 1. This only finds a single solution. I (Hutch) have a lengthy recursive macro that can find multiple solutions (if they exist). If the Solver approach doesn't satisfy, let me know and I can post my code for you. Hope this helps, Hutch "Nikki0195" wrote: I have a LARGE spreadsheet of items that I am trying to reconcile for an entire month range. The issue that I am having - the bank statement may show one amount ($1,000), where the spreadsheet may have 4 random amounts that equal $1,000. I need to be able to find the items that equal a specified number without having to manually add up several amounts and hope for a match. Any suggestions? I have tried Conditional Formatting, Conditional Sum, and the Solver add-in. None of these will give me what I need - considering that I have about 400 items in a month. Thank you all so much for taking the time to read this! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding amounts that sum to a number
Hi,
This is enormously difficult. I kept the link to a solution posted by Harlan Grove which in my opinion FWIW is an awesome piece of coding. Note in the comments at the top of the code you need to set the 2 references this is done in VB editor using TOOLS|References. http://www.microsoft.com/office/comm...664&sloc=en-us Mike "Nikki0195" wrote: I have a LARGE spreadsheet of items that I am trying to reconcile for an entire month range. The issue that I am having - the bank statement may show one amount ($1,000), where the spreadsheet may have 4 random amounts that equal $1,000. I need to be able to find the items that equal a specified number without having to manually add up several amounts and hope for a match. Any suggestions? I have tried Conditional Formatting, Conditional Sum, and the Solver add-in. None of these will give me what I need - considering that I have about 400 items in a month. Thank you all so much for taking the time to read this! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding amounts that sum to a number
Your data set is too large for this type of analysis. The number of possible
permiutations and combinations that will add up to a given number will be staggering and the processing time will be measured in months... For example in this list of 30 numbers. 85.89 53.27 2.97 7.00 80.70 33.91 60.62 10.28 44.61 42.28 6.74 96.54 93.79 29.29 49.43 39.03 63.34 22.82 69.89 49.47 76.24 52.15 40.29 55.69 58.80 22.52 91.93 20.39 34.65 45.26 There are 294 different combinations that add up to 275.35. With a list of 400 numbers the possibilites are massive. If nikki manages to do some of the matching to cut the list size down then it might be possible, but there will still be a very substantial amount of manual labour required. Generally speaking your search list needs to be less than 50 before the analysis becomes feasable. -- HTH... Jim Thomlinson "Mike H" wrote: Hi, This is enormously difficult. I kept the link to a solution posted by Harlan Grove which in my opinion FWIW is an awesome piece of coding. Note in the comments at the top of the code you need to set the 2 references this is done in VB editor using TOOLS|References. http://www.microsoft.com/office/comm...664&sloc=en-us Mike "Nikki0195" wrote: I have a LARGE spreadsheet of items that I am trying to reconcile for an entire month range. The issue that I am having - the bank statement may show one amount ($1,000), where the spreadsheet may have 4 random amounts that equal $1,000. I need to be able to find the items that equal a specified number without having to manually add up several amounts and hope for a match. Any suggestions? I have tried Conditional Formatting, Conditional Sum, and the Solver add-in. None of these will give me what I need - considering that I have about 400 items in a month. Thank you all so much for taking the time to read this! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I change column totals from number signs to amounts? | Excel Discussion (Misc queries) | |||
Finding first number in a row | Excel Worksheet Functions | |||
Rolling Monthly Amounts to Annual Monthly Amounts | Excel Discussion (Misc queries) | |||
11 $ amounts, 6 of the amounts must equal $3854.12, which 6? | Excel Discussion (Misc queries) | |||
finding a number and the number of times it occurs | Excel Discussion (Misc queries) |