Discussion Calculating formulas to make an IF function work correctly. in Excel
https://techcommunity.microsoft.com/t5/excel/calculating-formulas-to-make-an-if-function-work-correctly/m-p/2932031#M121138
<P>Setting up a file to track hours and provide estimates I need for holding back funds.</P><P>Column C is number of hours. Entries will be in quarter our increments.</P><P>Column E multiples the value in C * cell B15. (Gross)</P><P>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</P><P>Column R adds cells G-Q (which should equal E)</P><P>Column S is an IF statement: =IF(R20=E20,"YES","NO")</P><P>When I started entering hours, the first two days were fine (lines 18, 19)</P><P>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.</P><P>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.</P><P>Any ideas on what may be causing this and what the fix is?</P>Fri, 05 Nov 2021 16:43:51 GMTpdowney2021-11-05T16:43:51ZCalculating formulas to make an IF function work correctly.
https://techcommunity.microsoft.com/t5/excel/calculating-formulas-to-make-an-if-function-work-correctly/m-p/2932031#M121138
<P>Setting up a file to track hours and provide estimates I need for holding back funds.</P><P>Column C is number of hours. Entries will be in quarter our increments.</P><P>Column E multiples the value in C * cell B15. (Gross)</P><P>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</P><P>Column R adds cells G-Q (which should equal E)</P><P>Column S is an IF statement: =IF(R20=E20,"YES","NO")</P><P>When I started entering hours, the first two days were fine (lines 18, 19)</P><P>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.</P><P>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.</P><P>Any ideas on what may be causing this and what the fix is?</P>Fri, 05 Nov 2021 16:43:51 GMThttps://techcommunity.microsoft.com/t5/excel/calculating-formulas-to-make-an-if-function-work-correctly/m-p/2932031#M121138pdowney2021-11-05T16:43:51ZRe: Calculating formulas to make an IF function work correctly.
https://techcommunity.microsoft.com/t5/excel/calculating-formulas-to-make-an-if-function-work-correctly/m-p/2932068#M121142
<P><LI-USER uid="1208693"></LI-USER> </P>
<P>That is so called floating point error. You may change your check sum formula on something like</P>
<LI-CODE lang="excel-formula">=IF(ROUND(R20-E20,5)=0,"YES","NO")</LI-CODE>Fri, 05 Nov 2021 16:54:00 GMThttps://techcommunity.microsoft.com/t5/excel/calculating-formulas-to-make-an-if-function-work-correctly/m-p/2932068#M121142Sergei Baklan2021-11-05T16:54:00ZRe: Calculating formulas to make an IF function work correctly.
https://techcommunity.microsoft.com/t5/excel/calculating-formulas-to-make-an-if-function-work-correctly/m-p/2934119#M121207
<P><LI-USER uid="521"></LI-USER> 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.</P><P> </P>Sat, 06 Nov 2021 12:48:49 GMThttps://techcommunity.microsoft.com/t5/excel/calculating-formulas-to-make-an-if-function-work-correctly/m-p/2934119#M121207pdowney2021-11-06T12:48:49ZRe: Calculating formulas to make an IF function work correctly.
https://techcommunity.microsoft.com/t5/excel/calculating-formulas-to-make-an-if-function-work-correctly/m-p/2934153#M121211
<P><LI-USER uid="1208693"></LI-USER> </P>
<P>Here are bit more details about an issue</P>
<P><A href="https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result" target="_blank">Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs</A></P>
<P><A href="https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/" target="_blank">Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” - Microsoft 365 Blog</A></P>Sat, 06 Nov 2021 12:54:56 GMThttps://techcommunity.microsoft.com/t5/excel/calculating-formulas-to-make-an-if-function-work-correctly/m-p/2934153#M121211Sergei Baklan2021-11-06T12:54:56Z