Search range for combinations of values
Posted by KT on March 14, 2001 11:18 AM
I'm working on an audit. I know what the answer is (ex. $50,000). I want to search a range to see if any two of the cells equal 50,000 when added together. Solver doesn't seem to handle this. Any help would be appreciated.
KT
Posted by Mark W. on March 14, 2001 11:36 AM
KT, suppose that cells A1:A5 contains
{30000;20000;50000;10000;15000}. The array formula,
{=OR(50000=(A1:A5+TRANSPOSE(A1:A5)))} reveals that
this set does contain a pair of values whose total
is 50000.
Posted by Aladin Akyurek on March 14, 2001 11:43 AM
=COUNTIF(A2:$A$5,""&$B$1-A1) [ copy down to the before the last number]
where A1:A5 is the range of numbers you want to evaluate and B1 contains the answer ($50,000).
It gives you a count per number for which another number exists summing up to the answer.
=INDEX(A2:$A$5,MATCH($B$1-A1,A2:$A$5,0))
This formula gives you per number the other number, which together sum up to the answer.
Aladin
This formula
Posted by Mark W. on March 14, 2001 11:49 AM
Oops! Necessary revision...
Instead, use:
{=AND(OR(50000=(A1:A5+TRANSPOSE(A1:A5))),COUNTIF(A1:A5,50000/2)<>1)}
The other formula gave incorrect results if
A1:A5 contained {30000;25000;50000;10000;15000}.
Ahhh... the importance of clever test data! : )
Posted by Aladin Akyurek on March 14, 2001 12:57 PM
Re: Oops! Necessary revision...
Mark, this one flounders on {24000;25000;40000;26000;10000}.
Posted by Mark W. on March 14, 2001 1:16 PM
Yep! Back to the drawing board... : )
Posted by Mark W. on March 14, 2001 2:07 PM
Okay...Got the kinks out!
{=OR(50000=(ROW(A1:A5)<>TRANSPOSE(ROW(A1:A5)))*(A1:A5+TRANSPOSE(A1:A5)))}
Posted by Mark W. on March 14, 2001 2:54 PM
Now, on to your conclusion about Solver...
> Solver doesn't seem to handle this.
Try this model:
A1:A5 contains {10000;20000;30000;40000;50000}
B6 contains =SUM(B1:B5)
B7 contains =SUMPRODUCT(A1:A5,B1:B5)
Set Target Cell: $B$7 Equal To: 50000
By Changing Cells: $B$1:$B$5
...Constraints: $B$1:$B$5 = binary, $B$6 = 2
This works for me!
Posted by K.T. on March 15, 2001 10:57 AM
Thanks for the help guys. Mark, I'm trying your solver solution. It's been running for a while, but at least it's doing SOMETHING. My actual dataset is a bit more complex than my example, but if this works, it will be a huge timesaver.
Much appreciated - thanks.
KT
