Excel Function  
Author Message
DevilD





PostPosted: Fri Oct 26 08:43:49 PDT 2007 Top

worksheet functions >> 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

Excel514  
 
 
JE





PostPosted: Fri Oct 26 08:43:49 PDT 2007 Top

worksheet functions >> 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_group_search?as_ugroup=*excel*





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





PostPosted: Fri Oct 26 08:52:05 PDT 2007 Top

worksheet functions >> 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 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






> 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


 
 
Peo





PostPosted: Fri Oct 26 08:57:36 PDT 2007 Top

worksheet functions >> 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





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


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