Simple to do in head...Hard to do in XCell

  • Thread starter Conrad Mingledorff
  • Start date
C

Conrad Mingledorff

I have a column of numbers and a certain combination(s)
of these numbers equals a set value:
a1=5
a2=3
a3=7
a4=-2

The set value that I have = 8
The answer is: Cell a1 and a2.
How can I get Xcell to do this for me?
Thanks in advance.
Conrad
 
P

Paul

Conrad Mingledorff said:
I have a column of numbers and a certain combination(s)
of these numbers equals a set value:
a1=5
a2=3
a3=7
a4=-2

The set value that I have = 8
The answer is: Cell a1 and a2.
How can I get Xcell to do this for me?
Thanks in advance.
Conrad
Click to expand...

Computers are good at doing many calculations, even where most of the
answers are not required. You need to construct formulas for all the
possible combinations of cells (do you just want to add pairs, or would
singles, threes, fours, etc be acceptable?). Then you just look for a match
between your 'set value' and all these possibilities.
 
L

liddlem

Hi Conrad

Have you had a look at "Tools\Goal Seeker" or "Tools\Scenarios" o
"Tools\Solver" functions to see if they able to provide a solution?

If not, there are 2 other possible methods that you may wish to try.

The first is formula based and assumes that ..
A. The list is very short (As per your example)
B. The set value is in cell D2

This is a rather cumbersome (and dirty) method which requires that yo
have multiple 'staging' formulae before you get a final result.

Test whether the value in cell A1 is a valid candiate with thi
formula.
=IF(A1=D2,"a1",IF(A1+A2=D2, "a1 and a2", IF(A1+A3=D2,"a1 and a3"
IF(A1+A3=D2,"a1 and a3",IF(A1+A4=D2,"a1 and a4","Not Valid"))))

Now test whether the value in cell A2 is a valid candiate (In anothe
cell) with this formula.
=IF(A2=D2,"a2",IF(A2+A3=D2,"a2 and a3", IF(A2+A4=D2,"a2 and a4", "No
Valid")))

Test cells A3 and A4 respectively.

Finally, lookup the results where the value is <> "Not Valid"



The other solution requires a bit of code in a macro as follows.

Sub MyTest()
Dim MySetValue, MyFirstCell As Range
Dim MyValue1, MyValue2, MyTotal, MyRowCounter As Integer

MySetValue = Range("D2")
MyRowCounter = 1
Range("A1").Activate

Do While ActiveCell <> ""
MyRef = ActiveCell.Address
MyValue1 = ActiveCell
If MyValue1 = MySetValue Then
Range("D3") = MyRef
Exit Sub
Else
ActiveCell.Offset(1, 0).Activate
End If
Do While ActiveCell <> ""
MyTotal = MyValue1 + ActiveCell
If MyTotal = Range("D2") Then
MyRef = MyRef & " and " & ActiveCell.Address
Range("D3") = MyRef
Exit Sub
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
Range(MyRef).Activate
ActiveCell.Offset(1, 0).Activate
Loop
End Su
 
C

Conrad Mingledorff

-----Original Message-----
"Conrad Mingledorff"

Computers are good at doing many calculations, even where most of the
answers are not required. You need to construct formulas for all the
possible combinations of cells (do you just want to add pairs, or would
singles, threes, fours, etc be acceptable?). Then you just look for a match
between your 'set value' and all these possibilities.


.
Now we are getting closer :)
Click to expand...
I want it to be for all possible combinations. I just
don't know where to start.
Thanks again for your help with finding a way to do this.
 
P

Paul

Conrad Mingledorff said:
I want it to be for all possible combinations. I just
don't know where to start.
Thanks again for your help with finding a way to do this.
Click to expand...

You just have to be quite systematic in listing all possible combinations.
For four cells, A1:A4, there are 15 possible combinations:
=A1
=A2
=A3
=A4
=A1+A2
=A1+A3
=A1+A4
=A2+A3
=A2+A4
=A3+A4
=A1+A2+A3
=A1+A2+A4
=A1+A3+A4
=A2+A3+A4
=A1+A2+A3+A4

The number of possible combinations rises dramatically as you increase the
number of cells, but the principle remains the same.
 
C

Conrad Mingledorff

I dug into those a little, but will take a better look at
them. I was thinking that there is a function which will
look at every "Nth" row and do a calculation. Maybe using
this function and running some sort of macro?
 
Top