Calculating formulas to make an IF function work correctly.

[pdowney]

Setting up a file to track hours and provide estimates I need for holding back funds.

Column C is number of hours. Entries will be in quarter our increments.

Column E multiples the value in C * cell B15. (Gross)

Columns G-Q breakdown amounts to be held out based on a per hour cost. So, the values are determined by C* $B$1-10 and 12

Column R adds cells G-Q (which should equal E)

Column S is an IF statement: =IF(R20=E20,"YES","NO")

When I started entering hours, the first two days were fine (lines 18, 19)

But the third day when I entered the hours (6.5) on line 20, it through Column S to a NO, even though E and R show the same amount.

I started double checking everything, can find nothing.  I went in and added number 1-24 in .1 increments, and it shows 1- 2.2 is okay 2.3 and then 2.5-6.8 are bad. Then 6.9 up to 18.8 are good. After that, it is hit or miss up to 24.

Any ideas on what may be causing this and what the fix is?

[#2 · Accepted solution]

@pdowney 

That is so called floating point error. You may change your check sum formula on something like

=IF(ROUND(R20-E20,5)=0,"YES","NO")

[#3]

@Sergei Baklan Thank you. I had looked at rounding, but only from the aspect of applying internal to each calculation. This is much more efficient.  I appreciate the help.

[#4]

@pdowney 

Here are bit more details about an issue

Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs

Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” - Micr...