Excel Function
 

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

Excel Function
 

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_gr...ugroup=*excel*


In article ,
Linda wrote:

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!

Excel Function
 

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 toolssolver (if solver is not there you need to install it
under toolsadd-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




"Linda" wrote in message
...
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!
--
Linda

Excel Function
 

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



"Peo Sjoblom" wrote in message
...
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 toolssolver (if solver is not there you need to install
it under toolsadd-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




"Linda" wrote in message
...
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!
--
Linda