|
|
|
Author |
Message |
DevilD
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
>
>
|
|
|
|
|
|
|