This is unexpected

[Dr. Harvey Waxman]

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.


Thanks

 

[CyberTaz]

One option - You can use Excel>Preferences - Calculation, check the box for
Precision as Displayed & format the cell for 2 decimal places of accuracy.
Just keep in mind that the Precision setting is a Workbook option, not per
sheet or per cell. From Excel Help;

Precision as displayed Permanently changes stored values in cells from full
precision (15 digits) to whatever format, including decimal places, is
displayed.

 

[Harvey Waxman]

Precision as displayed Permanently changes stored values in cells from full
precision (15 digits) to whatever format, including decimal places, is
displayed.

Dang it. I didn't notice that option.

Many thanks

 

[JE McGimpsey]

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 worksheet 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")

 

[Dr. Harvey Waxman]

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 worksheet 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")

Of course. An excellent suggestion.

thanks