Difference using MOD function between Excel 2007 desktop and Android version

[RogerE]

I have a simple time sheet which has a summary sheet showing total time attended during a period and then the hours/minutes as separate fields. It has been used since Excel 95 - currently using Excel 2007.


[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD="width: 64"] Forename

[/TD]
[TD="width: 99"] Surname
[/TD]
[TD="width: 64"] Total
[/TD]
[TD="width: 64"] Hours
[/TD]
[TD="width: 64"] Mins
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD="width: 64"] Fred

[/TD]
[TD="width: 99"] Colon

[/TD]
[TD="width: 64"]

30:00​

[/TD]
[TD="width: 64"]

30​

[/TD]
[TD="width: 64"]

0​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD="width: 64"]Nobby

[/TD]
[TD="width: 99"] Nobbs

[/TD]
[TD="width: 64"]

68:00
​

[/TD]
[TD="width: 64"]

68​

[/TD]
[TD="width: 64"]

0
​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD="width: 64"] Samuel

[/TD]
[TD="width: 99"] Vines

[/TD]
[TD="width: 64"]

41:45​

[/TD]
[TD="width: 64"]

41​

[/TD]
[TD="width: 64"]

45
​

[/TD]
[/TR]
</tbody>[/TABLE]

The totals are calculated from a data entry sheet using a formula =SUMIFS(Data!F$2:F$145,Data!A$2:A$145,"="&Summary!A3,Data!B$2:B$145,"="&Summary!B3)

On the data sheet the row format is

<colgroup><col width="86"><col width="114"><col width="84"><col width="108" span="2"><col width="73"><col width="64" span="2"></colgroup><tbody>
[TD="class: xl66, width: 86"]Forename
[/TD]
[TD="class: xl66, width: 114"]Lastname[/TD]
[TD="class: xl67, width: 84"]Date[/TD]
[TD="class: xl66, width: 108"]start[/TD]
[TD="class: xl66, width: 108"]End[/TD]
[TD="class: xl66, width: 73"]Time[/TD]
[TD="class: xl66, width: 64"]Hrs[/TD]
[TD="class: xl66, width: 64"]Mins
[/TD]

</tbody>

where start and End are just time fields formatted as hh:mm, Time (The F column) is End-start (=E2-D2), and The Hours and minutes are just HOUR() and MINUTE() extractions



On the Summary sheet the total time can be in excess of 24 hours so the TOTAL field is formatted : [h]:mm.

The Summary Hours field is determined by a formula: =$C3-MOD($C3,1/24)

The Summary Mins field formula is:=MINUTE(C3)

This works fine on several PCs using the desktop versions of Excel up to, and including version 2010.

Recently a problem has come to light when some, but not all, entries where the total is a round number of hours show incorrect values for the Hours field. For example the "Nobby Nobbs" entry on Android version of Excel (1.0.1) shows as:


[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD="width: 64"] Forename

[/TD]
[TD="width: 99"] Surname
[/TD]
[TD="width: 64"] Total
[/TD]
[TD="width: 64"] Hours
[/TD]
[TD="width: 64"] Mins
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD="width: 64"] Fred

[/TD]
[TD="width: 99"] Colon

[/TD]
[TD="width: 64"]

30:00​

[/TD]
[TD="width: 64"]

30​

[/TD]
[TD="width: 64"]

0​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD="width: 64"]Nobby

[/TD]
[TD="width: 99"] Nobbs

[/TD]
[TD="width: 64"]

68:00
​

[/TD]
[TD="width: 64"]

67
​

[/TD]
[TD="width: 64"]

0
​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD="width: 64"] Samuel

[/TD]
[TD="width: 99"] Vines

[/TD]
[TD="width: 64"]

41:45​

[/TD]
[TD="width: 64"]

41​

[/TD]
[TD="width: 64"]

45
​

[/TD]
[/TR]
</tbody>[/TABLE]

As can be seen the Hours column for Nobby Nobbs calculates incorrectly. In all reported cases the hour is one hour below the actual number of hours.

There was one case where one person attended 3 one hour sessions, which the total showed as 3:00 but the Hours showed as 2

Someone has also reported the same anomoly when using Excel 365, which I don't have.

Is there a difference between the MOD function on later Excel versions or is it something different?




Many thanks

 

[joeu2004]

As you might have guessed, the arithmetic "error" is probably due to anomalies with binary floating-point arithmetic.

In general, MOD is unreliable when the divisor has a decimal fraction that is not a power of 2.

Moreover, C3-MOD(C3,1/24) returns Excel time (e.g. 68:34 appears to become 68:00, not 68). Presumably, you use the format [h] to display 68.

That is inconsistent with MINUTE(C3), which returns an integer (e.g. 34).

Instead, I would suggest --TEXT(C3,"[h]"), formatted as General or Number with zero decimal places. That returns the integer 68, as you display.

(IMHO, that is more reliable than INT(C3*24), when we consider binary arithmetic anomalies.)

Aside.... IMHO, it would be prudent to round all time calculations to the minute, in order to minimize cumulative binary arithmetic anomalies. For example:

=--TEXT(E2-D2,"[h]:m")
and
=--TEXT(SUMIFS(Data!F$2:F$145,...),"[h]:m")

-----

Since most Android phones use a different CPU than PCs, it would not surprise me if the binary floating-point anomalies are different, so Excel MOD behaves differently.

Even though all of the CPUs probably store Excel numeric values in 64-bit binary floating-point, which is standard, most PCs (with Intel-compatible CPUs) use a proprietary 80-bit binary floating-point form for calculations. I don't know about Androids CPUs.

Nevertheless, Excel 365 on a PC should use an Intel-compatible CPU.

I am not aware of any differences in the Excel MOD implementation after Excel 2010.

But there were differences, for example, between Excel 2007 and 2010. So there might be differences in recent Excel versions. (But I doubt it.)

For example, in Excel 2007, MOD(1.5,0.05) returns about -0.0000000000000000832667268468867, whereas it returns about 0.0499999999999999 in Excel 2010.

(FYI, 1.5-0.05*INT(1.5/0.05) returns exactly zero in both Excel 2007 and 2010.)

Such differences can arise because of subtle changes in the order of arithmetic operations and when intermediate results are rounded to 64-bit binary floating-point instead of using the 80-bit intermediate results.

 

[joeu2004]

As you might have guessed, the arithmetic "error" is probably due to anomalies with binary floating-point arithmetic.
In general, MOD is unreliable when the divisor has a decimal fraction that is not a power of 2.

Here is a random example using Excel 2010(!):


[TABLE="class: grid, width: 100"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]12:10[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]22:11[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]10:13[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]15:11[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]2:28[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]5:47[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]68:00[/TD]
[TD="align: right"]67[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
A8: =SUM(A1:A6)
B8: =A8 - MOD(A8, 1/24)
Column A is formatted as [h]:mm . B8 is formatted as [h] .


In this example, changing the formula A8 to =--TEXT(SUM(A1:A6),"[h]:m") remedies the problem in B8.

But I would not rely on that.

 

[RogerE]

Thanks for the reply. Have changed to the =(TEXT.....) format and looks like that does the trick.

 

[joeu2004]

Since most Android phones use a different CPU than PCs, it would not surprise me if the binary floating-point anomalies are different, so Excel MOD behaves differently

You're welcome.

But on second thought, I do not know anything about the architecture of Excel for Android; in particular, what CPU performs the Excel calculations.

It seems unlikely that the phone CPU has sufficient power (literally) to do many Excel calculations.

It seems more likely that the Excel for Android app simply interfaces with a cloud application. That is, the Excel calculations are performed by the CPU of a remote server. And in that case, it is possible (likely?) that the remote server CPU is Intel-compatible. (But maybe not.)

I have not been able to find an authoritative online article that explains the architecture of MSFT mobile apps, or Excel for Android, in particular.

Be that as it may, I believe that my description and later demonstration of the binary floating-point anomalies of Roger's MOD calculation, in general, probably explain what Roger observed.