• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Excel Course
  • VBA Course
  • Free Training
  • Login

Unit Aware Calculations in Excel

Every engineer knows that for the results of engineering calculations to be correct, the units of the input values must be correct. In the case of a calculation like F=ma, the units must be consistent with each other (e.g. N, kg, m/s^2) and in the case of a unit-specific calculation like P=Tn/5252, the equation requires values in particular units (e.g. hp, lbf-ft, RPM).

A tool like Mathcad is “unit aware” because it allows you to enter units along with a value and take these units into account in subsequent calculations.

But you may be wondering, is there a way to make Excel “unit aware”?

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

What are “Unit Aware” Calculations?

Unit aware means that if you tell Excel the units on your input values, it will perform the calculations and provide the correct result by performing the appropriate unit conversions.

It turns out that there is a relatively straightforward way to do this in Excel by using a combination of two built-in capabilities:

  1. The “CONVERT” function
  2. Data Validation

Excel CONVERT Function

In my opinion, the CONVERT function is one of the best kept Excel secrets for engineers.

The syntax for this handy function is

CONVERT(number,from_unit,to_unit)

where

number is the value to be converted

from_unit is a text string containing the unit to be converted FROM (or the unit of number)

to_unit is a text string containing the unit to be converted TO

How the CONVERT Function Works

The convert function simply multiplies number by a conversion factor that it looks up from an internal table. Therefore, one caveat to using the CONVERT function is that both from_unit and to_unit must be recognized by Excel. Otherwise, Excel returns an #N/A error.

CONVERT recognizes many units of weight and mass, distance, time, pressure, force, energy, power, magnetism, temperature, volume, area, information, and speed. For metric (SI) units, it also recognizes every prefix from yotta to yocto (I didn’t even know those were things :)) and everything in between.

Microsoft seems to add more units to CONVERT with each Excel release and they keep an excellent list of all the supported units on their site.

CONVERT Function Examples

There are two common ways to use CONVERT. The first way is to type from_unit and to_unit directly into the function. Keep in mind that these are text strings, so they must be enclosed in double quotes (“) or else you will receive a #VALUE! error.

=CONVERT(10,"m","in") >> returns 393.7
=CONVERT(2 "gal","l") >> returns 7.57

The second way that CONVERT can be used is by entering the from_unit and to_unit in cells on the worksheet. (With this method, no double quotes are required because Excel automatically recognizes text in cells as a string.)

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

This is really all you need to do to make Excel calculations “unit aware”

However, we can take it one step further by combining the ability to determine from_unit and to_unit from cells and use Data Validation to control what units are selected.

Data Validation

Data Validation is a tool that allows a spreadsheet author to control the values that are entered in a cell. You’ve probably encountered it before if you have ever seen a drop-down menu in a cell.

With the cell containing the from_unit selected, go to Data > Data Validation (in the Data Tools section)

There are several different validation criteria to choose from, but we will choose to allow only values from a pre-specified list.

The “Source” field contains the allowable values. These can be selected from a range in the workbook or simply entered as a list of comma-separated values as shown below:

If only recognized units are included, the conversion is sure to work without error.

Going back to the spreadsheet, we can select a new from_unit in cell C2, and the result in B3 will reflect the change in units.

Unit Aware Calculations in Excel: Examples

Of course, you can convert many other units as well, like area. In the following example, the spreadsheet has been set up to accept area in many different units, and convert to square meters for subsequent calculations:

It’s also possible to convert complex units by multiplying or dividing simple unit conversions:

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Primary Sidebar

Featured Posts

Basic Lookups in Excel with VLOOKUP and HLOOKUP

Advanced Lookups for Engineering

How to Quickly Create an XY Chart in Excel

Custom Excel Functions for Engineering Constants

Linear Interpolation in Excel

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in