String to formula

[joyrock37]

Hi friends,

I have the following task:

Cell A1 contains the string "4,4*EXP(0,03*".
Cell B1 contains a numeric value which can be changed.
Cell C1 concatenates the two strings: = A1&B1&")" to string that Looks like a formula.

In Cell D1, I would like to get the result of the string in Cell C1.

I prefer a standard Excel-function of some sort, but haven't been able to find anything that worked for me.

So I tried:
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

But the function Returns #Value !

Big Thanks for your help and suggestions!

 

[FormR]

Hi, does your Eval() UDF work if you call it like this instead:

=Eval(SUBSTITUTE(C1;",";"."))

 

[joyrock37]

Hi, thanks for the quick response!
When I use your suggestion on the string 4,4*EXP(0,03*39,5), I'm getting the same result: #Value !.

 

[FormR]

I'm getting the same result: #Value !.

Not sure to honest then, it works for me with UK regional settings.

How static is the string in A1? Can you maybe post a few different examples of the possible strings that can be in that cell?

 

[joyrock37]

The string in A1 as static as it can get

other examples:
4,4*EXP(0,03*39,5)
4,4*EXP(0,03*17,3)
4,4*EXP(0,03*9,8)
So really only the last value in the string is changed.

I found out, the function Eval() works, as long as I don't include any Excel-functions. I replaced the "EXP(0,03*39,5)" with "SUM(3;5)" and still getting the #Value !
With "simple" operations, like "4*5" the function returns the correct result.
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

I replaced the "EXP(0,03*39,5)" with "SUM(3;5)" and still getting the #Value !

I think that Evaluate is expecting things to be in a US/UK format i.e. "SUM(3,5)"

The string in A1 as static as it can get

But if the above is true why not simply use this formula in D1:

=4,4*EXP(0,03*B1)

 

[joyrock37]

Sorry, I wasn't quite exact.
A1 is only static for a specific number of cells. It can also change, but not as often as B1.

Maybe better to explain some more.

I need is to assign an Age to single trees which had their diameter measured.
The table contains tens of thousands of trees.
The formula which calculates the Age is an exponential function with only one variable, the Diameter.
However, to differentiate between tree species and regions they are growing in, two more variables are added:
The region they are growing in (43 different regions) and the species they belong to (13 species) the formula differs (Cell A1).
That makes 559 (43 x 13) Options, which are "static" and stored in a separate table; taken from there via INDEX (Formula Part 1).

Example:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Species[/TD]
[TD]Formula Part 1[/TD]
[TD]Diameter[/TD]
[TD]Complete Formula[/TD]
[TD]Age[/TD]
[/TR]
[TR]
[TD]Mountain Range[/TD]
[TD]Pine[/TD]
[TD]4,4*EXP(0,3*[/TD]
[TD]39,5[/TD]
[TD]4,4*EXP(0,3*39,5)[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Mountain Range[/TD]
[TD]Spruce[/TD]
[TD]3,2*EXP(0,2*[/TD]
[TD]31,9[/TD]
[TD]3,2*EXP(0,2*31,9)[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

That makes 559 (43 x 13) Options, which are "static" and stored in a separate table; taken from there via INDEX (Formula Part 1).

Hi instead of storing part of the formula as text in your separate table why don't you just store the numbers, retrieve them in the same way and reference them in the formula directly?

 

[joyrock37]

Just when u said it....

While you and I were thinking about it, I had the same idea and it works

However, I still wanted to know if Excel is able to Interpret a string of letters and numbers the same way as if I typed an "=" ahead of it.
I guess it can't?

Anyways, it's working, and I am happy you helped so quickly!

Thanks a lot!

 

[FormR]

However, I still wanted to know if Excel is able to Interpret a string of letters and numbers the same way as if I typed an "=" ahead of it.

Using a UDF similar to what you found is one way of doing that, I'm not sure why it's not working for you, but I think it might have something to do with your regional settings.

There is another way I think that uses the old XLM Macros and named ranges - if you Google "XLM 4 Macro + Evaluate" you might come across it and you might have better luck with it.