Create formula with variables

  • Thread starter Thread starter Kelly
  • Start date Start date
K

Kelly

Hi title may not be clear, I'll try to explain.

I am trying to create a formula with a variable.

Example if cell
A1 is "="
A2 is "1"
A3 is "+"
A4 is "2"

And say cell B1is "=A1&A2&A3&A4" the value of cell B1 is
then "=1+2" but I want it to be a formula not just text!
I want to created this formula by piecing together info
from other cells.

I want cell B1 to have a value of "3" and if I change
cell A3 to "-" then I want the value of B1 to change to "-
2"

Hope this makes sense. I am using the office 2000 package
any suggestions appreciated.

Thanks

Kelly
 
I don't know how to get it to work without using an IF, like

=+IF(A3="+",A2+A4,IF(A3="-",A2-A4, IF(A3="*",A2*A4,IF(A3="/",A2/A4,""))))

that will do addition, subtraction, multiplication and division. But it
doesn't do anything but determine what's in A3, then do the corresponding
operation. Im not sure how to, or if it's possible to, use a text
representation of "+" to be used as an actual operator in a formula.
 
Hi Kelly
Try the following in B1
=IF(A3="+",A2+A4,IF(A3="-",A2-A4,""))
That will handle plus and minus, if you need Multiply and
Divide as well, do the following.
=IF(A3="+",A2+A4,IF(A3="-",A2-A4,IF(A3="*",A2*A4,IF
(A3="/",A2/A4,""))))

Regards
Michael
 
There are a couple of ways of doing this, you can use a VBA solution with a
UDF


Function TestMe() As Double
Application.Volatile
TestMe = Evaluate(Range("A1").Value & _
Range("A2").Value & _
Range("A3").Value & _
Range("A4").Value)
End Function

in any cell put

=TestMe()

a formula solution might be to exclude A1 and use something like this

=CHOOSE(MATCH(A3,{"*","+","-","/"},0),A2*A4,A2+A4,A2-A4,A2/A4)

finally you could use excel 4 technique, insert>name>define and type in Eval
in the refers to box put

=EVALUATE(Sheet3!$A$1&Sheet3!$A$2&Sheet3!$A$3&Sheet3!$A$4)

now type in

=Eval

in a cell.

NOTE! That it will crash some versions of Excel if you copy it to another
sheet so I would not personally use it.
 
Kelly

This can probably be done natively in Excel but a User Defined Function might
suffice if you can use a helper cell.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

This Function would be copied to a general module in your workbook. For help
on getting started with macros see David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

With your Concatenated text "=1+2" in B1 enter this in C1

=EvalCell(B1) will return 3

Change A3 to "-" and returns "-1" which is correct( 1-2 = -1)

Change any of A2, A3 or A4 and see results in C1.

Gord Dibben Excel MVP
 
Back
Top