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

[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!

 

[Mike Middleton]

Tony -

Tushar Mehta has a template:

http://www.tushar-mehta.com/excel/templates/match_values/index.html

- Mike
http://www.MikeMiddleton.com

 

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

------------------------------

 

[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

 

[Tony]

MIke,

Thanks. This one does it OK:

Values Which Sum Nearest To Target Value
http://www.ozgrid.com/forum/showthread.php?t=61367
http://www.ozgrid.com/forum/attachm...506fa78b2f835&attachmentid=19010&d=1165601488

Just

opening closest sum.xls (editing as required) and selecting:
Tools - Sover - Solve - OK

--------------