Excel Function

[Guest]

Is there an Excel function where you can select a group of cells (with dollar
amounts) and find out which of the cells total up to a partiular amount?
Example, if you have a check for $82,067.91 and there are 40 rows of figures,
and you need to know which figures total up to the amount of the check.
Thank You!

 

[JE McGimpsey]

No, there's no such function in XL. If you search the archives you'll
find various attempts using VBA. Be careful, as the methods may vary and
may not be what you want (e.g., in 40 rows of figures, there's very
likely to be multiple solutions):

http://groups.google.com/advanced_group_search?as_ugroup=*excel*

 

[Peo Sjoblom]

You can use solver (an add-in that comes with Excel) although you might need
the commercial version if it is too large

Assume the 40 rows are A1:A40, insert an empty adjacent column and in B1:B40
put 1 in each cell

put the amount 82,067.91 in D2 and in C2 put

=SUMPRODUCT(A1:A40,B1:B40)

Select C2 and do tools>solver (if solver is not there you need to install it
under tools>add-ins),
set target cell (should be selected if you selected C2) to $C$2, Equal to
Value of 82,067.91
by changing cells $B$1:$B$40, in the subject to the Constraints click add
and put $B$1:$B$40
in the cell reference and ion the dropdown select bin and click OK, then
click solve


--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

As John pointed out, there might be several solutions where a combination of
these 40 rows
will total the amount while solver will give you one solution if possible


--


Regards,


Peo Sjoblom