Re: How do I determine which numbers in a list equal a given sum?
- From: "Ken Wright" <ken.wright@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 7 Oct 2005 19:23:15 +0100
LOL - Happy to provide a solution, but certainly can't claim the credit for
it :-)
Regards
Ken............
"B. R.Ramachandran" <BRRamachandran@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:C2118A32-4436-4D80-A400-7D80D6784EB2@xxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:403B381B-21DF-4095-AB44-04720B64E398@xxxxxxxxxxxxxxxx
>> >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.
>>
>>
>>
.
- Prev by Date: Re: Select Multiple sheets
- Next by Date: Re: what is the sign to keep the number the same in the formula
- Previous by thread: Select Multiple sheets
- Next by thread: Re: what is the sign to keep the number the same in the formula
- Index(es):
Relevant Pages
|
