Check Digit calculation (IBAN) - Discrepancies when using numbers with 18 digits or MOD-function

[Ritesh Sara]

Hi guys,

I am trying to calculate check digits (Modulus 97 check) for an IBAN
creation demo and am running in serious problems with Excel. First approach
I tried is to use the MOD-function however this seems to be not possible
using numbers with 18 digits.

Next approach I tried is a "step by step" calcualtion however the Excel
results are NOT correct when dividing numbers with 18 digits.

E.g. 210005665660111000 / 97 = 2165006862475370.00 -> rcorrect result should
be 2165006862475371.1340

Does anybody have any experience with this discrepancies or with check digit
calculations in Excel.

Thanks much for all your help.


Ritesh

 

[Niek Otten]

Hi Ritesh,

Excel's precision is 15 significant digits.
If you require more digits, make it text. Of course you then can't calculate
with it, but you can retrieve any character with the LEFT(), RIGHT() and
MID() functions.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[JE McGimpsey]

XL's precision limit is 15 decimal digits, so you'll not be able to use
native math functions for 18 digits.

If you want to do this in XL, you'll need to break the number up, or
store it as text and use string manipulation to work on part of the
string at a time.

There are at least a couple of available add-ins that claim to extend
precision up to 200 digits, though I've never used them. You can find
them with a Google search.

 

[Ron Rosenfeld]

Hi guys,

I am trying to calculate check digits (Modulus 97 check) for an IBAN
creation demo and am running in serious problems with Excel. First approach
I tried is to use the MOD-function however this seems to be not possible
using numbers with 18 digits.

Next approach I tried is a "step by step" calcualtion however the Excel
results are NOT correct when dividing numbers with 18 digits.

E.g. 210005665660111000 / 97 = 2165006862475370.00 -> rcorrect result should
be 2165006862475371.1340

Does anybody have any experience with this discrepancies or with check digit
calculations in Excel.

Thanks much for all your help.


Ritesh

Excel's precision is fifteen digits.

You may be able to use VBA and use the Decimal data type depending on the
algorithm.


--ron

 

[Dana DeLouis]

I don't see the original post, so here are some general ideas. Hope this
helps.
I used Int since you are doing Mod 97, and it shouldn't make a difference.
A higher Mod requires a few more steps. I mention this as the use of Int( )
will revert back to double.

Sub Demo()
Dim n
Dim R As Long
n = CDec("210005665660111000") / 97
R = (n - Int(n)) * 97

Debug.Print n
Debug.Print R
End Sub

Returns:
2165006862475371.1340206185567
13

Which checks with another program:
Mod[210005665660111000, 97]
13

HTH :>)

 

[Dave Peterson]

If you store that 18 digit value as a string by preformatting the cell as Text
or typing your entry with a leading quote '210005665660111000, you could use a
User Defined Function to do the checksum.

Option Explicit
Function IBANChkSum(rng As Range) As Variant

Dim myVal As Variant

Set rng = rng(1)
If Application.IsNumber(rng.Value) = True Then
IBANChkSum = CVErr(xlErrRef)
Else
If IsNumeric(rng.Value) = False Then
IBANChkSum = CVErr(xlErrRef)
Else
myVal = CDec(rng.Value)
IBANChkSum = "'" & Format(myVal / 97, "0.0000")
End If
End If

End Function

I got this returned: '2165006862475371.1340
(yes, with the leading single quote to force text)

But a small search using Google sure makes it look like this is not sufficient:
http://www.ecbs.org/Download/EBS204_V3.2.pdf

But maybe it's just part of your routine???

 

[Harlan Grove]

Dana DeLouis wrote...
....

I used Int since you are doing Mod 97, and it shouldn't make a difference.
A higher Mod requires a few more steps. I mention this as the use of Int( )
will revert back to double.

Sub Demo()
Dim n
Dim R As Long
n = CDec("210005665660111000") / 97
R = (n - Int(n)) * 97

Debug.Print n
Debug.Print R
End Sub

Returns:
2165006862475371.1340206185567
13

Which checks with another program:
Mod[210005665660111000, 97]
13

....

Personally, I prefer divide & conquer (pun intended), and for division
the best d&c algorithm is long division. If there are 18 decimal
numberals in the numeric string, the remainder when dividing by 97 is
given by

=MOD(MOD(MOD(MID(s,1,6),97)&MID(s,7,6),97)&MID(s,13,6),97)

Elementary school long division uses one numeral at a time, but it's
not necessary. It's reasonably efficient to use 3 chunks of 6 numerals
each. Note that using 2 chunks of 9 numerals each can bump into the bug
in Excel's MOD function
(http://support.microsoft.com/default.aspx?scid=kb;en-us;119083).

FTHOI, the quotient is given by

=INT(MID(s,1,6)/97)&TEXT(INT((MOD(MID(s,1,6),97)&MID(s,7,6))/97),"000000")
&TEXT(INT((MOD(MOD(MID(s,1,6),97)&MID(s,7,6),97)&MID(s,13,6))/97),"000000")

and the full result could be given by

=INT(MID(s,1,6)/97)&TEXT(INT((MOD(MID(s,1,6),97)&MID(s,7,6))/97),"000000")
&TEXT(INT((MOD(MOD(MID(s,1,6),97)&MID(s,7,6),97)&MID(s,13,6))/97),"000000")
&TEXT(MOD(MOD(MOD(MID(s,1,6),97)&MID(s,7,6),97)&MID(s,13,6),97)/97,
".000000000000000")

which returns

2165006862475371.134020618556701

Who says Excel can't handle more than 15 decimal digits?!

 

[Dana DeLouis]

Thanks Harlan! Very nice. I've never seen that before. Interesting. :>)
Looks like one can break the number into different size chunks and it still
works.
I was messing around with vba. I'm trying different text ideas for speed.
This is version 1.
I really wish that Mod bug would get fixed.
Anyway, thanks again. :>)

Sub Demo()
Dim n, v, r, j
n = CDec("1234567890123456789012345678")
v = Split(Format(n, "###### ###### ###### ###### ######"), Space(1))

r = v(0) Mod 97
For j = 0 To UBound(v) - 1
r = (r & v(j + 1)) Mod 97
Next j
Debug.Print r
End Sub

returns: 52
which checks with another program:

Mod[1234567890123456789012345678, 97]
52

Thank you very much. :>)
--
Dana DeLouis
Win XP & Office 2003

Dana DeLouis wrote...
...

I used Int since you are doing Mod 97, and it shouldn't make a difference.
A higher Mod requires a few more steps. I mention this as the use of
Int( )
will revert back to double.

Sub Demo()
Dim n
Dim R As Long
n = CDec("210005665660111000") / 97
R = (n - Int(n)) * 97

Debug.Print n
Debug.Print R
End Sub

Returns:
2165006862475371.1340206185567
13

Which checks with another program:
Mod[210005665660111000, 97]
13

...

Personally, I prefer divide & conquer (pun intended), and for division
the best d&c algorithm is long division. If there are 18 decimal
numberals in the numeric string, the remainder when dividing by 97 is
given by

=MOD(MOD(MOD(MID(s,1,6),97)&MID(s,7,6),97)&MID(s,13,6),97)

Elementary school long division uses one numeral at a time, but it's
not necessary. It's reasonably efficient to use 3 chunks of 6 numerals
each. Note that using 2 chunks of 9 numerals each can bump into the bug
in Excel's MOD function
(http://support.microsoft.com/default.aspx?scid=kb;en-us;119083).

FTHOI, the quotient is given by

=INT(MID(s,1,6)/97)&TEXT(INT((MOD(MID(s,1,6),97)&MID(s,7,6))/97),"000000")
&TEXT(INT((MOD(MOD(MID(s,1,6),97)&MID(s,7,6),97)&MID(s,13,6))/97),"000000")

and the full result could be given by

=INT(MID(s,1,6)/97)&TEXT(INT((MOD(MID(s,1,6),97)&MID(s,7,6))/97),"000000")
&TEXT(INT((MOD(MOD(MID(s,1,6),97)&MID(s,7,6),97)&MID(s,13,6))/97),"000000")
&TEXT(MOD(MOD(MOD(MID(s,1,6),97)&MID(s,7,6),97)&MID(s,13,6),97)/97,
".000000000000000")

which returns

2165006862475371.134020618556701

Who says Excel can't handle more than 15 decimal digits?!

 

[Harlan Grove]

Dana DeLouis wrote...
....

I really wish that Mod bug would get fixed.

....

Especially since all it would take would be replacing the current,
highly questionable implementation with a simple fmod call in the Excel
source code (assuming Excel's MOD function is implemented in C).

Excel: =MOD(123456789012,97) returns #NUM!

123: @MOD(123456789012,97) returns 18

OOo Calc: =MOD(123456789012;97) returns 18

Gnumeric: =MOD(123456789012,97) returns 18

R: 123456789012 %% 97 returns 18

Scilab: modulo(123456789012,97) returns 18

gawk: 123456789012 % 97 returns 18

Microsoft is in a class by itself! Only Microsoft screws up a simple
arithmetic operation on double precision floating point reals.

[I do this because experience has shown that only constant, repeated
ridicule seems to move Microsoft to fix anything. If they were
motivated by the desire to write good software, this would have been
fixed over a decade ago.]