What engineering/math features is Excel missing?

[Gareth Hayter]

Hi All

Background
I'm nearing the end of development of an Excel add-in (commercial) which adds new engineering and math features to Excel. I'd like to have a discussion with engineers that use Excel on a daily basis to ensure that the product adds true value and meets their needs and desires by quickly incorporating their feedback and suggestions into the product and essentially giving them the chance to direct the development of the product.

My question is: Where is the correct place/forum to have this type of discussion as I don't want to break any forum rules? (I'm trying to be absolutely clear about my intentions)

I won't mention the product/website/features until I have been permitted, as I don't want to be accused of spamming or promoting a commercial product.

Thanks for your time and help,
Gareth Hayter.

 

[JohnRBaker]

How about Roark's Formulas?

John R. Baker, P.E. (ret)
EX-Product 'Evangelist'
Irvine, CA
Siemens PLM:
UG/NX Museum:

The secret of life is not finding someone to live with
It's finding someone you can't live without

 

[Gareth Hayter]

Hi John

Thanks for the suggestion. Isn't there already a product for this? [URL unfurl="true"]http://www.roarksformulas.com[/url]

I'll await a moderator's reply to my initial question before sharing any details about my product and if/how UDF's fit into the current vision.

Many thanks,
Gareth.

 

[fel3]

Gareth…

In my opinion, the two most important things Excel lacks for engineering and science calculations are [1] units and [2] visible and properly formatted equations.

One of the primary reasons I use Mathcad and SMath Studio for many of my engineering calculations is that they handle units beautifully (for the most part) and the equations are, by default, visible and formatted like equations. Also, even though my favorite calculator is the HP-42S (and its super-clone, the SwissMicros DM42), I often use my HP-48G+ or HP-50G for "quick and dirty" engineering calculations because they handle units.

(This is not to say that I don't use Excel for engineering calculations, because I do. It's just a lot more work to troubleshoot and annotate an Excel calculation than a Mathcad or SMath calculation. For my type of work, the only thing Excel does better than Mathcad or SMath--that I can think of at the moment--is handle large tables of numbers.)

In engineering and science, excepting certain constants, numbers without units are usually useless. Programs and devices that handle units virtually eliminate conversion errors and give the final results actual meaning. Even empirical equations benefit from using units (these generally require some weird combination of units attached to an equation constant to make it work, but it's worth the effort to figure that part out).

Having visible and properly formatted equations makes following a calculation procedure pretty easy. Following someone else's calculation procedure in Excel is typically impossible for all but the simplest calculations (like cost estimates), and it's even difficult for your own spreadsheets if you haven't touched them in a while. Excel can show equations, but not in a way that makes them easily understood or the calculation procedure easy to follow.

As a civil engineer, I often need to submit calculations to a reviewing agency as part of the approval process. With Mathcad and SMath, the calculations are easy to follow. With Excel, the calculations are invisible for the most part. In fact, with just a printout from Excel, it's virtually impossible to tell if Excel was actually used to make the calculations.

One thing that most, if not all, programs lack that I would like to see in Excel is the ability to stretch/shrink chart axes so that a paper copy is easy to read with an engineering scale. Even in our digital age, this is useful. Another problem with Excel graphs is that log axes cannot be "trimmed" to a non-power-of-ten number. Graph Expert Pro allows this. It's a pain in Excel when I really want to start a log axis at 8, let's say, but I am stuck with starting it at 1 or losing some of the graph if I start at 10. I also wish the trendline equations that I generate in a chart were live so that I could directly reference them in the spreadsheet. I can copy them, but if I change something "upstream" of the chart, it won't update the calculations based on the trendline.

As far as math ability, I can't think of anything I need that Excel lacks that I can't create an equation for. I sure some engineers and scientists are missing something important for their work, but as far as math, I think Excel does fine.

Fred

==========
"Is it the only lesson of history that mankind is unteachable?"
--Winston S. Churchill

 

[Gareth Hayter]

Hi Fred

Thank-you for your insights.

As no moderator has chimed in yet (I will defer to whatever they decide), I'll talk about the product because what you've touched on is very much related to the product road-map, and it's easier talking in specifics. The product is working and downloadable but I won't yet give the name or download link.

My assumptions about the two top features were/are exactly what you've referred to:

1) Units
2) Math equations

Units
Here's what I've planned regarding units. Are these features or some variation of them useful or not? (These features have been prototyped and work correctly, but not yet added to the product)
[ol 1]
[li]Check whether the units for all cells in a formula (and all of its precedents) are correct, highlighting cells that are either missing units or whose specified units are incorrect.[/li]
[li]Auto-apply units to cells with missing unit specifications, based on precedent cells and formulas.[/li]
[li]Each of the above use-cases can be triggered by right-clicking a cell(s) and then clicking 'Check units' or something similar to run the above logic. Alternatively, the ability to generate a report of an entire workbook, reporting which cells are missing determinable units or have incorrect units.[/li]
[li]Of course, unit-checking/applying could be performed automatically when a cell/formula is edited so that you're alerted about incorrect units in realtime.[/li]
[/ol]

Math equations
Most of these features have already been implemented:
[ol 1]
[li]View any formula as a real math equation (with many options such as displaying the text in the cell to the left of any reference instead of the address).[/li]
[li]View any formula as a plot. If the formula has many references then you can select which one to use as the y-axis.[/li]
[li]Right-click transform selected cell(s) to replace Greek letter names in text with symbols, replace _ and ^ with subscripts and superscripts etc.[/li]
[li]Apply units as formatting inside cells. Define custom units.[/li]
[/ol]

Calculation sheets/documents
To aid creating calculation docs, the add-in enables you to insert data into Word (cell values, descriptions, units as well as equation objects and plots (inserting plots not done yet). These can be inserted as single values or as rows in a table. When the underlying cells are changed in Excel, the linked data in Word auto-updates (as long as the Word Writer tool is open). In fact, 'input' values can be modified in Word, which then updates their value in Excel, recalculates then updates all changed data back in Word....essentially turning your Word document into a custom calculator powered by Excel.

Considering the above, are these capabilities useful to someone like yourself? In their current form, or would some variation be required?

Are there any glaring omissions of capabilities?

All the best,
Gareth.

 

[3DDave]

Yeah, support for units would have been what to lead with. Engineers can find formulas or create functions, no sweat. But they can't jam units into Excel.

 

[GregLocock]

Don't worry about moderators, eng-tips has 30000 (or whatever) moderators and a robust method for dealing with naughtiness. You are fine so far.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[Gareth Hayter]

Awesome to hear, thanks for clarifying, Greg!

 

[jhardy1]

WYSIWYG Formatting of expressions - so that all algebraic expression can be reviewed and validated from a printed or PDF copy against the expression in the source document (text book, Code, etc), not just by the originator while working within Excel.

Units-awareness in ALL variables and ALL expressions, not just as a user-option, or by "right-clicking" to optionally apply units.

Until Excel can do both of these things, it will never replace something like Mathcad as my "go to" tool for composing engineering calculation documents - especially where those calculation worksheets are going to be reviewed and / or re-used by others.

http://julianh72.blogspot.com

 

[klaus.]

why not using SMath Studio instead of Excel ???

 

[Denial]

Accessing trendline parameters

As you have commented, this cannot be done from a chart.[ ] However it can be done using the LINEST() function.[ ] This has the further advantage that it gives you the parameters to greater accuracy than you get from the text annotation on the chart.[ ] (This inaccuracy has been reported/queried several times on Eng-Tips.)

 

[IRstuff]

greater accuracy than you get from the text annotation on the chart.

In what sense? The precision of the trend line equation can be set to the same precision found in a worksheet.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Denial]

IRstuff.[ ] See, for example, thread770-202275.[ ] I know there are some other posts that give specific and dramatic examples, but I cannot find them with a quick search.[ ] The problem arises when the DEFAULT formatting of the parameters in the trendline presentation is not up to the job.[ ] (Many users are unaware that this formatting can be changed.)

 

[IRstuff]

OK, seems like two separate problems; the cited thread is talking about trying to fit a polynomial to a set of points. So unless there are way more points than degrees in the polynomial, the curve will be best fitted at the measured points, that's neither an accuracy problem nor a bug; that's basic regression, since the process minimizes the fit error at the measured data points. If you look at strict linear regression, the process takes the differences between the measured data and the predicted data and minimizes the RSS total. If you had only two measurements, the fitted line would pass perfectly through the measured data; likewise, for a n-th order polynomial fit, if there are n+1 or fewer points, the fitted polynomial would pass through the measured data. That's more of a problem of either insufficient data or poor choice in polynomial order.

The fitting equation display precision is a precision problem, not an accuracy problem.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[IDS]

Most of the responses seem to be answering the question "how can I turn Excel into Mathcad or Smath?".

From my point of view, using a spreadsheet as a spreadsheet, I think the main engineering/math features missing or lacking in features are:

Interpolation - both linear and higher order.
Vector and complex number calculations.
Linear algebra; the built in functions are dead slow, and don't have any solve functions.
Polynomial equation solvers.
Numerical integration and differentiation.
The built in solver and goal seek functions are dead slow and clumsy to set up for multiple operations.
High precision maths.
Geometric functions such as line interpolation and rotation in 2D and 3D.
Cross section properties.

The capability to deal with units and assign values to variables and solve functions in text and math format would also be useful.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Gareth Hayter]

Doug, don't worry about the 'how', but would any of your wishes be covered by having access to these functions inside Excel:

https://mathjs.org/docs/reference/functions.html

Kind regards,
Gareth.

 

[IDS]

Gareth - I don't know anything about JavaScript maths capabilities, but I imagine they would cover most of the things on the list, if not all.

Everything can certainly be done with VBA and/or Python, which is what I focus on these days.

The only thing I don't know how to tackle is accessing the symbols in the equation editor so you can use as an interface to a solver function. We had some discussions on this a few years ago, but got distracted by other things.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Gareth Hayter]

Doug - it wouldn't be Javascript - it's just that existing implementations are easier to start from.

Yes, we certainly had a few discussions about related topics. What's changed is that I've forked the original add-in into multiple more well-defined add-ins, and the one I'm referring to here is specifically for engineering and maths, and has some new features in those areas.

I can certainly 'display' an equation, even while you're editing the formula - updating the equation as you type, however at the moment there wouldn't be support for editing the actual 'equation', only the formula - maybe in the future...

 

[fel3]

Denial…

I am familiar with LINEST() and LOGEST(), but I don't see functions to estimate power curves, exponential curves, or polynomial curves. Or, maybe, I'm just blind.

Fred

==========
"Is it the only lesson of history that mankind is unteachable?"
--Winston S. Churchill

 

[fel3]

Gareth…

I think you understand my issues pretty well, and others have added good thoughts too.

I have no desire to turn Excel into Mathcad or vice versa. Each program has its own strengths and their designs make equivalence essentially impossible anyway. No matter what functionality you add to Excel, Mathcad and/or SMath will still be better for formal engineering calculations--it's part of their basic designs. And, no matter what improvements are made to Mathcad and/or SMath, Excel will still be better at certain things--again, because of its design. It's just that for science and engineering calculations in Excel, units and readable equations are an absolute must.

My wish list is more expansive for both programs. For example, I would like the capabilities of CurveExpert Pro and GraphExpert Pro in Ex cel, Mathcad, and SMAth. But, until that happens, I will continue to use CurveExpert Pro and GraphExpert Pro when I need their specialist capabilities.

Fred

==========
"Is it the only lesson of history that mankind is unteachable?"
--Winston S. Churchill