1. doco says:

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. Greg says:

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