Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creat | Excel Discussion (Misc queries) | |||
How to creat log scale chart? | Charts and Charting in Excel | |||
How can I creat | Excel Discussion (Misc queries) | |||
Creat a new worksheet | Excel Discussion (Misc queries) | |||
creat a macro to look for a file | Excel Discussion (Misc queries) |