String to formula

joyrock37

New Member
Joined
Oct 14, 2011
Messages
26
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!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi, does your Eval() UDF work if you call it like this instead:

=Eval(SUBSTITUTE(C1;",";"."))
 
Upvote 0
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 !.
 
Upvote 0
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?
 
Upvote 0
The string in A1 as static as it can get :biggrin:

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]
 
Last edited:
Upvote 0
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]



example.JPG
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top