Re: This is unexpected

Tech-Archive recommends: Fix windows errors by optimizing your registry

In article <info-9C33CD.07383312092006@xxxxxxxxxxxxxxxxxxxx>,
"Dr. Harvey Waxman" <info@xxxxxxxxxxxx> wrote:

In this formula, =IF(I20=14.58,"C","R")

I20 is 14.58 (actually 14.5799999) yet "R" is returned. Clicking the equal
sign when the formula is displayed, the dialogue box says that indeed it is
false. Apparently it's not close enough to 14.58.

I addressed this by doing a find/replace in the column from 799999 to 8 but
Is
it possible to set the precision so this doesn't happen? The 14.579999
resulted from pasting the "values only" of a prior calculation (there are
33,000 rows of data) in an attempt to shorten the painfully long calculation
times.

Setting the displayed precision, except for setting the Precision as
Display preference (Preferences/Calculation...) doesn't affect the value
in the cell that calculations are based on.

I don't recommend the Precision as displayed option since it can yield
some unexpected results unless the workbook calculations are very
carefully designed. For a very simple instance, if decimal places are
set to 2:

A1: 1.00
A2: =A1/3 ===> 0.33
A3: =A1/3 ===> 0.33
A4: =A1/3 ===> 0.33
A5: =SUM(A2:A4)=A1 ===> FALSE

As your work*** calculations get more complex, and especially if some
cells are set to fewer digits than cells that are dependent upon them,
the harder it is to ensure that you don't get inadvertent errors.

A better option, IMO, would be to use the ROUND() function in your IF
statement:

=IF(ROUND(I20,2)=14.58, "C", "R")

or, if your tolerance band is other than a factor of 10,

=IF(ABS(I20 - 14.58) < 0.0333333333333333, "C", "R")
.

Quantcast