I have an Excel sheet with a basic layout as follows :
| Setting | Value | | x + 10 |.. .. | | x + 20 |.. .. | | x + 35 |.. .. |
Value cell for each row is calculated based on
x + 10, etc. the cells in the
Settings column are formatted as strings as they are meant to provide a visual representation of the setting value.is there a simple way to convert
x + 10 from a string to a numeric value given the value of
x is available in a cell somewhere in the sheet?
I've looked at several similar questions, such as Excel convert string equation to mathematical function, but the questions themselves all involve more complexity than what i am doing, and i am looking for a simple solution that doesn't involve VBA.
Answer 1 :
There is a built in old VBA function called evaluate which will take an exact string that is an acceptable excel math formula and convert it to numbers and operators and solve.in your case you have two steps you are looking at.one is to perform the substitution for X and the second is to solve the resulting equation.
This solution will generate a helper column just for illustrating the steps.the final cell does not need the helper column as long as the formulas from the various steps get properly nested within subsequent formula.PART 1 - SUBSTITUTION
Look at the substitution formula.this formula will look for a given string within another cell and replace it with another value.
SUBSTITUTE(string of text, text you are looking for, text you want to change to, optional occurrence to change)
In a temporary helper column use the following formula and copy down.Lets assume your value for X is in the cell Q4.and the first formula you are dealing with is in B2.
So if X is 10, you should wind up with a column that looks like :
PART 2 - Evaluate
| Setting |helper | Value | | x + 10 |10 + 10|.. .. | | x + 20 |10 + 20|.. .. | | x + 35 |10 + 35|.. .. |
couple of critical steps here.Select the cell to the right of 10 +10.the reason being is that when we make this formula we want it to deal with cells relative to the position of the cell we create the formula in.the next thing to be done is create a named formula.to do this go to the FORMULAS tab on the ribbon.on the Formulas ribbon, in the defined names section, select Define Name.in the name box, give it a name of your choosing.i tend to like ANSWER.what ever you choose will be how you will be referring to it in your cell like any other function.in the refers to box enter the following equation :
Its important that you leave the $ off the cell reference.C3 in this case is refering to the cell with 10 +10.Click ok when done. in that cell which you selected at the start enter
Where ANSWER is the name of your formula.it should then grab the cell to the left and evaluate it and give you an answer of 20.when you copy the cell down, it will refer to the next formula down and so on as far as you copy.
Now to do this without the helper column, you need to define your formula with the substitute formula in it so your named formula will look something like :
Now the trick is you will need to create this for each worksheet you want to use it on and your cant repeat the name of your formula.i have not found a work around to this catch yet.
Answer 2 :
You can do this using text editors, actually--as long as it is definite that the variable in your text-as-formulae are mapped to certain worksheet cells.
First copy the text-as-formulae to a text editor, e.g. Notepad++ (even Word or Windows Notepad may work for simple cases).
Then replace your variables with cell references, e.g. replace x → A1.
Now switch to Formula View in Excel, if you use Excel 2010 this can be done by clicking formulas--(formula auditing button group)--show formulas.
Forth, copy the new formulae from text editor back to Excel.
Finally, click"show formulas"again to quit Formula View.Voila!