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…


  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)

    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.


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

Leave a Reply

Your email address will not be published.