Jennie wrote:

=MOD((15*(1,4-1));6)

returns 6,0 and

=MOD((15*(0,4));6)

returns 0,0

I would like the first MOD function to return 0 as well. Is there a reason why Excel behaves like this?

Yes.  It is explained in overwhelming detail in http://support.microsoft.com/kb/78113.

In a nutshell, the problem is due to the fact that Excel (and most applications) uses binary floating-point to represent numbers.  Consequently, most non-integers (and integers greater than 2^53) cannot be represented exactly.  The usually-infinitesimal differences sometimes become noticable after some arithmetic operations.

In your case, 1.4 is represented as 1.39999999999999,9911182158029987476766109466552734375, so 1.4-1 is represented as 0.399999999999999,911182158029987476766109466552734375.  The result of multiplying by 15 is represented as 5.99999999999999,82236431605997495353221893310546875.

Note:  Because Excel displays only the first 15 significant digits, rounding the 16th, these intermediate results will display as 1.40000000000000, 0.40000000000000 and 6.00000000000000.  But they really aren't.  You can verify this by entering, for example, =A2-0.4-0 formatted as General, where A2 contains =1.4-1.  The redundant -0 is needed to avoid the dubious heuristic poorly described under the misleading title "Example When a Value Reaches Zero" in KB 78113.  In short, sometimes Excel forces an arithmetic result to be exactly zero if it thinks the result is "close enough" to zero.

In contrast, the constant 0.4 is represented by 0.400000000000000,02220446049250313080847263336181640625.  By coincidence, multipying by 15 results in exactly 6.  But I empahsize:  that is only by coincidence.

The general solution is to explicitly round expressions to the precision that your require.  For example:

=MOD(ROUND(15*(1.4-1),2),6)

if you required accuracy to 2 decimal places.

Jennie wrote:

> I did not experience the same problem in office 2003

I am using XL2003 for all of my work above.  So yes, the same problem exists in XL2003.

You might not have seen it in XL2003 because instead of those exact constants, you were working with the results of other arithmetic operations which only appeared to have results like those constants.  Alternatively, you might have been working with very different constants.  Each arithmetic situation is a little different.  It is very difficult to predict which combinations of numbers will exhibit these floating-point anomalies.

=iferror(MODE(IF(AJ15:AJ24<>0,AJ15:AJ24)),"")

If your formula does not result in an error then the result of your formula is displayed. When your formula generates an error, "" or nothing is displayed.