how to identify

[sarans]

how to identify a few cells in a list(A1) that sums a equal total in another
cell(B1)

i.e:

A1
---
1,245.55
34,543.68*
8,955.67*
43,873.93
11,282.08*

B1
---
54,781.43

thanks in advance.
--

sarans

[John Michl]

You could try using solver. I haven't done this in many years so I
don't remember the specifics. I think I set up a column adjacent to my
list of numbers. In the second column, I entered the number 1. I then
used =SumProduct(ColA:ColB) which equalled the sum of all numbers
(since they were multiplied by one.) I then used Solver to "play" with
the column of 1's setting some of them to zero until the formula
equalled the target value.

- John

[Gary''s Student]

Hi sarans:

With only 5 numbers, John's approach can be applied withou solver:

1. enter you values into A1 thru E1 and copy down to row 32.
2. enter the following table in F1 thru J32:
0 0 0 0 0
0 0 0 0 1
0 0 0 1 0
0 0 0 1 1
0 0 1 0 0
0 0 1 0 1
0 0 1 1 0
0 0 1 1 1
0 1 0 0 0
0 1 0 0 1
0 1 0 1 0
0 1 0 1 1
0 1 1 0 0
0 1 1 0 1
0 1 1 1 0
0 1 1 1 1
1 0 0 0 0
1 0 0 0 1
1 0 0 1 0
1 0 0 1 1
1 0 1 0 0
1 0 1 0 1
1 0 1 1 0
1 0 1 1 1
1 1 0 0 0
1 1 0 0 1
1 1 0 1 0
1 1 0 1 1
1 1 1 0 0
1 1 1 0 1
1 1 1 1 0
1 1 1 1 1

3. in K1 enter:
=SUMPRODUCT(A1:E1,F1:J1) and copy down.
Just pick the correct value out of column K
--
Gary's Student


"sarans" wrote:

> how to identify a few cells in a list(A1) that sums a equal total in another
> cell(B1)
>
> i.e:
>
> A1
> ---
> 1,245.55
> 34,543.68*
> 8,955.67*
> 43,873.93
> 11,282.08*
>
> B1
> ---
> 54,781.43
>
> thanks in advance.
> --
>
> sarans

[sarans]

Hi John

Thanks for your post.
I couldn't get the correct result.
Could you please place an example.

--
sarans


"John Michl" wrote:

> You could try using solver. I haven't done this in many years so I
> don't remember the specifics. I think I set up a column adjacent to my
> list of numbers. In the second column, I entered the number 1. I then
> used =SumProduct(ColA:ColB) which equalled the sum of all numbers
> (since they were multiplied by one.) I then used Solver to "play" with
> the column of 1's setting some of them to zero until the formula
> equalled the target value.
>
> - John
>
>

[sarans]

Hi Gary's Master

Yes, it works.

Thanks a lot.

You are great.

I still need to figure out John's approach too.

Best regards,

--
sarans


"Gary''s Student" wrote:

> Hi sarans:
>
> With only 5 numbers, John's approach can be applied withou solver:
>
> 1. enter you values into A1 thru E1 and copy down to row 32.
> 2. enter the following table in F1 thru J32:
> 0 0 0 0 0
> 0 0 0 0 1
> 0 0 0 1 0
> 0 0 0 1 1
> 0 0 1 0 0
> 0 0 1 0 1
> 0 0 1 1 0
> 0 0 1 1 1
> 0 1 0 0 0
> 0 1 0 0 1
> 0 1 0 1 0
> 0 1 0 1 1
> 0 1 1 0 0
> 0 1 1 0 1
> 0 1 1 1 0
> 0 1 1 1 1
> 1 0 0 0 0
> 1 0 0 0 1
> 1 0 0 1 0
> 1 0 0 1 1
> 1 0 1 0 0
> 1 0 1 0 1
> 1 0 1 1 0
> 1 0 1 1 1
> 1 1 0 0 0
> 1 1 0 0 1
> 1 1 0 1 0
> 1 1 0 1 1
> 1 1 1 0 0
> 1 1 1 0 1
> 1 1 1 1 0
> 1 1 1 1 1
>
> 3. in K1 enter:
> =SUMPRODUCT(A1:E1,F1:J1) and copy down.
> Just pick the correct value out of column K
> --
> Gary's Student
>
>
> "sarans" wrote:
>
> > how to identify a few cells in a list(A1) that sums a equal total in another
> > cell(B1)
> >
> > i.e:
> >
> > A1
> > ---
> > 1,245.55
> > 34,543.68*
> > 8,955.67*
> > 43,873.93
> > 11,282.08*
> >
> > B1
> > ---
> > 54,781.43
> >
> > thanks in advance.
> > --
> >
> > sarans

[John Michl]

In cell A1 through A5, enter the five numbers.
In cell B1 through B5, enter 0 (zero)
In cell B6 enter =SumProduct(A1:A5,B1:B5)
Choose from the menu, Tools > Solver
Set Target Cell to B6
Equal to Value of 54,781.43
By Changing Cells B1:B5
Subject to the Constraint B1:B5 = binary
Click Solve and those values that are part of the answer will show a
"1" next to them.

- John

[John Michl]

A minor modification that will make it more useful. Instead of
entering the target number in solver, enter in the spreadsheet. To
continue the above example,
A7 = Target Value
A8 = Variance
B7 = 54,781.43
B8 = abs(b6-b7)

In solver change "Equal to Value" to "Equal to Min" then solve.

When done, if the Variance is zero, you'll have an exact match. If it
is not zero, then you'll have the combination that is closest to the
target as possible.

- John
www.JohnMichl.com

[sarans]

Hi John

Thank you very much for your prompt response.

I will try later as I am out at this time.

Best regards,
--
sarans


"John Michl" wrote:

> A minor modification that will make it more useful. Instead of
> entering the target number in solver, enter in the spreadsheet. To
> continue the above example,
> A7 = Target Value
> A8 = Variance
> B7 = 54,781.43
> B8 = abs(b6-b7)
>
> In solver change "Equal to Value" to "Equal to Min" then solve.
>
> When done, if the Variance is zero, you'll have an exact match. If it
> is not zero, then you'll have the combination that is closest to the
> target as possible.
>
> - John
> www.JohnMichl.com
>
>