Re: find combination of cells that equals a sum

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Feb 1, 4:27 pm, Billy Rogers
<BillyRog...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Thanks for the help!
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

"Gary''s Student" wrote:
Use Solver:

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

--
Gary''s Student - gsnu200767

"Billy Rogers" wrote:

A friend asked me this

"Would there by chance be a function in Excel where you can choose a range
of cells with an amount in each cell and see if any combination of those
cells added up to a certain dollar amount? Say, I have a range of data that
adds up to $1,536,211.26, but I'm trying to see if any of the cells in that
range add up to $12,455.98."

Have any of you guys ever done something like this? I think it may have to
be solved using VBA.

thanks

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

Solver's the best solution but the formulation on the link is the most
basic. It will report an infeasible solution if no combination of
values provides an exact equality. Better to Minimize the sum of the
deviations from the Target Value and if the solution is exact great,
if not, it tells you the best solution that gets you the closest.

The simplest extension is

Sum Xi - Target Value

But since you want to include both the negative and positive deviation
you can also run

Tanget Value - Sum Xi

And select the smallest difference.

Or really do it right, define an axillary variable Y and break into
its positive and negative components i.e.,

So Sum Xi + Y = Target Value

Y = Y' - Y'' so

Sum Xi + Y' - Y'' = Target Value

Then minimize on the auxillary variables with the single contraint
being an equality that is set to the target value and formulate this
way:

Min Y' + Y''

subject to

Sum Xi - Y' - Y'' = Target Value

SteveM
.

Relevant Pages

  • Re: find combination of cells that equals a sum
    ... of cells with an amount in each cell and see if any combination of those ... Sum Xi - Target Value ...
    (microsoft.public.excel.programming)
  • Re: How to find set of numbers to sum nearest to a given total but not more?
    ... Values Which Sum Nearest To Target Value ... 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" or a similar search. ... without any editing; just as shows after downloaded from: ...
    (microsoft.public.mac.office.excel)
  • Re: simple transport protocol
    ... I'm designing simple protocol for a bootloader, ... transfer data from host computer to target (ARM7-based embedded system) over ... ended with a control check sum. ... Command is to issue commans to target system such as start, stop, ...
    (comp.programming)
  • simple transport protocol
    ... transfer data from host computer to target (ARM7-based embedded system) over ... It should not be very reliable, so a simple checksum algorithm can be ... ended with a control check sum. ... Command is to issue commans to target system such as start, stop, ...
    (comp.programming)