Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I determine which numbers in a list equal a given sum?
I have list of dollar amounts given to me and a general ledger sum which some
of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. |
#2
|
|||
|
|||
Hi,
If the dollar amounts are in say A2:A100, enter the following formula in B2, =if($A$2:$A$100=ledger sum,"x",""), where replace the words "ledger sum" with the actual amount (e.g., 12345.67), and click 'Enter'. Now fill-in the formula down to the last row. Regards, B. R. Ramachandran "infraterra" wrote: I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. |
#3
|
|||
|
|||
BRR,
Thanks for the help, but I don't think I did a very good job of explaining the situation. Let me use this example to clarify: If I had a specific total, say 10 and I had the following list of numbers {1,2,6,7} I want the program to indicate that if I use 1,2, and 7, I will get a sum of 10. Again, any help from anyone will be greatly appreciated. --Carlos "B. R.Ramachandran" wrote: Hi, If the dollar amounts are in say A2:A100, enter the following formula in B2, =if($A$2:$A$100=ledger sum,"x",""), where replace the words "ledger sum" with the actual amount (e.g., 12345.67), and click 'Enter'. Now fill-in the formula down to the last row. Regards, B. R. Ramachandran "infraterra" wrote: I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. |
#4
|
|||
|
|||
If you are looking for a solution (Not necessarily the only one) to a subset
of a group of numbers that will add up to a target number, then this can often be done with Solver. Assuming range of numbers in A1:A30, add a set of 0s in B1:B30 and in say B31 put =SUMPRODUCT(A1:A30*B1:B30) Now do Tools / Solver / Set Target Cell 'B31' to 'value of' and put in your target number. Then, using the range selector under the 'By Changing cells' section, select cells B1:B30 as the ones to change and hit enter which will take you back to the first dialog box. Now hit the 'Add' button, and add the constraint that B1:B30 must be 'bin' (Means binary as in 1 or 0, and it's one of the dropdowns, so just hit the arrow and select 'bin') and just hit Solve. You MUST ensure that in this example, when you add the 'bin' constraint range, you do not inadvertantly include the formula cell B31, else you will get an error message such as 'Binary Contsraint cell reference must include only adjustable cells' Won't do any more than single solution, but for a Finance Dept that will often suffice in this context. If you are going to look for more than one target number in the data, then with that formula in say B31, in B32 type the target number, and in B33 put =B32-B31. Now have Solver solve B33 = to 0 with the same constraints. Saves having to change any values in Solver that way, just type what you want in B32. Looks neater too if you format B1:B30 to a white Font and then use conditional formatting to highlight values in Col A where Val in Col B = 1 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "infraterra" wrote in message ... I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. |
#5
|
|||
|
|||
See my response as that is exactly what it does
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "infraterra" wrote in message ... BRR, Thanks for the help, but I don't think I did a very good job of explaining the situation. Let me use this example to clarify: If I had a specific total, say 10 and I had the following list of numbers {1,2,6,7} I want the program to indicate that if I use 1,2, and 7, I will get a sum of 10. Again, any help from anyone will be greatly appreciated. --Carlos "B. R.Ramachandran" wrote: Hi, If the dollar amounts are in say A2:A100, enter the following formula in B2, =if($A$2:$A$100=ledger sum,"x",""), where replace the words "ledger sum" with the actual amount (e.g., 12345.67), and click 'Enter'. Now fill-in the formula down to the last row. Regards, B. R. Ramachandran "infraterra" wrote: I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. |
#6
|
|||
|
|||
Ken,
Very impressive idea; excellent exploitation of the 'Solver' utility! One may randomize the positions of the data (A1:A30 in your example) and repeat the solver procedure to get multiple solutions. Of course, it would be a cumbersome process since it is impossinle to guess how many solutions are possible for a given scenario. Regards, B. R. Ramachandran "Ken Wright" wrote: If you are looking for a solution (Not necessarily the only one) to a subset of a group of numbers that will add up to a target number, then this can often be done with Solver. Assuming range of numbers in A1:A30, add a set of 0s in B1:B30 and in say B31 put =SUMPRODUCT(A1:A30*B1:B30) Now do Tools / Solver / Set Target Cell 'B31' to 'value of' and put in your target number. Then, using the range selector under the 'By Changing cells' section, select cells B1:B30 as the ones to change and hit enter which will take you back to the first dialog box. Now hit the 'Add' button, and add the constraint that B1:B30 must be 'bin' (Means binary as in 1 or 0, and it's one of the dropdowns, so just hit the arrow and select 'bin') and just hit Solve. You MUST ensure that in this example, when you add the 'bin' constraint range, you do not inadvertantly include the formula cell B31, else you will get an error message such as 'Binary Contsraint cell reference must include only adjustable cells' Won't do any more than single solution, but for a Finance Dept that will often suffice in this context. If you are going to look for more than one target number in the data, then with that formula in say B31, in B32 type the target number, and in B33 put =B32-B31. Now have Solver solve B33 = to 0 with the same constraints. Saves having to change any values in Solver that way, just type what you want in B32. Looks neater too if you format B1:B30 to a white Font and then use conditional formatting to highlight values in Col A where Val in Col B = 1 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "infraterra" wrote in message ... I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. |
#7
|
|||
|
|||
LOL - Happy to provide a solution, but certainly can't claim the credit for
it :-) Regards Ken............ "B. R.Ramachandran" wrote in message ... Ken, Very impressive idea; excellent exploitation of the 'Solver' utility! One may randomize the positions of the data (A1:A30 in your example) and repeat the solver procedure to get multiple solutions. Of course, it would be a cumbersome process since it is impossinle to guess how many solutions are possible for a given scenario. Regards, B. R. Ramachandran "Ken Wright" wrote: If you are looking for a solution (Not necessarily the only one) to a subset of a group of numbers that will add up to a target number, then this can often be done with Solver. Assuming range of numbers in A1:A30, add a set of 0s in B1:B30 and in say B31 put =SUMPRODUCT(A1:A30*B1:B30) Now do Tools / Solver / Set Target Cell 'B31' to 'value of' and put in your target number. Then, using the range selector under the 'By Changing cells' section, select cells B1:B30 as the ones to change and hit enter which will take you back to the first dialog box. Now hit the 'Add' button, and add the constraint that B1:B30 must be 'bin' (Means binary as in 1 or 0, and it's one of the dropdowns, so just hit the arrow and select 'bin') and just hit Solve. You MUST ensure that in this example, when you add the 'bin' constraint range, you do not inadvertantly include the formula cell B31, else you will get an error message such as 'Binary Contsraint cell reference must include only adjustable cells' Won't do any more than single solution, but for a Finance Dept that will often suffice in this context. If you are going to look for more than one target number in the data, then with that formula in say B31, in B32 type the target number, and in B33 put =B32-B31. Now have Solver solve B33 = to 0 with the same constraints. Saves having to change any values in Solver that way, just type what you want in B32. Looks neater too if you format B1:B30 to a white Font and then use conditional formatting to highlight values in Col A where Val in Col B = 1 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "infraterra" wrote in message ... I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. |
#8
|
|||
|
|||
If your list is in column A and your GL amount is in C1 then place this in B1: =If(A1=C$1, "Match"," ") Copy this to all the cells in column B that has a value next to in in column A. infraterra Wrote: I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. -- jahoobob |
#9
|
|||
|
|||
Ken,
YOU ARE THE MAN! It totally worked...and it rocks! Big ups to you and yours...have a great weekend. "Ken Wright" wrote: If you are looking for a solution (Not necessarily the only one) to a subset of a group of numbers that will add up to a target number, then this can often be done with Solver. Assuming range of numbers in A1:A30, add a set of 0s in B1:B30 and in say B31 put =SUMPRODUCT(A1:A30*B1:B30) Now do Tools / Solver / Set Target Cell 'B31' to 'value of' and put in your target number. Then, using the range selector under the 'By Changing cells' section, select cells B1:B30 as the ones to change and hit enter which will take you back to the first dialog box. Now hit the 'Add' button, and add the constraint that B1:B30 must be 'bin' (Means binary as in 1 or 0, and it's one of the dropdowns, so just hit the arrow and select 'bin') and just hit Solve. You MUST ensure that in this example, when you add the 'bin' constraint range, you do not inadvertantly include the formula cell B31, else you will get an error message such as 'Binary Contsraint cell reference must include only adjustable cells' Won't do any more than single solution, but for a Finance Dept that will often suffice in this context. If you are going to look for more than one target number in the data, then with that formula in say B31, in B32 type the target number, and in B33 put =B32-B31. Now have Solver solve B33 = to 0 with the same constraints. Saves having to change any values in Solver that way, just type what you want in B32. Looks neater too if you format B1:B30 to a white Font and then use conditional formatting to highlight values in Col A where Val in Col B = 1 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "infraterra" wrote in message ... I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. |
#10
|
|||
|
|||
Hi! Have a look at http://www.mrexcel.com/pc09.shtml Be patient: there is what seems to me to be a generalised solution to your question in there. I've run Ioannis' routine and it works. What more can one say? Alf -- AlfD ------------------------------------------------------------------------ AlfD's Profile: http://www.excelforum.com/member.php...fo&userid=4785 View this thread: http://www.excelforum.com/showthread...hreadid=474156 |
#11
|
|||
|
|||
LOl - You're very welcome
Regards Ken.............. "infraterra" wrote in message ... Ken, YOU ARE THE MAN! It totally worked...and it rocks! Big ups to you and yours...have a great weekend. "Ken Wright" wrote: If you are looking for a solution (Not necessarily the only one) to a subset of a group of numbers that will add up to a target number, then this can often be done with Solver. Assuming range of numbers in A1:A30, add a set of 0s in B1:B30 and in say B31 put =SUMPRODUCT(A1:A30*B1:B30) Now do Tools / Solver / Set Target Cell 'B31' to 'value of' and put in your target number. Then, using the range selector under the 'By Changing cells' section, select cells B1:B30 as the ones to change and hit enter which will take you back to the first dialog box. Now hit the 'Add' button, and add the constraint that B1:B30 must be 'bin' (Means binary as in 1 or 0, and it's one of the dropdowns, so just hit the arrow and select 'bin') and just hit Solve. You MUST ensure that in this example, when you add the 'bin' constraint range, you do not inadvertantly include the formula cell B31, else you will get an error message such as 'Binary Contsraint cell reference must include only adjustable cells' Won't do any more than single solution, but for a Finance Dept that will often suffice in this context. If you are going to look for more than one target number in the data, then with that formula in say B31, in B32 type the target number, and in B33 put =B32-B31. Now have Solver solve B33 = to 0 with the same constraints. Saves having to change any values in Solver that way, just type what you want in B32. Looks neater too if you format B1:B30 to a white Font and then use conditional formatting to highlight values in Col A where Val in Col B = 1 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "infraterra" wrote in message ... I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. |
#12
|
|||
|
|||
OP is looking for a series of numbers that when added together equal his
target value -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "jahoobob" wrote in message ... If your list is in column A and your GL amount is in C1 then place this in B1: =If(A1=C$1, "Match"," ") Copy this to all the cells in column B that has a value next to in in column A. infraterra Wrote: I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total? Thanks, in advance, for any help. -- jahoobob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine second (and then third) highest value in a list | Excel Discussion (Misc queries) | |||
check if 2 cells are equal but only if they contain numbers not i. | Excel Worksheet Functions | |||
How can I compare a number against a list of numbers | Excel Worksheet Functions | |||
Sorting List of Numbers | Excel Discussion (Misc queries) | |||
list of sequential numbers | Excel Discussion (Misc queries) |