Formula Display in Excel
Formula Display in Excel
(OP)
Does anyone now of any plugin or vba application to display an excel formula in a more traditional long hand notation?
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Contact USThanks. We have received your request and will respond promptly. Come Join Us!Are you an
Engineering professional? Join EngTips Forums!
*EngTips's functionality depends on members receiving email. By joining you are opting in to receive email. Posting Guidelines 

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.
Here's Why Members Love EngTips Forums:
Register now while it's still free!
Already a member? Close this window and log in.
RE: Formula Display in Excel
The only thing that would make it more readable is to use defined names in place of cell addressess:
=Qty*Dissipation
TTFN
RE: Formula Display in Excel
=A1^2+B1*C1 can be displayed as:
=A1&"^2+"&B1&"*"&C1&"="
See the FAQs of this forum for other options.
Hope it helps!
RE: Formula Display in Excel
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.
RE: Formula Display in Excel
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.
RE: Formula Display in Excel
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!
RE: Formula Display in Excel
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 OptionsViewFormula 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.
RE: Formula Display in Excel
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.
TTFN
RE: Formula Display in Excel
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 pastespecialvalues from entire column 2 into column 3.
RE: Formula Display in Excel
"Either way when you use OptionsViewFormula you will see the formula in terms of your cell labels (variable names)"
RE: Formula Display in Excel
RE: Formula Display in Excel
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...
RE: Formula Display in Excel
Here's how you do it:
Type Alt+F11
Select Insert  Module
Use ctrlc and ctrlv to cutpaste 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(celltotheleft) down the adjacent column.
I just learned this from
http://www.mvps.org/dmcritchie/excel/formula.htm
RE: Formula Display in Excel
Once again, it gives you an easy tool to display both the formula and the value. If you change the formula everything updates automatically.
I have provided an example spreadsheet here:
http://www.geocities.com/pschimpf/formula_test.htm
You will have to trust me when I tell you I have inspected the file carefully and it has no harmful virus/macro.
All you have to do to make the function GetFormula available is:
From excel, Open up Visual Basic Editor (Tools/Macro/VBE).
Cut paste the following text into your Module1
Function GetFormula(Cell)
GetFormula = Cell.Formula
End Function
Return to excel (File/CloseandReturntoExcel)
Use the function GetFormula(cellreference) in your spreadsheet
That function will be available to any spreadsheet whenever a spreadsheet containing that definition is open.
RE: Formula Display in Excel
richg1
I found a website where you can download the MathLook For Excel for free fro 10 days after that the cost is only $99.00
www1.uts.com/mathlook