+ Reply to Thread
Results 1 to 14 of 14

How do I determine which numbers in a list equal a given sum?

  1. #1
    infraterra
    Guest

    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. #2
    B. R.Ramachandran
    Guest

    RE: How do I determine which numbers in a list equal a given sum?

    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. #3
    infraterra
    Guest

    RE: How do I determine which numbers in a list equal a given sum?

    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. #4
    Ken Wright
    Guest

    Re: How do I determine which numbers in a list equal a given sum?

    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.




  5. #5
    Ken Wright
    Guest

    Re: How do I determine which numbers in a list equal a given sum?

    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.




  6. #6
    B. R.Ramachandran
    Guest

    Re: How do I determine which numbers in a list equal a given sum?

    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.

    >
    >
    >


  7. #7
    Ken Wright
    Guest

    Re: How do I determine which numbers in a list equal a given sum?

    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.

    >>
    >>
    >>




  8. #8
    Forum Contributor
    Join Date
    01-11-2004
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    388
    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

  9. #9
    infraterra
    Guest

    Re: How do I determine which numbers in a list equal a given sum?

    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.

    >
    >
    >


  10. #10
    jahoobob
    Guest

    Re: How do I determine which numbers in a list equal a given sum?


    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

  11. #11
    Ken Wright
    Guest

    Re: How do I determine which numbers in a list equal a given sum?

    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.

    >>
    >>
    >>




  12. #12
    Ken Wright
    Guest

    Re: How do I determine which numbers in a list equal a given sum?

    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




  13. #13
    Registered User
    Join Date
    12-29-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: How do I determine which numbers in a list equal a given sum?

    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.

  14. #14
    Registered User
    Join Date
    02-24-2016
    Location
    Newark Valley, NY
    MS-Off Ver
    2010
    Posts
    1

    Re: How do I determine which numbers in a list equal a given sum?

    Thank you so much Ken Wright for solving this for me!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1