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

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

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

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

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

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

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

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