Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Modulus 10 help

  1. #1
    Guest

    Default

    I'm trying to set up a simple spreadhseet so our payment clerks can enter a cheque number and have it auto-generate the self-check cheque digit.

    The bank says its as follows : "the digit is based on modulus 10, weighting 731731"

    apparently cheque number 535644 will have a last digit of 7 based on the above logic, but my spreadhseet won't agree....

    is anyone familiar with what I ought to be doing ? I'm looking at each digit and multiplying by the weighting, then adding the sum of the resulting digits and deducting it from the next multiple of 10 to get a remainder but it's not working out

    Any takers ?

    cheers
    Chris

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I know there is a way to do this with a shorter array formula but this will do the trick. It assumes that the cheque number is in cell A1

    =MOD(LEFT(A1,1)*7+MID(A1,2,1)*3+MID(A1,3,1)+MID(A1,4,1)*7+MID(A1,5,1)*3+RIGHT(A1,1),10)

    Hope it helps,
    D

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks DK, it certainly gives the right cheque digit in this case..... I'm back at home now so will plug this into some more examples tommorrow morning to see if it works globally..... weird though, all the examples I looked at blabbered on about having to then sum up the digits of each individual multiplication (ie first digit was 5 so multiplied by 7 gave 35... of which 3 + 5 gave 8.... etc etc)

    closest I ever came to modulus 10 at school was Blakes 7....!

    thanks again mate
    Chris

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yup, it works fine, thanks mate. You're right too, I trimmed it down to

    =(MOD(SUM(MID(+A1,{1;2;3;4;5;6},1)*({7;3;1;7;3;1})),10))

    weird - I'm using the same sort of formula for checking VAT numbers too, never realising that the numbers form part of a self-checking thing.

    thanks again
    Chris

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-20 02:34, Chris Davison wrote:
    Yup, it works fine, thanks mate. You're right too, I trimmed it down to

    =(MOD(SUM(MID(+A1,{1;2;3;4;5;6},1)*({7;3;1;7;3;1})),10))

    weird - I'm using the same sort of formula for checking VAT numbers too, never realising that the numbers form part of a self-checking thing.

    thanks again
    Chris
    Just an info question: Is the weighting company dependent? What is the "weighting" that is used with VAT? Is this EU-wide?

    Your shortening of the formula dk suggested is nice. You don't need extra parens and that +A1 (you use some wizard to devise a formula, I guess).

    Why not expand it a bit as in

    =MOD(SUM(MID(A1,{1;2;3;4;5;6},1)*({7;3;1;7;3;1})),10)=RIGHT(A1)+0

    so that it returns a logical value?

    Aladin

    PS. I guess I've something here I can use in my classes with Accountancy students.



  6. #6
    Guest

    Default

    Just an info question: Is the weighting company dependent? What is the "weighting" that is used with VAT? Is this EU-wide?

    The cheques are from HM Treasury (UK Government) rather than a commercial bank, as we are funded via Government money (a charity). This weighting (731731) applies to any cheques within the range 000000 to 999999. The first 3 digits are allocated to certain Government Departments (ie 001 = Defence, 010 = Education, 020 = Foreign Office etc etc). So I guess for other Departments outside this range, the weightings may be different - I'd have to ring HM Treasury to find out !

    The weighting on UK VAT numbers is 8,7,6,5,4,3,2,10,1 although the modulus is 97
    (no remainder). I want to know if this is EU-wide also I am waiting to hear back from our Tax Inspector on this, but will keep you posted.


    Your shortening of the formula dk suggested is nice. You don't need extra parens and that +A1 (you use some wizard to devise a formula, I guess).

    No wizard When DK popped his formula up it suddenly reminded me of the formulae we were looking at on my VAT numbers. I don't like the wizards longterm - they say do this, do that, now do this and I find I need to understand the formula first.

    Why not expand it a bit as in

    =MOD(SUM(MID(A1,{1;2;3;4;5;6},1)*({7;3;1;7;3;1})),10)=RIGHT(A1)+0

    so that it returns a logical value?

    thanks I'll take a look... I notice the +0 "co-oercion" too

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    forgot to login...

    Aladin - yes, all Departments start at 000000 cheque number (ie all companies), with their account number differentiating who it belongs to, so the weighting (731731) is static to everyone : every Government Department holding an account at HM Treasury within the Bank of England.

    (Prime numbers, I just noticed)

    Hope this helps
    Chris

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •