The Importance of Units in Excel and Spreadsheet Calculations
Excel, a widely used spreadsheet program, has no inherent feature to enforce units for cell values. This can lead to errors, especially when performing operations involving different types of measurements such as feet and gallons. Let's explore why this lack of unit support exists and the importance of incorporating units in your spreadsheets.
Current Limitations in Excel
Excel does not natively support units for cell values. For instance, you can add a cell containing the number of feet to a cell containing the number of gallons without any error or warning. While modern versions of Excel offer data types like Stocks and Geography, which can store objects and their properties, they still do not enforce units-based operations. MathCAD and TK Solver, on the other hand, do assign engineering/scientific units to values, and they will raise an error if you try to perform operations with incompatible units. However, they allow the addition of different units (like meters to feet) to display an answer in a different unit (like furlongs).
The Blame Game
The responsibility for this feature lies with American school textbooks. Students are taught to solve problems by extracting numbers from a problem and forming an equation, but they are rarely taught to include units. For example, if John has 5 apples and gives 2 to Sally, a typical problem might be solved as:
X 5 - 2 3
However, the equation should be written with units:
X 5 Apples - 2 Apples 3 Apples
This early exposure to writing equations with units can help prevent unit mismatches in more complex calculations. For instance, the equation X 5 Feet - 2 Gallons would immediately show an error, while X 5 Feet per Second * 2 Hours would be instantly clear to anyone working with the problem.
Chemistry as an Exception
High school chemistry requires a more rigorous approach to units. Students must write all units and perform unit conversions. For example, in the equation g 2 Moles * CO2, it is clear that the units do not match, and the equation would need to be adjusted to ensure consistency. This rigorous approach helps students develop a habit of including units in their calculations, which can prevent many common errors.
A Working Engineer's Perspective
As a working engineer, I always include units in my work. Whether it's in handwritten equations, computer variable names, or in Excel, I ensure that units are included for clarity and consistency. In Excel, I often write units to the right of the numbers and define global constants for unit conversions. This makes my equations readable and easy to understand, both for myself and for others who may need to work with my data in the future.
While the inclusion of units in data types can enforce consistency, it often causes pain when performing appropriate unit conversions, such as the equation F m * a. The software has to know that F (Newtons) is the product of m (kg) and a (m/s^2). Although this is possible, the complexity often leads to errors and requires significant pain to implement in practice.
In conclusion, ensuring unit consistency in spreadsheets is crucial to prevent errors. While Excel currently does not support units natively, adopting the practice ofincluding units can greatly enhance the accuracy and readability of your calculations. Future versions of Excel may include unit parameters, but until then, teaching and emphasizing the importance of units in all calculations is key.