Excel Tip #3: Manage Units and Avoid “Magic Numbers”

Share your tips, on Engage!

Do you have some tips of your own about spreadsheets? If you're a member, I'd love you to share them on AIChE Engage.

Chemical engineers work with a wide variety of engineering units in their day-to-day calculations. Because of this, ChEs are practiced at making calculations consistent when it comes to the units of different variables. This raises the issue of how best to manage conversion of units to a consistent basis for formula-based calculations on spreadsheets. As shown in the ideal gas law example in the first post of this series, placing unit labels in adjacent cells is a good practice. And, you should always carry out unit conversions in separate cells on the spreadsheet. Below is an example of this in which a pressure is specified in psi but the internal calculations of the spreadsheet require the use of pascals.

images

Engineers often include unit conversion factors within formulas — this is bad form because it places a number in the formula that may be difficult to understand and interpret. These are sometimes referred to as “magic numbers.” In the spirit of keeping the spreadsheet explicit and easier to analyze and comprehend, avoid putting magic numbers into formulas.

A related tip is to let the spreadsheet carry out the calculations — do not place a number in a cell or formula that is the result of an off-line calculation. For example, it may be tempting to enter one-half a known diameter in a formula that requires a radius. Avoid the temptation and show the divide-by-two calculation explicitly.

More tips and techniques

If you're just joining us, check out the entire series. And if you want a full crash course instead of just helpful tips, you should check out the AIChE Academy's "Spreadsheet Problem-Solving for Chemical Engineers," where these tips come from, and also check out the other Excel courses available through the AIChE Academy at www.aiche.org/academy.

Share your tips, on Engage!

Do you have some tips of your own about spreadsheets? If you're a member, I'd love you to share them on AIChE Engage.

This Excel spreadsheet series is drawn from an article by David Clough that appeared in AIChE's CEP Magazine. You can find the current issue and an extensive archives of back issues at www.aiche.org/cep.

Comments

Rachel Wiggins's picture

I agree! Stating the conversions explicitly also makes the spreadsheet easier to check/verify, or to understand when it is handed over to someone else. Sometimes I use a custom format in a cell, that includes the unit (0.00" lb/ft^3"). What do you think of this practice?