Formula Help

[Stelios]

am using a way to do the following

column1 column 2 column 3
(2+3)+(3*5) 5+15 20


in the first column I want to be able to see the expresion
in the second column I want to have the result of each parenthesis
and in the final column the result

Please help
Thanks
Stelios

 

[FloMM2]

Stelios,
Here is one solution:
Break down your equation so each part is in it own column.
Column A Column B Column C etc.
( 2 + etc.

Somewhere on the same worksheet list the numbers(0 -9) and the
operators (*, /, +, - ). I used R3:R12 for numbers and S3:S6 for
operators.
Column A Column E Column G Column K
( ) ( )
Column B, C, D, F, M
Use Validation to do a drop down "List".

Column L uses an "IF statement"
"=IF(C2="*",(B2*D2),IF(C2="/",(B2/D2),IF(C2="+",(B2+D2),_
IF(C2="-",(B2-D2),0))))" Leave off the first " and last ". The "
inside the IF statement are necessary.

Column N uses an "IF statement"
"=IF(I2="*",(H2*J2),IF(I2="/",(H2/J2),IF(I2="+",(H2+J2),_
IF(I2="-",(H2-J2),0))))" Leave off the first " and last ". The "
inside the IF statement are necessary.


Column P uses an "IF statement"
"=IF(M2="*",(L2*N2),IF(M2="/",(L2/L2),IF(M2="+",(L2+N2),_
IF(M2="-",(L2-N2),0))))" Leave off the first " and last ". The "
inside the IF statement are necessary.

You can copy this down, and change the numbers and the operators
and the answer in column P will change with the ne information.

hth

 

[Dave D-C]

This will do it. Let me know what grade we got.

Sub Main()
Dim ch$, strOld$, strNew$, lenTmp%
Dim iPos%, iPosLParen%, iPosRParen%, iCol%
iCol = 2
strOld = Cells(1, 1).Value
Do
strNew = ""
iPosRParen = 0
For iPos = 1 To Len(strOld)
ch = Mid$(strOld, iPos, 1)
strNew = strNew & ch
If ch = "(" Then
iPosLParen = iPos
ElseIf ch = ")" Then
iPosRParen = iPos
If iPosLParen <> 0 Then
lenTmp = iPosRParen - iPosLParen + 1
strNew = Left$(strNew, Len(strNew) - lenTmp) & _
Application.Evaluate( _
Mid$(strOld, iPosLParen + 1, lenTmp - 2))
End If
iPosLParen = 0
End If
Next iPos
If iPosRParen = 0 Then strNew = Application.Evaluate(strNew)
Cells(1, iCol) = "'" & strNew
iCol = iCol + 1
strOld = strNew
Loop While iPosRParen <> 0
End Sub ' Dave D-C