How to find set of numbers to sum nearest to a given total but not more?

T

Tony

How to find a set of numbers to sum the nearest by defect to a given
total but not more?

I have a set of numbers (invoice amounts) with decimals and want to
find a set of numbers to sum the nearest by defect to a given total but
not more. How to do it?

For instance, if I had the numbers 1, 3, and 10, and I wanted the set
to sum a total of 13 (or nearest one, not more), I would choose 3 & 10.
If I wanted a total of 14, I would choose them all, and if I wanted,
say, 9, I would choose 1 & 4 as the nearest one (not 10, since 10 is
more than the total 9).

Thanks!
 
T

Tony

Mike,

Thanks. I have tried to use the template-set-match.xls as is. I mean,
without any editing; just as shows after downloaded from:
http://www.tushar-mehta.com/excel/s...templates/match_values/template-set-match.zip

Using

Excel 11.5.8 (Office 11.5.8) on Mac OS X 10.6.3 (Intel) I cannot get it
to work:

- In step 8, the Load Model window shows:
$F$4
If I click OK to that, I get the error:
Load Model: Invalid Target Cell.

- In step 8, if I change $F$4 by J1:J4 as suggested, I get the error:
Load Model: Invalid Constraint Formula.

At this point I am lost. Your help most appreciated.

Thanks again.

------------------------------
 
M

Mike Middleton

Tony -

Maybe you could try some of the other methods that you'll find if you do a
Google search for "excel which values sum to target" (without the quotes) or
a similar search.

- Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top