Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.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 |
#2
Posted to microsoft.public.excel.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_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! |
#3
Posted to microsoft.public.excel.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 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 |
#4
Posted to microsoft.public.excel.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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
Excel 2002: Auto Sum function not working in large Excel file | Excel Discussion (Misc queries) | |||
challenge! javascript function into excel function | Excel Worksheet Functions | |||
Excel Workday Function with another function | Excel Discussion (Misc queries) | |||
Can you nest a MID function within a IF function in Excel | Excel Worksheet Functions |