While using excel IF()

Occasional Visitor

i've come across what i think a paradoxical ERROR as below;


i was decreasing month number on head line, using "=YEAR(TODAY())+MONTH(TODAY())/100" to record quarterly updates of company earnings.



I cannot figure out what i thought wrong?

could anyone let me know what?


1 Reply

@GaianASOSFAD  wrote:  ``I cannot figure out what i thought wrong?``


Neither can we because you do not actually explain what "paradoxical error" you want us to look at.


Is it that L2-$C2 appears to be 0.03 in the value-if-false part, but L2-$C2=0.03 returns FALSE?


If so, the explanation might be simply binary arithmetic anomalies ("errors", a misnomer).


Note that 2022.03 - 2022 appears to be 0.0299999999999727, not 0.03.


The expression should be ROUND(L2-$C2, 2) to overcome the difference.


The reason is:  most decimal fractions cannot be represented exactly in 64-bit floating-point, which is how Excel stores numeric values internally.


And the binary approximation of the same decimal fraction (0.03, for example) might differ depending on the magnitude of the number.


2022.03 appears to be just that because Excel formats only the first 15 significant digits (rounded).  The exact decimal representation of the binary approximation is 2022.02999999999,9972715158946812152862548828125 .


When we subtract 2022, we are left with 0.0299999999999727,15158946812152862548828125 .


In contrast, the exact decimal representation of the binary approximation of 0.03 is 0.0299999999999999,988897769753748434595763683319091796875 .


(I use period for the decimal point and comma to demarcate the first 15 significant digits.)