×
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.

# Spreadsheet program for engineering units?5

 Forum Search FAQs Links MVPs

## Spreadsheet program for engineering units?

(OP)
Can anyone recommend a Windows spreadsheet program that understands engineering units?

E.g. will convert values automatically between units I have specified, and automatically display the units of the results of expressions of values in units e.g. energy (J) / mass (g) / temperature (J) -> specific heat (J/g/K).

Thanks.

### RE: Spreadsheet program for engineering units?

Yeah, it is called MathCAD.

David Simpson, PE
MuleShoe Engineering

"Belief" is the acceptance of an hypotheses in the absence of data.
"Prejudice" is having an opinion not supported by the preponderance of the data.
"Knowledge" is only found through the accumulation and analysis of data.

### RE: Spreadsheet program for engineering units?

I've heard good things about:

Uconeer

Good luck,
Latexman

### RE: Spreadsheet program for engineering units?

(OP)
Thanks all, but I do want a spreadsheet program.

### RE: Spreadsheet program for engineering units?

Sounds like an opportunity to write your own and sell it!

Good on ya,

Goober Dave

Haven't see the forum policies? Do so now: Forum Policies

### RE: Spreadsheet program for engineering units?

I use Mathcad and have used Uconeer in the past. BTW, Uconeer was written by a member on this site. Since Mathcad is a bit pricy, an alternative is SMarh Studio, which is similar in concept to Mathcad, but is freeware: http://en.smath.info/forum/yaf_postst1381_SMath-St...

Alternately, TK Solver can handle units and has an Excel add-on that might do what you want.

Speaking of which, if you give more context and more information, it's more likely that someone will be able to come up with a solution. Withholding usage scenarios only makes it harder to find a solution.

### RE: Spreadsheet program for engineering units?

Excel has the CONVERT() function.

### RE: Spreadsheet program for engineering units?

I second IRStuff's mention of smath - a great free tool imo. (I do realize it is not what you requested).

=====================================
(2B)+(2B)' ?

### RE: Spreadsheet program for engineering units?

#### Quote:

Excel has the CONVERT() function.

... which looks like it was written by the work-experience kid over a coffee break.

I've had a convert UDF on my to-do list for quite some time, and it's still there. I may lift it up the list a bit (but please don't anyone hold there breath waiting).

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

#### Quote:

Excel has the CONVERT() function.

... which looks like it was written by the work-experience kid over a coffee break.

So buy the kid a bagel and tell him to get back to work on it.

### RE: Spreadsheet program for engineering units?

To be fair to the work-experience kid, looking up the documentation for the CONVERT() function it does look like it's less useless than I thought it was. For one thing it looks like it recognises metric prefixes, which I had an idea it didn't.

More details here:

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

In Excel 2010 you now get a drop-down list of available unit abbreviations when you use the CONVERT() function. Users of earlier versions may find the link below useful, which provides an add-in that does the same thing.

http://blog.contextures.com/archives/2011/07/13/ex...

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

sMathStudio is a free Mathcad (almost) workalike... it's great for the price and does units fairly well.

Dik

One more:

### RE: Spreadsheet program for engineering units?

I've used ChangeUnits Add-in for a few years. Seems to work OK.

K. Stifle, P.E.

### RE: Spreadsheet program for engineering units?

2
I have just posted my own unit conversion spreadsheet on my blog:

#### Quote:

Looking for a spreadsheet or add-in that would provide better functionality my requirements are:
■Provision of User Defined Functions (UDF’s) for unit conversion, so that the conversion may be easily applied to tabular data anywhere in any spreadsheet.
■A wide range of output units, including all “customary units” used for engineering applications in the USA.
■Editable unit data tables so that new units may be added or corrected.
■Output of converted data in array form, so that the data may be easily used in other UDF’s.
■Preferably free and open source.

None of the existing unit conversion spreadsheets that I’m aware of meet all these requirements (most don’t even come close), so I decided to write my own.

The download file provides open source UDF's to convert to and from SI and a list of 344 non-SI units (taken from Wikipedia).
It's version 0.0, so no doubt there are some errors, and things that could be improved.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

chrisjj,

On the last page of any engineering spreadsheets I do, I set up the last worksheet for engineering conversions and other stuff.

#### CODE

.   A        B         C
1   in       =.0254    m
2   ft       =12*in    m
3   lb       =4.45     N
4   furlong  =660*ft   m 

Column B shows the formula, not the values. I name the cells in column B, in this case, "in", "ft", "lb" and "furlong" respectively. Column C is just for information

Anywhere else in my spreadsheet, when I enter a value in units like lb.in, I go =210*lb*in, and it comes out in N.m.

For something like pressure, I would go 14.7*lb/in^2, and it comes out in N/m^2 (Pa).

--
JHG

### RE: Spreadsheet program for engineering units?

(OP)
Thanks JHG. That's neat. Sounds like you're another user that would like the program I'm looking for! :)

### RE: Spreadsheet program for engineering units?

chrisjj - what features are you looking for that my spreadsheet doesn't have?

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

(OP)
E.g. (from my OP) "automatically display the units of the results of expressions of values in units". Hence that I asked for a spreadsheet program rather than a spreadsheet document.

Your spreadsheet document looks like probably the best solution that can be delivered in a spreadsheet document - well done!

### RE: Spreadsheet program for engineering units?

#### Quote:

E.g. (from my OP) "automatically display the units of the results of expressions of values in units".

OK, I can add that. It will look something like:

If you have:
Row 1: a 100 kN
Row 2: b 10 m
Row 3: = a * b (text)

Then =EvalU(A3, A1:C2) will return 1000 kN.m

Or you could use =EvalU("= a * b", A1:C2) with the same result.

Anything else you would like?

I'm not exactly clear what you mean by a "spreadsheet program" as opposed to a "spreadsheet document".

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

#### Quote (chrisjj)

Thanks JHG. That's neat. Sounds like you're another user that would like the program I'm looking for! :)

I have the program. It is a spreadsheet.

I can enter my values and the units, and have everything convert to my standard units. Since Excel and Libre (Open) Office allow you to name spreadsheet cells, you have no problems writing out expressions in a format you can understand.

--
JHG

### RE: Spreadsheet program for engineering units?

(OP)
> I'm not exactly clear what you mean by a "spreadsheet program" as opposed to a "spreadsheet document".

A spreadsheet program is e.g. Excel. a spreadsheet document is e.g. an XLS. A Windows program can do more than can a spreadsheet document.

### RE: Spreadsheet program for engineering units?

(OP)
> I have the program. It is a spreadsheet.

The program I described does this:

#### Quote (Chris)

automatically display the units of the results of expressions of values
in units e.g. energy (J) / mass (g) / temperature (J) -> specific heat (J/g/K).

Your spreadsheet, clever though it is, does not do that.

### RE: Spreadsheet program for engineering units?

"A spreadsheet program is e.g. Excel. a spreadsheet document is e.g. an XLS. A Windows program can do more than can a spreadsheet document."

Again, some confusion in terminology. While Excel is a program, that does not mean that a spreadsheet does not contain a program; to wit, any VBA content means that a spreadsheet document can run a VBA program, and so it's more than just a document, and would be properly described as a "spreadsheet program."

If you mean a Windows program that reads and executes spreadsheets, then you are out of luck; you're back to SMath and Mathcad. TK!Solver is probably the closest thing to what you apparently want:
http://www.uts.com/ItemDetails.asp?ItemID=0100-50-...

### RE: Spreadsheet program for engineering units?

(OP)
> TK!Solver is probably the closest thing to what you apparently want:

Yet does not meet the requirement: a spreadsheet program.

> you are out of luck

So it seems! Thanks, all.

### RE: Spreadsheet program for engineering units?

I would argue, however, that TK Solver IS A spreadsheet program, since its inputs are all entered in a table format. It may not be what you want, but it is a form of a spreadsheet program, since everything is entered into tabular sheets.

### RE: Spreadsheet program for engineering units?

(OP)

#### Quote (IRstuff)

I would argue, however, that TK Solver IS A spreadsheet program

Here's an interesting article:

http://www.uts.us.com/resourcecenter/customersucce...
“The important thing is that TK behaves for me exactly the way a spreadsheet doesn’t.”

### RE: Spreadsheet program for engineering units?

chrisjj - so what other features are you looking for?

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

(OP)
Just the basic spreadsheet program features e.g. of Excel, plus the units as described.

### RE: Spreadsheet program for engineering units?

#### Quote:

Just the basic spreadsheet program features e.g. of Excel, plus the units as described.

OK, the feature you wanted is a little harder than I first thought, but it should be doable.

But I must say that converting values to/from one specified system of units from/to another specified system of units (or base SI units) seems to me like the basic functionality needed in a "units aware" spreadsheet, and that's what my spreadsheet does.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

(OP)
> OK, the feature you wanted is a little harder than I first thought, but it should be doable.

That would be great.

> But I must say that converting values to/from one specified system of units from/to another
> specified system of units (or base SI units) seems to me like the basic functionality needed
> in a "units aware" spreadsheet

Agreed. I accept I am asking for more than basic.

You asked: Anything else you would like? My answer: no - that's all I am missing.

### RE: Spreadsheet program for engineering units?

#### Quote:

You asked: Anything else you would like? My answer: no - that's all I am missing.

OK, I should have something presentable within a few days (depending on time required for paid work :))

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

I've made a couple of changes to the spreadsheet:
- Since the OP specifically mentioned specific heat, I have added specific heat units (SI only at the moment).
- When no output units are specified the function generates a result value and units. Previously these were discarded if the output units were not recognised, but the value will be valid so I have changed it so that the value and units are returned as long as all the input units are recognised.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

Doug, in your spreadsheet are not included units of Decimal Metric System, anywhere a good work, Felicidades¡¡

### RE: Spreadsheet program for engineering units?

surfer200 - I'm not sure what you mean by the Decimal Metric System, but it should recognise any metric unit with any of the recognised prefixes.

Can you give an example of something that doesn't work?

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

Doug: kg/cm2, ton/m2, kg/m3--- etc

### RE: Spreadsheet program for engineering units?

In the specific heat example, while J and kJ work as an output unit, e, Wh, BTU do not seem to work. It seems to accept BTU as an input unit, and the return unit not specified appears to do the conversion correctly, but when I put BTU.kg-1.K-1 in H53, it claims to not be able find a "To" unit.

### RE: Spreadsheet program for engineering units?

surfer200 & IRstuff - there is a similar problem in both cases with non-SI compound return units. The program can handle input made up of any combination of recognised input units (including kg, kgf, and m with any prefix), but if these combine to make a unit that is not in the list it can't break down the compound non-SI units into their constituent parts. The solution is to add more units to the non-SI list.

For stressess I will certainly add kgf/cm2, kgf/m2, and also kgf.cm and kgf.m for moments. At the moment I don't intend to add ton/m2 and ton.m because of the numerous different "ton" definitions. Note that the "tonne" (1000 kg) is already listed as a unit of mass, but a tonne.m will not be a recognised unit of bending moment, so you won't be able to convert lbf ft to tonne.m, but you will be able to go from lbf ft to kgf.cm.

For specific heat I will add common non-SI units to the list, but they won't include BTU.kg-1.K-1 or other mixed systems (unless there are examples of mixed units in common use).

Regardless of what I add to the list, it is editable, so people can add whatever they like. I'll describe the procedure for doing that in my next blog post on the units topic.

Thanks for the feedback.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

Sure, I was just doing this out of idle curiosity.

My weapon of choice is still Mathcad.

### RE: Spreadsheet program for engineering units?

#### Quote:

My weapon of choice is still Mathcad.

That's OK, I'm not aiming to take over the Mathcad market.

Not just yet anyway. :)

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

While Modelica isn't a spreadsheet program, it does feature exactly the capability described by the OP:

https://www.openmodelica.org/images/docs/Modelica-...

"The acausality makes Modelica library classes more reusable than traditional classes containing assignment
statements where the input-output causality is fixed"

which means that Modelica can solve for any variable defined in a system model.

### RE: Spreadsheet program for engineering units?

Whilst Modelica certainly looks interesting, the linked presentation says it is "a language for modeling of complex cyber physical systems i.e., Modelica is not a tool", so it doesn't seem to be what was asked for in the OP.

I was hoping I might get some feedback on my spreadsheet:
- Does it do what was wanted?
- If not, what is lacking?
- Any other comments?

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

"Modelica," in the abstract, is indeed only the specification for the language. However, there are at least 4 different implementations of Modelica, of which, OpenModelica is an open-source and free implementation. OpenModelica's install file is about 350MB, so not a tool for the faint of heart or short of memory.

### RE: Spreadsheet program for engineering units?

Latest instalment of Units4Excel:

I have added the unit conversion functions to my concrete section analysis spreadsheet to make it "units aware".

http://newtonexcelbach.wordpress.com/2012/08/09/ul...

Also new is provision for the ACI concrete code (ACI318).

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

I have recently posted a new version of my Units4Excel spreadsheet, which you can download from here:
http://newtonexcelbach.wordpress.com/2012/08/28/un...

I have also asked the Wikipedia people if they would like to post a link to it, but they seem a bit skeptical at the moment (uderstandably, I suppose), so if anyone thinks it's the best thing since sliced bread, could you post a comment to that effect here:
http://en.wikipedia.org/wiki/Talk:Conversion_of_un...

If on the other hand you think it's a load of rubbish, please let me know :)

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

I just downloaded the latest version of the spreadsheet and I am having issues with the evalu function. It says that N is not a compatible unit but when I leave off a set unit it gives kg.m.s-2 which = N. I can use the convertA function to then convert the result of evalu which correctly gives N as the units though. I tried with both the spreadsheet and the add-in and had the same issue with both.

Thanks,
Daniel

### RE: Spreadsheet program for engineering units?

#### Quote:

I just downloaded the latest version of the spreadsheet and I am having issues with the evalu function. It says that N is not a compatible unit but when I leave off a set unit it gives kg.m.s-2 which = N. I can use the convertA function to then convert the result of evalu which correctly gives N as the units though. I tried with both the spreadsheet and the add-in and had the same issue with both.

I'll have a look. Could you post the actual example you are having the problem with?

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

I downloaded the updated version and N seems to be working fine now. However, I'm now having an issue with Pa. It is giving the same units not compatible message that N was. I've attached the sheet that I was using (I was testing on the EvalU tab near the bottom). Great work on this spreadsheet though, probably the most useful add-in that I've come across.

Thanks,
Daniel

### RE: Spreadsheet program for engineering units?

Daniel - thanks for the comments and the feedback.

The problem with Pa was I had it defined in the table as m-2.N, rather than N.m-2. I forget why I did that, but I suspect it was a workaround for N not working properly, which stopped working when N was fixed. Anyway, now both N and Pa seem to work OK, both with other SI units and non-SI units.

One thing that still doesn't work as well as I would like is that it doesn't recognise N.m as being equivalent to J. The problem is that the way it is set up at the moment I can't specify N.m as being both a bending moment or torque and energy. I'll see if I can fix that in the next version.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

Rev, nice to see that furlong per fortnight made the list :)

### RE: Spreadsheet program for engineering units?

I downloaded the latest version and am still having the same issue with Pa. It says that Pa is not a compatible unit with kg.m-1.s-2. Could it be that since the calculation doesn't involve N directly that it doesn't recognize that it is also equivalent to N.m-2?

Thanks,
Daniel

### RE: Spreadsheet program for engineering units?

Daniel - Now fixed so that Pa, N.m-2 and kg.m-1.s-2 will all be recognised as the same thing. I have copied the updated .xlsb version to the download site. I'll upload the add-in version in the next few days.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Spreadsheet program for engineering units?

Aibase is also a Windows program which can calculate with SI-unit expressions:
http://www.aibase-cs.com/quantities.html
It's not a true spreadsheet program, because calculations are based on script, and version 3 has no functions to create large tables. It might be of interest though if not an office-like spreadsheet is required, but free-form documents with calculations.

### RE: Spreadsheet program for engineering units?

I would recommend TechCalc100 found on Amazon.
It is a very practical Excel Technical Calculation Tool that requires no iteration, formulation, isolating variables, re-writing equations and replacing unknown variables.

#### 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.

#### Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!

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!