Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
infraterra
 
Posts: n/a
Default 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   Report Post  
B. R.Ramachandran
 
Posts: n/a
Default

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   Report Post  
infraterra
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
B. R.Ramachandran
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
jahoobob
 
Posts: n/a
Default


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   Report Post  
infraterra
 
Posts: n/a
Default

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   Report Post  
AlfD
 
Posts: n/a
Default


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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to determine second (and then third) highest value in a list Scott M. Lyon Excel Discussion (Misc queries) 4 September 12th 05 08:51 PM
check if 2 cells are equal but only if they contain numbers not i. Peter Boardman Excel Worksheet Functions 4 April 17th 05 08:16 PM
How can I compare a number against a list of numbers johnny Excel Worksheet Functions 4 March 22nd 05 08:13 PM
Sorting List of Numbers Rowf Excel Discussion (Misc queries) 4 March 8th 05 09:29 PM
list of sequential numbers kellyaek Excel Discussion (Misc queries) 2 December 3rd 04 05:45 PM


All times are GMT +1. The time now is 04:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"