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

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

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

"Ritesh Sara" <ritesh_sarampan@hotmail.com> wrote in message
news:%23r1%23aaCaFHA.3528@TK2MSFTNGP09.phx.gbl...
> 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
>
>

On Fri, 3 Jun 2005 12:25:40 +0200, "Ritesh Sara" <ritesh_sarampan@hotmail.com>
wrote:

>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

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 :>)
--
Dana DeLouis
Win XP & Office 2003


"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:epCsYHDaFHA.2996@TK2MSFTNGP10.phx.gbl...
> 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
>
> "Ritesh Sara" <ritesh_sarampan@hotmail.com> wrote in message
> news:%23r1%23aaCaFHA.3528@TK2MSFTNGP09.phx.gbl...
>> 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
>>
>>
>
>

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???





Ritesh Sara wrote:
>
> 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

--

Dave Peterson

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,1 3,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...b;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)&M ID(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)&M ID(s,13,6))/97),"000000")
&TEXT(MOD(MOD(MOD(MID(s,1,6),97)&MID(s,7,6),97)&MI D(s,13,6),97)/97,
".000000000000000")

which returns

2165006862475371.134020618556701

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

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


"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1117832336.754804.17820@g49g2000cwa.googlegro ups.com...
> 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,1 3,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...b;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)&M ID(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)&M ID(s,13,6))/97),"000000")
> &TEXT(MOD(MOD(MOD(MID(s,1,6),97)&MID(s,7,6),97)&MI D(s,13,6),97)/97,
> ".000000000000000")
>
> which returns
>
> 2165006862475371.134020618556701
>
> Who says Excel can't handle more than 15 decimal digits?!
>