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

Discussion in 'Microsoft Excel Discussion' started by Ritesh Sara, Jun 3, 2005.

  1. Ritesh Sara

    Ritesh Sara Guest

    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
     
    Ritesh Sara, Jun 3, 2005
    #1
    1. Advertisements

  2. Ritesh Sara

    Niek Otten Guest

    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" <> wrote in message
    news:%23r1%...
    > 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, Jun 3, 2005
    #2
    1. Advertisements

  3. Ritesh Sara

    JE McGimpsey Guest

    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.

    In article <#r1#>,
    "Ritesh Sara" <> wrote:

    >
    > 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.
     
    JE McGimpsey, Jun 3, 2005
    #3
  4. On Fri, 3 Jun 2005 12:25:40 +0200, "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
    >


    Excel's precision is fifteen digits.

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


    --ron
     
    Ron Rosenfeld, Jun 3, 2005
    #4
  5. Ritesh Sara

    Dana DeLouis Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:%23r1%...
    >> 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
    >>
    >>

    >
    >
     
    Dana DeLouis, Jun 3, 2005
    #5
  6. Re: Check Digit calculation (IBAN) - Discrepancies when using numberswith 18 digits or MOD-function

    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
     
    Dave Peterson, Jun 3, 2005
    #6
  7. Ritesh Sara

    Harlan Grove Guest

    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, Jun 3, 2005
    #7
  8. Ritesh Sara

    Dana DeLouis Guest

    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" <> wrote in message
    news:...
    > 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, Jun 4, 2005
    #8
  9. Ritesh Sara

    Harlan Grove Guest

    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.]
     
    Harlan Grove, Jun 6, 2005
    #9
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Microsoft

    Function MOD

    Microsoft, Jul 6, 2004, in forum: Microsoft Excel Discussion
    Replies:
    6
    Views:
    271
    Dave Peterson
    Jul 6, 2004
  2. Bernard Jason

    MOD Function

    Bernard Jason, Jul 6, 2004, in forum: Microsoft Excel Discussion
    Replies:
    8
    Views:
    364
    Earl Kiosterud
    Jul 6, 2004
  3. davee10

    Losing last digit on 16 digit number

    davee10, Oct 9, 2005, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    234
    Ron Rosenfeld
    Oct 9, 2005
  4. cj21

    2-digit - 6-digit

    cj21, Jan 19, 2006, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    169
  5. Cabby

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

    Cabby, Feb 20, 2011, in forum: Microsoft Excel Discussion
    Replies:
    13
    Views:
    852
    Cabby
    Feb 22, 2011
Loading...

Share This Page