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.
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.
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.
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.
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" <infraterra@discussions.microsoft.com> wrote in message
news:403B381B-21DF-4095-AB44-04720B64E398@microsoft.com...
>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.
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" <infraterra@discussions.microsoft.com> wrote in message
news:66BD2B4C-B6B9-436E-ADC2-217B96A1ED6D@microsoft.com...
> 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.
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" <infraterra@discussions.microsoft.com> wrote in message
> news:403B381B-21DF-4095-AB44-04720B64E398@microsoft.com...
> >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.
>
>
>
LOL - Happy to provide a solution, but certainly can't claim the credit for
it :-)
Regards
Ken............
"B. R.Ramachandran" <BRRamachandran@discussions.microsoft.com> wrote in
message news:C2118A32-4436-4D80-A400-7D80D6784EB2@microsoft.com...
> 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" <infraterra@discussions.microsoft.com> wrote in message
>> news:403B381B-21DF-4095-AB44-04720B64E398@microsoft.com...
>> >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.
>>
>>
>>
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
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" <infraterra@discussions.microsoft.com> wrote in message
> news:403B381B-21DF-4095-AB44-04720B64E398@microsoft.com...
> >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.
>
>
>
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
LOl - You're very welcome
Regards
Ken..............
"infraterra" <infraterra@discussions.microsoft.com> wrote in message
news:E8FE192C-FC84-4EA8-9094-3B49FC0A1A08@microsoft.com...
> 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" <infraterra@discussions.microsoft.com> wrote in message
>> news:403B381B-21DF-4095-AB44-04720B64E398@microsoft.com...
>> >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.
>>
>>
>>
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" <jahoobob.1wjwtn@news.officefrustration.com> wrote in message
news:jahoobob.1wjwtn@news.officefrustration.com...
>
> 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
The SumMatch Excel Add-in gives you all the combinations of a group of numbers that add up to a target sum. The trial version is available from SumMatch.com.
Thank you so much Ken Wright for solving this for me!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks