Creat formulas
 

How do I go about creating formulas with a set of given numbers and the final
total?

Ex. My numbers are 2,6,9,3 and end total is 75. I can use any function in
the formula but it must equal 75. I can only use the given numbers once, and
they must be used in that order.

Creat formulas
 

=(2+6)*9+3
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Shuggy"
wrote in message
How do I go about creating formulas with a set of given numbers and the final
total?
Ex. My numbers are 2,6,9,3 and end total is 75. I can use any function in
the formula but it must equal 75. I can only use the given numbers once, and
they must be used in that order.

Creat formulas
 

Hi and thanks.

Is there a way I can place this into excel and it spits out the formula for
me?

"Jim Cone" wrote:

=(2+6)*9+3
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Shuggy"
wrote in message
How do I go about creating formulas with a set of given numbers and the final
total?
Ex. My numbers are 2,6,9,3 and end total is 75. I can use any function in
the formula but it must equal 75. I can only use the given numbers once, and
they must be used in that order.

Creat formulas
 

If I understand you correctly, you are looking for (((2+6)*9)+3)?

You would have to use some programming to do that. Assuming the arithmetic
functions include +, -, *, /, ^, and ^1/n (nth root), you could try the UDF
below (or, at least it may give you a starting point). If the numbers
2,6,9,3 are in cells A1, A2, A3, and A4 the syntax is

=shuggy(A1,A2,A3,A4,75,1) where 75 is the target value and 1 is the nth
solution (some problems can have multiple solutions). Also, I added a
variable in the function for precision, as there will likely be some rounding
issues (remember computers use binary math) which you can change.



Function Shuggy(dblOne As Double, _
dblTwo As Double, dblThree As Double, _
dblFour As Double, dblTarget As Double, _
lngIndex As Long) As String

Dim dblPrecision As Double
Dim i As Long
Dim x As Long
Dim y As Long
Dim varOperators As Variant
Dim strExpression As String
Dim dblResult As Double
Dim lngCount As Long

dblPrecision = 0.000001
varOperators = Array("+", "-", "*", "/", "^", "^(1/")

For i = LBound(varOperators) To UBound(varOperators)
For x = LBound(varOperators) To UBound(varOperators)
For y = LBound(varOperators) To UBound(varOperators)
strExpression = "(((" & dblOne & varOperators(i) & _
dblTwo & ")" & IIf(i = UBound(varOperators), ")", "") & _
varOperators(x) & dblThree & ")" & _
IIf(x = UBound(varOperators), ")", "") & _
varOperators(y) & dblFour & ")" & _
IIf(y = UBound(varOperators), ")", "")
dblResult = Evaluate(strExpression)
If Abs(dblResult - dblTarget) <= dblPrecision Then
lngCount = lngCount + 1
If lngCount = lngIndex Then
Shuggy = strExpression
Exit Function
End If
End If
Next y
Next x
Next i

Shuggy = "Not Found"
End Function




"Shuggy" wrote:

How do I go about creating formulas with a set of given numbers and the final
total?

Ex. My numbers are 2,6,9,3 and end total is 75. I can use any function in
the formula but it must equal 75. I can only use the given numbers once, and
they must be used in that order.

Creat formulas
 

Nth root of a negative number causes an error so I added a line to check for
that:

Function Shuggy(dblOne As Double, _
dblTwo As Double, dblThree As Double, _
dblFour As Double, dblTarget As Double, _
lngIndex As Long) As String

Dim dblPrecision As Double
Dim i As Long
Dim x As Long
Dim y As Long
Dim varOperators As Variant
Dim strExpression As String
Dim dblResult As Double
Dim lngCount As Long

On Error Resume Next

dblPrecision = 0.000001
varOperators = Array("+", "-", "*", "/", "^", "^(1/")

For i = LBound(varOperators) To UBound(varOperators)
For x = LBound(varOperators) To UBound(varOperators)
For y = LBound(varOperators) To UBound(varOperators)
strExpression = "(((" & dblOne & varOperators(i) & _
dblTwo & ")" & IIf(i = UBound(varOperators), ")", "") & _
varOperators(x) & dblThree & ")" & _
IIf(x = UBound(varOperators), ")", "") & _
varOperators(y) & dblFour & ")" & _
IIf(y = UBound(varOperators), ")", "")
dblResult = Evaluate(strExpression)
If Err.Number = 0 Then
If Abs(dblResult - dblTarget) <= dblPrecision Then
lngCount = lngCount + 1
If lngCount = lngIndex Then
Shuggy = strExpression
Exit Function
End If
End If
Else: Err.Clear
End If
Next y
Next x
Next i

Shuggy = "Not Found"
End Function

"JMB" wrote:

If I understand you correctly, you are looking for (((2+6)*9)+3)?

You would have to use some programming to do that. Assuming the arithmetic
functions include +, -, *, /, ^, and ^1/n (nth root), you could try the UDF
below (or, at least it may give you a starting point). If the numbers
2,6,9,3 are in cells A1, A2, A3, and A4 the syntax is

=shuggy(A1,A2,A3,A4,75,1) where 75 is the target value and 1 is the nth
solution (some problems can have multiple solutions). Also, I added a
variable in the function for precision, as there will likely be some rounding
issues (remember computers use binary math) which you can change.



Function Shuggy(dblOne As Double, _
dblTwo As Double, dblThree As Double, _
dblFour As Double, dblTarget As Double, _
lngIndex As Long) As String

Dim dblPrecision As Double
Dim i As Long
Dim x As Long
Dim y As Long
Dim varOperators As Variant
Dim strExpression As String
Dim dblResult As Double
Dim lngCount As Long

dblPrecision = 0.000001
varOperators = Array("+", "-", "*", "/", "^", "^(1/")

For i = LBound(varOperators) To UBound(varOperators)
For x = LBound(varOperators) To UBound(varOperators)
For y = LBound(varOperators) To UBound(varOperators)
strExpression = "(((" & dblOne & varOperators(i) & _
dblTwo & ")" & IIf(i = UBound(varOperators), ")", "") & _
varOperators(x) & dblThree & ")" & _
IIf(x = UBound(varOperators), ")", "") & _
varOperators(y) & dblFour & ")" & _
IIf(y = UBound(varOperators), ")", "")
dblResult = Evaluate(strExpression)
If Abs(dblResult - dblTarget) <= dblPrecision Then
lngCount = lngCount + 1
If lngCount = lngIndex Then
Shuggy = strExpression
Exit Function
End If
End If
Next y
Next x
Next i

Shuggy = "Not Found"
End Function




"Shuggy" wrote:

How do I go about creating formulas with a set of given numbers and the final
total?

Ex. My numbers are 2,6,9,3 and end total is 75. I can use any function in
the formula but it must equal 75. I can only use the given numbers once, and
they must be used in that order.