Combined Total Download
As discussed in the comments to which numbers sum to target, here is the workbook that Bernie mentions and Jim T emailed to me.
Download Combined Total.zip
Daily posts of Excel tips…and other stuff
As discussed in the comments to which numbers sum to target, here is the workbook that Bernie mentions and Jim T emailed to me.
Download Combined Total.zip
Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].
doco:
This would be excellent to find a group of sales ratios from a list that met a certain target of say Average, Median, Stdev , AveDev, GeoMean, etc. Cool! I’ll have to pick through the code and discover how this is done.
In stead of wanting to know the points that sum to a target…
List = { .85, .87, …, 1.05, 1.09 }
I would want to know a target that might be say Average = .99 or some other central tendency…
doco
8 November 2005, 7:08 amGreg:
I just looked at the original post that this file is related to. Am I the only one that uses Excel’s Solver add-in for this type of problem (which numbers from a list sum to a target value)? Seems fairly quick for any of the lists I’ve needed to pick through, and already built-in.
I don’t have the option to post screenshots, so I’ll try to explain verbally:
I put the list of available numbers in column A. Corresponding cells in column C become a calculation: =A1*B1, =A2*B2, =A3*B3, etc.
At the bottom of your calculation cells in column C, sum the column C values.
Go to Tools –> Solver (You may need to go to Add-Ins first, if you don’t have Solver installed already)
Parameters:
Set Target Cell: $C$41 (or whatever cell contains your total)
Equal to: Value of (enter target value you’re looking for)
By Changing Cells: Select cells in column B. $B$1:$B$40 in my example
Add a constraint: $B$1:$B$40=binary
Click Solve, and watch (if you like) Excel churn through all of the options. If Excel can find a solution, you’ll be able to filter on the “1″ values in column B. Don’t know how optomized it is, but it’s worked in the situations I’ve neeeded it so far.
Great blog - keep up the good work.
–Greg
17 November 2005, 11:02 am