Scenario?

[simmerdown]

I have a list of numbers (approximately 3000) that sum to X. However, my
control figure is Y.

Is there a formula that will evaluate the list of 3000 numbers and return
those that when summed, equal the difference between X and Y?

[Bernard Liengme]

You could do this with Solver but with 3000 number, it is unlikely that
there is a unique solution.
Do you care?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"simmerdown" <[email protected]> wrote in message
news:[email protected]...
>I have a list of numbers (approximately 3000) that sum to X. However, my
> control figure is Y.
>
> Is there a formula that will evaluate the list of 3000 numbers and return
> those that when summed, equal the difference between X and Y?

[simmerdown]

No, I don't care. At least that would narrow down the field, then I could
take it from there.

"Bernard Liengme" wrote:

> You could do this with Solver but with 3000 number, it is unlikely that
> there is a unique solution.
> Do you care?
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "simmerdown" <[email protected]> wrote in message
> news:[email protected]...
> >I have a list of numbers (approximately 3000) that sum to X. However, my
> > control figure is Y.
> >
> > Is there a formula that will evaluate the list of 3000 numbers and return
> > those that when summed, equal the difference between X and Y?
>
>
>

[Bernard Liengme]

Assuming your numbers are in A1:A3000
In B1:B3000 enter 1
C1 =SUMPRODUCT(A1:A3000, B1:B3000), C2 = Y (your target), C3 = ABS(C1-C2)

In Solver
Changing cells B1:B3000
Constraints C3=0 and B1:B3000 bin (binary - i.e. 0 or 1)
Solve

Does it work with your data?

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"simmerdown" <[email protected]> wrote in message
news:[email protected]...
> No, I don't care. At least that would narrow down the field, then I could
> take it from there.
>
> "Bernard Liengme" wrote:
>
>> You could do this with Solver but with 3000 number, it is unlikely that
>> there is a unique solution.
>> Do you care?
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "simmerdown" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a list of numbers (approximately 3000) that sum to X. However,
>> >my
>> > control figure is Y.
>> >
>> > Is there a formula that will evaluate the list of 3000 numbers and
>> > return
>> > those that when summed, equal the difference between X and Y?
>>
>>
>>

[simmerdown]

I really appreciate your help.

After inputting the information as described, once I hit "solver" I receive
a message that says "too many adjustable cells".

Any thoughts?

"Bernard Liengme" wrote:

> Assuming your numbers are in A1:A3000
> In B1:B3000 enter 1
> C1 =SUMPRODUCT(A1:A3000, B1:B3000), C2 = Y (your target), C3 = ABS(C1-C2)
>
> In Solver
> Changing cells B1:B3000
> Constraints C3=0 and B1:B3000 bin (binary - i.e. 0 or 1)
> Solve
>
> Does it work with your data?
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
>
> "simmerdown" <[email protected]> wrote in message
> news:[email protected]...
> > No, I don't care. At least that would narrow down the field, then I could
> > take it from there.
> >
> > "Bernard Liengme" wrote:
> >
> >> You could do this with Solver but with 3000 number, it is unlikely that
> >> there is a unique solution.
> >> Do you care?
> >> --
> >> Bernard V Liengme
> >> www.stfx.ca/people/bliengme
> >> remove caps from email
> >>
> >> "simmerdown" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have a list of numbers (approximately 3000) that sum to X. However,
> >> >my
> >> > control figure is Y.
> >> >
> >> > Is there a formula that will evaluate the list of 3000 numbers and
> >> > return
> >> > those that when summed, equal the difference between X and Y?
> >>
> >>
> >>
>
>
>

[Bernard Liengme]

Play with a smaller set of data and see if that works.
If so we can try to work out something
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"simmerdown" <[email protected]> wrote in message
news:[email protected]...
>I really appreciate your help.
>
> After inputting the information as described, once I hit "solver" I
> receive
> a message that says "too many adjustable cells".
>
> Any thoughts?
>
> "Bernard Liengme" wrote:
>
>> Assuming your numbers are in A1:A3000
>> In B1:B3000 enter 1
>> C1 =SUMPRODUCT(A1:A3000, B1:B3000), C2 = Y (your target), C3 = ABS(C1-C2)
>>
>> In Solver
>> Changing cells B1:B3000
>> Constraints C3=0 and B1:B3000 bin (binary - i.e. 0 or 1)
>> Solve
>>
>> Does it work with your data?
>>
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>>
>> "simmerdown" <[email protected]> wrote in message
>> news:[email protected]...
>> > No, I don't care. At least that would narrow down the field, then I
>> > could
>> > take it from there.
>> >
>> > "Bernard Liengme" wrote:
>> >
>> >> You could do this with Solver but with 3000 number, it is unlikely
>> >> that
>> >> there is a unique solution.
>> >> Do you care?
>> >> --
>> >> Bernard V Liengme
>> >> www.stfx.ca/people/bliengme
>> >> remove caps from email
>> >>
>> >> "simmerdown" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I have a list of numbers (approximately 3000) that sum to X.
>> >> >However,
>> >> >my
>> >> > control figure is Y.
>> >> >
>> >> > Is there a formula that will evaluate the list of 3000 numbers and
>> >> > return
>> >> > those that when summed, equal the difference between X and Y?
>> >>
>> >>
>> >>
>>
>>
>>

[simmerdown]

I narrowed the data down to about 25 figures. Again, I'm trying to
determine, of these figures, which of these when summed adds to X.

I used the solver listed below on the 25 figures, however I'm not sure I
understand the results.

Is there a possibility I could email you a sample file?

"Bernard Liengme" wrote:

> Play with a smaller set of data and see if that works.
> If so we can try to work out something
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "simmerdown" <[email protected]> wrote in message
> news:[email protected]...
> >I really appreciate your help.
> >
> > After inputting the information as described, once I hit "solver" I
> > receive
> > a message that says "too many adjustable cells".
> >
> > Any thoughts?
> >
> > "Bernard Liengme" wrote:
> >
> >> Assuming your numbers are in A1:A3000
> >> In B1:B3000 enter 1
> >> C1 =SUMPRODUCT(A1:A3000, B1:B3000), C2 = Y (your target), C3 = ABS(C1-C2)
> >>
> >> In Solver
> >> Changing cells B1:B3000
> >> Constraints C3=0 and B1:B3000 bin (binary - i.e. 0 or 1)
> >> Solve
> >>
> >> Does it work with your data?
> >>
> >> --
> >> Bernard V Liengme
> >> www.stfx.ca/people/bliengme
> >> remove caps from email
> >>
> >>
> >> "simmerdown" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > No, I don't care. At least that would narrow down the field, then I
> >> > could
> >> > take it from there.
> >> >
> >> > "Bernard Liengme" wrote:
> >> >
> >> >> You could do this with Solver but with 3000 number, it is unlikely
> >> >> that
> >> >> there is a unique solution.
> >> >> Do you care?
> >> >> --
> >> >> Bernard V Liengme
> >> >> www.stfx.ca/people/bliengme
> >> >> remove caps from email
> >> >>
> >> >> "simmerdown" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >I have a list of numbers (approximately 3000) that sum to X.
> >> >> >However,
> >> >> >my
> >> >> > control figure is Y.
> >> >> >
> >> >> > Is there a formula that will evaluate the list of 3000 numbers and
> >> >> > return
> >> >> > those that when summed, equal the difference between X and Y?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>