Hi all,

Im having some issues with the following, which I stumbled across purely by
coincidence. The worksheet was set up by others with more Excel experience
than I so Im not sure as to the best workaround.

FYI, numbers in A1:B3 are entered with only one decimal place. Cells C1:C4
and D4 are calculated. Formulas are noted at the bottom of the post.

The formula in D4 ***appears*** to be incorrect. Since C1:C3 are EXACTLY
-0.5, so is C4. According to the formula, D4 should display €œO.K€�. Instead,
it displays €œToo Low.€�

I believe the problem lies with the way Excel is storing the numbers (at the
binary level). When troubleshooting, I set cells A1:C3 (and C4) to display
with 30 decimals. I then found the calculation for C3 is not EXACTLY -0.5 as
it should be. Instead, there is a 2 in the fifteenth decimal place.

I tried using the ROUND function in C1:C4 but D4 displays €œFALSE.€� D4 still
displays FALSE if I use ROUND in that formula as well.

What am I missing?

Thanks for any and all assistance!

Regards,
Chris



A B C
D
Standard Unit Diff
1 16.3 15.8 -0.5
2 16.2 15.7 -0.5
3 16.1 15.6 -0.5
4 Average Difference -0.5
Too Low

The formula for €œDiff€� in C1:C3 is: =IF(A1="","",B1-A1).
The formula for €œAverage Difference€� in C4 is:
=IF(C1="","",AVERAGE(C1:C3))
The formula for D4 is:
=IF(C4="","",IF(C40.5,"TOO HIGH",IF(C4<-0.5,"TOO LOW",IF(C4-0.5,"O.K."))))