2 thoughts on “Combined Total Download”

1. 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

2. 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

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.