Test for variances and precision in your data with an IF function
—by Frank Kuhn
Microsoft Excel 97/2000/2002/v. X/2003
Microsoft Windows, Macintosh
Suppose you need to check whether two values are close—not necessarily the same—but at least within a degree of variation. Perhaps you can allow some variation due to differences in decimal precision and rounding; or it might be that you’re testing for va-riances and tolerances in your data. For example, you may want to know if your monthly sales are within 10 percent of your goal. Or, you may want Excel to alert you if blood test results show a large deviation from an earlier test, which may signify a health concern. In any event, you’d want Excel to alert you to any wild deviations.
In the article “Crosscheck row and column totals using an error message,” in our February 2005 issue, we discussed how you can use IF functions and an error message to ensure that column and row totals equal each other. You can use a similar technique to check for variations between data, as well as alert you to deviations that exceed the limits you set. In this article, we’ll show you how to use the IF function to do so.
Tracking acceptable deviations in your data
To start you off, we’ll first show you how to use an IF function in conjunction with an error message to alert you to a significant deviation between two sums caused by differences in rounding or decimal precision. Then, we’ll expand this concept by show-ing you how to use the same technique to signal you when data exceeds the limits you define.
Using IF functions and error alerts to check decimal precision
It’s possible for your column and row totals to differ ever so slightly from one another because Excel uses binary operations, which are mathematics based on 0 and 1, instead of using mathematics based on decim-als. You don’t need to know the details of binary op-erations, but you should realize that it may cause subtle variations in your data. This variance isn’t a probl