Modulus 10 help

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

[dk]

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

[Chris Davison]

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

[Chris Davison]

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

[Aladin Akyurek]

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.

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

[Chris Davison]

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