Formulas and functions

[Nick_Dodd]

Hi     Is there a way to calculate a value displayed in one cell in another cell    ie: if I had a value of 3/10 expressed as text in one cell, is there a way to calculate this value as a decimal in an adjacent cell ie: 0.3?

Thanks

Nick Dodd

[#2]

@Nick_Dodd 

If you're willing to use VBA:

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following function into the code window:

Function Eval(s As String)
    Eval = Evaluate(s)
End Function

Close the Visual Basic Editor.

Use like this in a cell formula to evaluate the expression in A2. The formula can be anywhere.

=Eval(A2)

Save the workbook as a macro-enabled workbook (.xlsm) and make sure that you allow macros when you open it.

Alternatively, you can use a defined name; this will only work in the cell to the right of the text string.

So let's say you have a text expression in A2.

Select B2.

On the Formulas tab of the ribbon, click Define Name...

Enter Eval in the Name box, and enter the formula =EVALUATE(A2) in the Refers To box, then click OK.

Enter the following formula in B2:

=Eval

Note that there is no reference to A2 in the cell formula; the reference is in the definition of Eval.

If you enter =Eval in cell D6, it will refer to C6, etc.

[#3]

@Nick_Dodd 

Your example, Nick, is VERY specific. So I'm going to assume that you're wanting this to work with what we might call traditional fractional notation. IF that's the case, then here's a formula that doesn't require VBA to work. 

=VALUE(LEFT(A1,FIND("/",A1)-1))/VALUE(RIGHT(A1,LEN(A1)-FIND("/",A1)))

This finds the value of whatever text in cell A1 precedes the "/" character, and divides that by the value of whatever text follows the "/" character. It uses the text manipulation functions LEFT and RIGHT to determine the relevant text, FIND and LEN to count the characters as needed, and then VALUE to convert text to value.

As this image shows, because it's dependent on the location of the "/" character, it will work with more complex fractions as well.

But if you want a function to work with other kinds of numbers in text, then let me invite you to be a bit more expansive on your description of what you're seeking to accomplish.

[#4]

Thanks for your response. I'm not able to try it this week as I am on holiday, but will give it a whirl next week.
Yours
Nick Dodd

[#5]

@Nick_Dodd 

This is funny and crazy too.
Using FILTERXML() function you will return a matrix containing the numbers

=FILTERXML("<x><y>"&SUBSTITUTE(K4,"/","</y><y>")&"</y></x>","//y")

Then you just need to get the first element and divide by the last element
INDEX(PreviousFormula,1)/INDEX(PreviousFormula,2)
As it envolves a lot of effort, would be a good idea you use a more modern method like LET() function, so you can create a more elaborate example.

[#6]

@Nick_Dodd 

This is very much 'for interest' at the moment because it relies upon functionality currently only available under Microsoft 365 insider beta channel.  The formula is an alternative form of that presented by @Hans Vogelaar .

I created a defined name EVALUATEλ (I use the Greek letter λ from Insert/Symbol merely as a reminder as to the nature of the formula the name refers to) defined by

= LAMBDA(expr, EVALUATE(expr))

The worksheet formula is then

= EVALUATEλ(expression)

where 'expression' is currently an array of fractions as text but could be any other formula in text form, such as 'EXP(1)' which would evaluate to the math constant e.

Note: EVALUATE is an old XLM macro command and there is no assurance that it will be supported intefinitely.

[#7]

To make it dynamic considering the values are on left column

=EVALUATE(INDIRECT(ADDRESS(ROW(),COLUMN()-1)))

 Find attachment