Do you mean to substitute cell names with the actual numbers? I bet it is possible to write a vba function to convert any formula, but for many tasks you can simply modify original formula in the following way: For example,
=A1^2+B1*C1 can be displayed as:
=A1&"^2+"&B1&"*"&C1&"="
Just to Clarify...I was thinking of a way of taking the excel equation and displaying it as it would be written by hand. More like formulae written in MathCad, or how formulae are written in text books.
I'm sure I could write a vba module to do it, but I might be duplicating something that's already been done.
It would be useful for checking the more lengthy eng formulae.
There is a macro written which works miracles.Its an alternative to concatenate and actually changes the formula values when the input is changed.
I have just seen that one at a consultants office.
This macro is fantastic especially for cross checking purposes.
Sounds promising. Is it possible to get a copy? Perhaps you could post the vba macro code...I was about start writing something myself but got side tracked with work!
You can name your cells (Voltage1, R2, Mass1,ComputedAcceleration etc). I usually do it by putting the name immediately to the left of the cell and typing Insert-Name-Create command.
If you line up all your inputs in a column and put all the labels in the column to the left, you can define all those names in one command (select the entire block in two columns).
Then when you enter a formula you can either type the name of the variable OR point to the cell as usual.
Either way when you use Options|View|Formula you will see the formula in terms of the cell names.
Now my question, is there any convenient way to get a reasonable depiction of the formula AND the result? The only way I know is two separate printouts... one with formula's and one with results. I'd like to see 'em both on my screen at the same time.
If you're really meticulous about using named ranges, the cheesiest thing is to copy the calculation cells into another range and replace the "=" with "'=", which will turn the second set of calculations into text cells, thereby showing the formulas.
Actually, it will still work if there are absolute references; it's just that you might need to fix some of the references. If you're using "$" references, then there should be nothing to fix.
The opposite thing will also work and might be a little bit simpler. Copy the calculation cells and Paste Special as Values and then set display option to formulas. This has the advantage of not needing to do anything other than change the display option, but obviously results in "dead" results that won't update with new inputs.
Thanks IRStuff. I didn't think of that, but the paste-special-values will do just what I want.
I think that the next time I will create three columns. Column 1 has all labels. Column 2 has all variables (either input or computed). Column 3 has that set of dead values. Periodically for an update I just have to repeat the cut and paste-special-values from entire column 2 into column 3.
Correction to my first post on this thread. Should be
"Either way when you use Options|View|Formula you will see the formula in terms of your cell labels (variable names)"
Had a quick look and the Mathlook for Excel is exactly what I was after. Don't know about spending >2,000 US$ for what's already in most of the text books, but mathlook does seem a good buy for checking and scripting purposes...
You can create a formula which will display the formula in another cell. The format is identical to Options|View|Formulas, except by using this formula, you can now mix and match formula's and results on the same page (with live update, no copying to update).
Here's how you do it:
Type Alt+F11
Select Insert - Module
Use ctrl-c and ctrl-v to cut-paste the following text into the module:
Function GetFormula(Cell)
GetFormula = Cell.Formula
End Function
Then select File - Close and Return
Now use the formula GetFormula(cellreferece) to display a formula. Or if you have formula's in a column copy the GeFormula(cell-to-the-left) down the adjacent column.
