MOD Function: MOD(7.0,1) = 1 Why? and how to work around it.

C

Cabby

I'm using Excel 2003 spreadsheet, but I've tried this in Excel 2010 &
OpenOffice.org. A similiar error occurs.
You can see that I've also used the Microsoft suggested workaround but
to no avail.
Does anyone have an answer or possible suggestion.



A B C

Base Increment
5.0 0.10

Qty MOD(Qty,1) number-(INT(number/divisor)*divisor)
Equiv. = Qty-(INT(Qty/1)*1)

$A$2 5.0 0.0 0.0
SUM($A5,$B$2) 5.1 0.1 0.1
SUM($A13,$B$2) 5.9 0.9 0.9
SUM($A14,$B$2) 6.0 0.0 0.0
SUM($A15,$B$2) 6.1 0.1 0.1
SUM($A23,$B$2) 6.9 0.9 0.9
SUM($A24,$B$2) 7.0 1.0 1.0
SUM($A25,$B$2) 7.1 0.1 0.1
SUM($A26+$B$2) 7.2 0.2 0.2
SUM($A33+$B$2) 7.9 0.9 0.9
SUM($A34+$B$2) 8.0 1.0 1.0
SUM($A35+$B$2) 8.1 0.1 0.1
 
J

Jim Cone

What you see is not what's there.
Change the number format of the cells from one decimal place to several... "0.000" or
"0.###"
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Calculate Payments add-in: amount, interest rate, payment, term - in the free folder)




"Cabby" <[email protected]>
wrote in message
I'm using Excel 2003 spreadsheet, but I've tried this in Excel 2010 &
OpenOffice.org. A similiar error occurs.
You can see that I've also used the Microsoft suggested workaround but
to no avail.
Does anyone have an answer or possible suggestion.
-snip-
 
C

Cabby

Tks for the reply Jim, but no matter how many decimal places I use, it
won't turn 1 into 0.
Btw, all the plus signs in my code were actually commas. The other
thing is that I cut out
numerous lines for brevity. 20 or more rows are required being
incremented by 0.100 before
the error shows up.
 
R

Ron Rosenfeld

Tks for the reply Jim, but no matter how many decimal places I use, it
won't turn 1 into 0.
Btw, all the plus signs in my code were actually commas. The other
thing is that I cut out
numerous lines for brevity. 20 or more rows are required being
incremented by 0.100 before
the error shows up.

It sounds like you applied the decimal places to the cell where your MOD function is.

What Jim meant, I believe, was to apply it to the cell where you have the SUM function and you will probably see that what you think is 7.0 is really something like 6.99999999999999

For another test, if the above doesn't work, in some cell enter:

=SUM($A24,$B$2)=7.0

This will probably result in FALSE if our assumptions are correct, whereas

=SUM($A24,$B$2)<7.0 will probably --> TRUE
 
C

Cabby

Ron, I did understand what Jim was saying. I applied 6 decimal places
to all numbered fields.
If I can draw your attention to the data I submitted, there were at
least 35 rows in my spreadsheet.
I cut out many of the rows for brevity.

Relative to your tests, I tried them and this is what I got:

SUM($A24,$B$2) = 7 SUM($A24,$B$2) < 7
FALSE TRUE

SUM($A14,$B$2) = 6 SUM($A14,$B$2) < 6
TRUE FALSE

if I use Base = 3, error is seen on row 45 & 55.
if I use Base = 4, error is seen on row 35 & 45.
if I use Base = 5, error is seen on row 25 & 35.
 
C

Cabby

PS:
I haven't used VBA 'number MOD divisor' because I don't know how to
incorporate it into a spreadsheet.
 
J

joeu2004

I haven't used VBA 'number MOD divisor' because I don't
know how to incorporate it into a spreadsheet.

You cannot use VBA MOD per se because VBA MOD works only with
integers. It converts non-integer arguments to integers.

In VBA, you would write: myMOD1 = x - Int(x). And you will find that
it returns the same results as A5-INT(A5) in Excel.

The root cause of the problem is: Excel (and VBA; indeed most
applications) represent numbers using a computer binary form called
floating-point. For overwhelming details, see http://support.microsoft.com/kb/78113.

Consequently, most non-integers cannot be represented exactly. This
causes anomalies such as the one you see. Here is another example:
=IF(10.1-10=0.1,TRUE) returns FALSE(!).

The simple solution: use ROUND when you expect results to have a
specific precision.

For example: =MOD(ROUND(A6,1),1). Better: =ROUND(SUM(A5,$B$2),1).
Then =MOD(A6,1) returns what you expect.
 
R

Ron Rosenfeld

PS:
I haven't used VBA 'number MOD divisor' because I don't know how to
incorporate it into a spreadsheet.

VBA's MOD function only works with integers, so it would not be of much use.

It seems you have a rounding problem in your SUM function and that is giving you the unexpected results. In other words, what looks to you like 7.0 is really something like 6.9999999999999999999. So the MOD function, or even the work around using the INT function, will be giving a result of 0.999999999999999999999999999 which Excel displays as a "1"

One way around the problem would be to round the value you wish to apply the MOD function to, to your desired level of precision.

e.g. =mod(round(a2,1))
 
J

joeu2004

The simple solution:  use ROUND when you expect results to have a
specific precision.

For example:  =MOD(ROUND(A6,1),1).  Better:  =ROUND(SUM(A5,$B$2),1).
Then =MOD(A6,1) returns what you expect.

Although that would appear to work, it is even more reliable to write:

=ROUND(MOD(ROUND(A6,1),1),1)

or in the "better" example:

=ROUND(MOD(A6,1),1)

This is evident in the example =IF(MOD(10.1,1)=0.1,TRUE), which
returns FALSE(!).
 
J

joeu2004

PS.....

The simple solution:  use ROUND when you expect results to have a
specific precision.

For example:  =MOD(ROUND(A6,1),1).  Better:  =ROUND(SUM(A5,$B$2),1).
Then =MOD(A6,1) returns what you expect.

Note that formatting to 1 decimal place __alone__ is usually not
sufficient.

Formatting changes the __appearance__ of values. Usually, it does not
alter the underlying value. For example, 1.05 __appears__ as 1.1 when
formatted to 1 decimal place, but it is 1.05.

There is a calculation option -- Tools > Options > Calculation >
Precision As Displayed (PAD) -- which alters the underlying value of
the __result__ of a a formula based on the formatted precision of the
cell.

However, I do __not__ recommend PAD for several reasons.

First, it changes constants irreversibly if you make the mistake of
setting PAD or entering the constant before choosing the formatted
precision of all cells. For that reason, if you choose to try PAD, it
would be prudent to make a copy of the Excel file first.

Second, it changes __all__ results based on the formatted precision;
it does not discriminate. There are times when you might not want
that. For example, when I do amortization schedules, I do not round
interest, principal and remaining balance amounts, even though I
display them with 2 decimal places.

Third, PAD does not solve all problems because it applies only to the
formula __result__. For example, IF(10.1-10=0.1,TRUE) still returns
FALSE(!). So you still need to decide when to use and not to use
explicit rounding (i.e. the ROUND function) -- although admittedly, it
will be required much less.
 
C

Cabby

There is a calculation option -- Tools > Options > Calculation >
Precision As Displayed (PAD) -- which alters the underlying value of
the __result__ of a a formula based on the formatted precision of the
cell.

However, I do __not__ recommend PAD for several reasons.


Precision As Displayed (PAD) worked as you said it would and I will
keep in mind your recommendations.

But to beat the dead horse one last time:
My main point was not the inaccuracy of a specfic number
(though, I have learned lots about ROUND(), INT(), & MOD()),
it was the repetition of the errors.

using Base = 5 and Increment = 0.1
errors occur for numbers 7.0, 8.0, 9.0, 10.0, 77.0 thru 299.0,
no errors for numbers 300.0 thru 508.0

To detect the errors,
I used this =IF(ROUND($B5,1)=1,"TRUE","FALSE")

So I should be able to use something like this
=IF(ROUND(MOD($A5,1),1)=1,0,MOD($A5,1))

joeu2004, Ron & Jim
Thank-you for the help
Cabby
 
J

joeu2004

Precision As Displayed (PAD) worked as you said it would and
I will keep in mind your recommendations.

Or non-recommendation ;-).

But to beat the dead horse one last time:

There are laws against that in some countries :).

My main point was not the inaccuracy of a specfic number

Nor was it mine. The point is: there are inaccuracies in almost
__all__ non-integers.

The effects are difficult to predict, especially the __cumulative__
effects of repetitive operations. Therefore....

So I should be able to use something like this
=IF(ROUND(MOD($A5,1),1)=1,0,MOD($A5,1))

Well, I wouldn't.

I would use one of the rounding expressions that I mentioned
previously, at least: =ROUND(A5+$B$2,1) to increment by 0.1 (B2)
expecting accuracy to 1 decimal place.

using Base = 5 and Increment = 0.1
errors occur for numbers 7.0, 8.0, 9.0, 10.0, 77.0 thru 299.0,
no errors for numbers 300.0 thru 508.0

Your perception that there are "no" errors with other numbers is
incorrect.

It is due, in part, to an Excel heuristic that attempts to "correct"
results that are "close to zero" [1].

Your perception is compounded by the fact that it appears that you
replaced MOD(A5,1) with A5-INT(A5). Nothing wrong with that; it just
exposes this Excel anomaly.

Let me explain....

If you compute 300 by adding 0.1 to 5 repetitively, the result is
actually 300-5*2^-44, slightly less than 300.

You can see this by computing =A2954-300-0 [1] and formatting as
Scientific. You will see that the difference is about -2.84E-13, a
very small number [4].

(Read: -2.84 times 10 to the -13 power. Also: -2.84 shift to the
right of the decimal point 13 times.)

And indeed, if you compute =MOD(A2954,1) or =A2954-INT(A2954)-0, the
result is about -2.84E-13.

Note: The negative result is reminiscent of a defect in the Excel INT
function [2].

In fact, MOD(A2954,1) and A2954-INT(A2954)-0 should return about
0.999999999999716. That is demonstrated by a VBA implementation of
MOD(x,1) [3].

But the important point is: the result of =MOD(A2954,1) and =A2954-
INT(A2954)-0 is not zero.

Summary....

As stated previously, most non-integer values cannot be represented
exactly in Excel (and most applications).

Arithmetic operations with these inaccurate non-integer values often
have inaccurate results.

It is difficult to predict, a priori, whether or not the results will
be inaccurate, and if so, in what way. In depends on many factors,
including but limited to the relative size of the operands of the
arithmetic operation.

To minimize computational anomalies when you expect/require results
accurate to a specific number of decimal places, it is important to
use some form of explicit rounding -- either the ROUND function
itself, or the "Precision As Displayed" calculation option.

But note: "Precision As Displayed" is __not__ recommended.

If you require more in-depth understanding, I can provide it. But it
would necessarily have to get into concepts like 53-bit mantissa, 80-
bit computation (FPU) and 64-bit floating-point storage.

If that sounds overwhelming -- and it is for most people -- just stick
with the mantra "explicitly round your computations prudently".

-----

Endnotes

[1] Re: A2954-300-0. The redundant "-0" is needed to avoid a self-
correcting heuristic in Excel, which tries recognize results that are
"close to zero" [sic] and replace them with zero. The dubious Excel
heuristic is poorly described under the misleading title "Example When
a Value Reaches Zero" in http://support.microsoft.com/kb/78113.

[2] Mathematically, x-INT(x) cannot be negative. But Excel INT(x)
sometimes returns ROUNDUP(x,0) (!). I believe the root cause is the
same as the root cause of the defect poorly and incompletely described
in http://support.microsoft.com/kb/161234.

[3] VBA UDF myMOD1:

Function myMOD1(x As Double) As Double
myMOD1 = x - Int(x)
End Function

[4] Instead of computing 300 by adding 0.1 to 5 repetitively, try
computing 300 by =5+(3000-50)*0.1. You will see that that computes
300 exactly, by coincidence. That should give you some idea of the
difficulty in predicting results of floating-point operations.
 
J

joeu2004

Non-material errata....

[2] Mathematically, x-INT(x) cannot be negative. But Excel INT(x)
sometimes returns ROUNDUP(x,0) (!). I believe the root cause is the
same as the root cause of the defect poorly and incompletely described
in http://support.microsoft.com/kb/161234.

The defect that causes INT(x) to return ROUNDUP(x,0) sometimes is
__not__ the same as the root cause of the problem described in
http://support.microsoft.com/kb/161234.

I had concluded that the root cause of the problem related to KB
161234 requires 3 conditions:

1. The integer part is less than 2^16 (i.e. fits within 16 bits).
If the integer part is zero, the fraction must exceed 2^-1 (0.5) [6].
And

2. The fraction part can be represented in 32 bits or less. That
is, all bits to the right are zero. And

3. The 16th significant digit of the value is 5 or more.

For details, see my response dated 24 Jan 2011 9:18 AM in
http://social.answers.microsoft.com...m/thread/0a6fe743-f5a8-4744-86a1-3a198223bd23

The value 300-5*2^-44 (the result of add 0.1 to 5 repetitively until
Excel displays 300) meets conditions #1 and #3, but not #2. The
fractional part requires 44 bits.

Off-hand, the root cause of the defect that causes INT(x) to return
ROUNDUP(x,0) sometimes seems quite straight-forward.

For example, 300-5*2^-44 is represented internally exactly as
299.999999999999,71578290569595992565155029296875, which rounds up to
300.

In fact, 300-8*2^-44 is represented exactly as
299.999999999999,54525264911353588104248046875, and INT(300-3*2^-44)
is 300. (Incorrect!)

But 300-9*2^-44 is represented exactly as
299.999999999999,48840923025272786617279052734375, and
INT(300-4*2^-44) is 299. (Correct!)

It seems as if Excel INT(x) is implemented effectively by first
rounding x to 15 significant digits, then isolating the integer part.
 
J

joeu2004

Minor clarification....

If you compute 300 by adding 0.1 to 5 repetitively, the result is
actually 300-5*2^-44, slightly less than 300.

You can see this by computing =A2954-300-0 [1] and formatting as
Scientific.  You will see that the difference is about -2.84E-13, a
very small number [4].

I had presumed that A2954 would contain 300 computed by adding 0.1 to
5 repetitively.

But if you start with 5.0 in A5, as it seems you did in your initial
posting, A2955, not A2954, would contain 300 computed by adding 0.1
repetitively.

Whatever!
 
J

joeu2004

[Non-Google Groups users will see this as a "duplicate" posting. I
want to correct some minor, but potentially confusing typos.]

Non-material errata....

[2] Mathematically, x-INT(x) cannot be negative. But Excel INT(x)
sometimes returns ROUNDUP(x,0) (!). I believe the root cause is the
same as the root cause of the defect poorly and incompletely described
in http://support.microsoft.com/kb/161234.

The defect that causes INT(x) to return ROUNDUP(x,0) sometimes is
__not__ the same as the root cause of the problem described in
http://support.microsoft.com/kb/161234.

I had concluded that the root cause of the problem related to KB
161234 requires 3 conditions:

1. The integer part is less than 2^16 (i.e. fits within 16 bits).
If the integer part is zero, the fraction must exceed 2^-1 (0.5).
And

2. The fraction part can be represented in 32 bits or less. That
is, all bits to the right are zero. And

3. The 16th significant digit of the value is 5 or more.

For details, see my response dated 24 Jan 2011 9:18 AM in
http://social.answers.microsoft.com/Forums/en-US/excelform/thread/0a6...

The value 300-5*2^-44 (the result of adding 0.1 to 5 repetitively
until
Excel displays 300) meets conditions #1 and #3, but not #2. The
fractional part requires 44 bits.

Off-hand, the root cause of the defect that causes INT(x) to return
ROUNDUP(x,0) sometimes seems quite straight-forward.

For example, 300-5*2^-44 is represented internally exactly as
299.999999999999,71578290569595992565155029296875, which rounds up to
300, larger than 300-5*2^-44 (!).

In fact, 300-8*2^-44 is represented exactly as
299.999999999999,54525264911353588104248046875, and INT(300-8*2^-44)
is 300. (Incorrect!)

But 300-9*2^-44 is represented exactly as
299.999999999999,48840923025272786617279052734375, and
INT(300-9*2^-44) is 299. (Correct!)

It seems as if Excel INT(x) is implemented effectively by first
rounding x to 15 significant digits, then isolating the integer part.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top