Issues with FLOOR.MATH

**enough** · 04-24-2017, 03:32 PM

Hi Everyone,

I can't figure out why my formula =FLOOR.MATH(I14, ".25") is resulting in 1.75 when the number it is looking at is 2.0.

Some more information in case it helps. I have two columns with time in them and I am trying to calculate/round the time difference down to the nearest quarter. Once I have the difference I then used the formula above. What is odd is that the formula results in 2.0 when it looked at the calculated number of 15:00-17:00 but then doesn't when I used the same formula on 14:00-16:00. Any ideas? or any suggestions on another formula?

Thanks in advance!

**MrShorty** · 04-24-2017, 04:01 PM

Difficult to say without seeing the actual numbers, but I would guess that it is routine double precision round off error. If you format your differences (before flooring them) so that you can see all 15 digits, I expect you will find that some of those that are 2.0 to the nearest tenth are 1.9999999..... (something ever so slightly smaller than 2), which naturally floors to 1.75.

My first suggestion for fixing this is to include a ROUND() function on the intermediate difference before you FLOOR() it. You may need to research to know what precision you need for this intermediate round, but I would expect something like ROUND(difference,7) would be a good first guess. The FLOOR() the rounded result.