How to evaluate a string as a formula?

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!

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

On 14 Mar 2006 18:44:12 -0800, "zhaoning.zju@gmail.com"
<zhaoning.zju@gmail.com> wrote:

>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

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

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
--
FindeO


"zhaoning.zju@gmail.com" escribió:

> 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!
>
>

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?

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




<zhaoning.zju@gmail.com> wrote in message
news:1142825020.680100.65920@t31g2000cwb.googlegroups.com...
> 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?
>

On 19 Mar 2006 19:23:40 -0800, "zhaoning.zju@gmail.com"
<zhaoning.zju@gmail.com> wrote:

>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

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.

Ron Rosenfeld wrote:
> On 19 Mar 2006 19:23:40 -0800, "zhaoning.zju@gmail.com"
> <zhaoning.zju@gmail.com> wrote:
>
> >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

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

Regards.