×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Formula Display in Excel2

## Formula Display in Excel

(OP)
Does anyone now of any plug-in or vba application to display an excel formula in a more traditional long hand notation?

### RE: Formula Display in Excel

Not sure what you mean.  When you change the Options|View|Formulas, aren't you seeing the formulas in long hand?

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

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&"="

See the FAQs of this forum for other options.

Hope it helps!

### RE: Formula Display in Excel

(OP)
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.

### RE: Formula Display in Excel

Hi all

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

(OP)
segmental1,

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

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.

### RE: Formula Display in Excel

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.

(OP)
Thanks Yakpol,

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 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. I just learned this from http://www.mvps.org/dmcritchie/excel/formula.htm ### RE: Formula Display in Excel I think that GetFormula is a great tool and very easy. 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/Close-and-Return-to-Excel) Use the function GetFormula(cell-reference) in your spreadsheet That function will be available to any spreadsheet whenever a spreadsheet containing that definition is open. ### RE: Formula Display in Excel Hi 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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

#### Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a partâ€™s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

# Join Eng-Tips® Today!

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 Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!