Why does adding an IF statement alter the outcome of the calculation occasionally?

[EvertM]

I am trying to calculate overtime, and am using the following simple setup (file attached):

A1: 07:30
B1: 15:15

C1: =((B1-A1)*1440)-465 This will show a positive value if the difference between A1 & B1 is more than 465 minutes, and a negative value if it's below 465. I get 0, which is correct
C2: =IF(A2,((B2-A2)*1440)-465,"") Should give the same outcome, as long as A2 is not empty. I get -0.00000000000005684342 ...


However, if I repeat the same process with 08:30-16:15, both formulas give me the correct answer, 0

Why is this happening?

[Beamernsw]

Another wierd Excel thing
I can't help answering your question as to why, but maybe this little change will help your calculations.

=IF(A2,INT((B2-A2)*1440)-465,"")

[FDibbins]

My 1st question would be...
=if(A2...what?

All this is ding is testing to see if A2 contains a value. If it does, then it calcs ((B2-A2)*1440)-465 otherwise it returns "" (nothing)

You are encountering excel's floating point decimal error. It is a known error, read this to understand what it is...
https://docs.microsoft.com/en-us/off...ccurate-result

[EvertM]

My 1st question would be...
=if(A2...what?

All this is ding is testing to see if A2 contains a value. If it does, then it calcs ((B2-A2)*1440)-465 otherwise it returns "" (nothing)

You are encountering excel's floating point decimal error. It is a known error, read this to understand what it is...
*link removed*

The IF-statement was more complicated, and basically only there to prevent the calculation from taking place should any of the required values not be present.

My whole point is that the formula has a different outcome when I add the IF-statement, and only for certain values.

[EvertM]

Just did a test with Google Sheets.

For the 07:30-15:15 period, it also reports -0.00000000000005684342 (both with and without the IF)
For 08:30-16:15 it reports 0

So it can't be excel's floating point decimal error, as suggested. Unless Google Sheets is a web interface for Microsoft Excel...?

[curiouscat408]

My whole point is that the formula has a different outcome when I add the IF-statement, and only for certain values.

To summarize, you are asking why

=((B1-A1)*1440)-465

results in exactly zero (0.00E+00 in Scientific format)

but the same calculation in an IF expression does not. Namely:

=IF(A2,((B2-A2)*1440)-465,"")

results in about -5.68E-14, an infinitesimal difference.

FDibbins partially answers the question: the infinitesimal difference (-5.68E-14) is the correct result due to binary arithmetic anomalies.

But Excel arbitrarily replaces such infinitesimal differences with exactly zero, but only under certain arcane conditions and in very limited contexts, namely: only formulas of the literal form =expression - expression and both operands are nonzero (or addition of nonzero values with opposite signs).

This is poorly and incompletely explained under the title "Example when a value reaches zero" in the MSFT document that FDibbins cites.

(There are also some conditions where the SUM function -- and only the SUM function -- also replaces infinitesimal differences with exactly zero.)

The syntax is very specific. For example, =(expression) and =expression+0 will defeat this heuristic, resulting in an infinitesimal difference.

So, if you wrote =((B1-A1)*1440 - 465) instead, you would see the same result as you see with the IF expression, assuming the cell format is General or Scientific.