How to evaluate a string as a formula?

[zhaoning.zju]

Hi all,
I am going to write a string formula (without "=") in a cell, e.g.
(A1+B1)/2 in C1. And I want the cell C2 to display the value of the
formula in C1.
To be concrete, let's take an example as follows:
In a sheet, I write:
110 in A1,
90 in A2,
(A1+A2)/2 in C1.
What formula shall I write in cell C2 to get a value of 100, and get a
value of 50 when C1 is changed to (A1+A2)/4?
Thanks!

 

[Raman]

Be more specific
You could enter the same formula
It is not possible in excel to write a function without =
You could use the tools-options-transition-transition formula entry
option to use excel as a virtual calculator

 

[Ron Rosenfeld]

Hi all,
I am going to write a string formula (without "=") in a cell, e.g.
(A1+B1)/2 in C1. And I want the cell C2 to display the value of the
formula in C1.
To be concrete, let's take an example as follows:
In a sheet, I write:
110 in A1,
90 in A2,
(A1+A2)/2 in C1.
What formula shall I write in cell C2 to get a value of 100, and get a
value of 50 when C1 is changed to (A1+A2)/4?
Thanks!

You will need to use VBA in order to do this.

<alt-F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then select
Insert/Module from the top menu and enter the code below into the window that
opens.

Then, in C2, enter the formula: =Eval(C1)

=====================
Function Eval(rg As Range)
Application.Volatile
Eval = Evaluate(rg.Text)
End Function
======================



--ron

 

[bplumhoff]

Hello,

you might want to use the VBA function EVAL() shown at
http://www.decisionmodels.com/calcsecretsh.htm

Another approach is shown at
http://www.sulprobil.com/html/it_quality.html

HTH,
Bernd

 

[Guest]

With the cursor in C2, for example, Insert-Define-Name: "Formul" (or what u
want),
and , in "Refers to" introduce =EVALUATE(C1), and Accept.

Once in the sheet again in C2 put
=Formul (the name previoiusly defined) and u can modify the formula in C1
that you obtain the result in C2.

Hope this helps

 

[zhaoning.zju]

Thanks, I think the method you provided is exactly what I want. But
when I did it that way, it showed me a "#NAME?" in C2. What's the
problem?

 

[Peo Sjoblom]

Did you do this part?

"You will need to use VBA in order to do this.

<alt-F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
select
Insert/Module from the top menu and enter the code below into the window
that
opens."

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Ron Rosenfeld]

Thanks, I think the method you provided is exactly what I want. But
when I did it that way, it showed me a "#NAME?" in C2. What's the
problem?

Post back, by copying exactly (Edit/Copy) exactly what you entered into the VB
Editor and exactly what you entered as a formula.


--ron

 

[zhaoning.zju]

In VBA module, I wrote(actually copied your code):
Function Eval(rg As Range)
Application.Volatile
Eval = Evaluate(rg.Text)
End Function

In the worksheet, cell C1, I wrote:
(A1+A2)/2

In cell C2, I wrote:
=eval(C1)

And I am sure that the VBA module is under the same project with the
workbook.

 

[TKT-Tang]

Have you not referred to the (ubiquitous) EEE letters located at
J-Walk's Excel web-site ?

Regards.

 

[zhaoning.zju]

J-Walk's Excel web-site?
Where is it?

 

[TKT-Tang]

J-Walk's Excel web-site ?
Where is it ?

I see ; J-Walk is not his real name previously. But nowadays, he is
running a blog by that name. That shows that he likes the name.

Please visit the following link (to start with, and then, one may wish
to proceed further) :-

http://j-walk.com/ss/books/index.htm

Please do so today and thereafter, everyday ; any time of the day,
well, almost. That's a pilgimage to the Mecca of Excel (You won't mind,
Mr. Walker ?).

Regards.