? Link attached - How to validate SIN (Social Insurance Number)

[newsgroup2003]

I am trying to create a spreadsheet for work to validate SIN numbers.
This is the formula in it's non spreadsheet form:

http://www.pwgsc.gc.ca/compensation/ppim/ppim-5-1-e.html

SIN VALIDATION
PURPOSE--To check the validity of a Social Insurance No. (SIN).
REQUIREMENT--A newly acquired, or previously un-recorded SIN, should
be checked for validity prior to input so as to ensure that it will
pass the personnel-pay system computer edits.
PROCEDURES
TO VALIDATE A SIN, PROCEED AS FOLLOWS:
Originator
--WRITE the SIN on a sheet of paper, e.g. 440-968-592;
-- INSERT a check mark over the 2nd, 4th, 6th and 8th digits, as
indicated above;
-- WRITE the SIN again, but this time doubling the digits that were
check-marked, i.e. 480-18616-5182.
WHERE THE DOUBLING OF A SINGLE DIGIT RESULTS IN A TWO-DIGIT NUMBER,
THEN:
| -- ADD these two digits to form a single digit,
| -- AND add all of these numbers, i.e. 4+8+0+9+6+7+5+9+2 = 50.
IF THE SIN IS VALID THE RESULTING TOTAL MUST BE A MULTIPLE OF TEN.
Therefore the above SIN is valid in that the total is 50.

 

[Ron Coderre]

Try this:
For a SIN in A1,
B1:
=MOD(SUM(--MID(A1,{1,3,6,9,11},1))+SUM((MOD(2*MID(A1,{2,5,7,10},1),10)+INT(2*MID(A1,{2,5,7,10},1)*0.1))),10)=0
Copy that formula down as far as needed

A1: 440-968-592
B1: the formula returns TRUE

A2: 123-456-789
B2: the formula returns FALSE


Does that work for you?

***********
Regards,
Ron

 

[Robert_Steel]

If you are able to enter each digit into a seperate cell
eg. A1:I1
=SUMPRODUCT(INT((A1:I1)*{1,2,1,2,1,2,1,2,1}/10)+MOD((A1:I1)*{1,2,1,2,1,2,1,2,1},10))
will return the check digit.
You can check this is a multiple of 10 using
=if(mod(B1,10)=0,"OK","Error")

There may be a way to simply enter the SIN with ###-###-### format but I
will need to give it more thought.
Let us know if this does the job or give us more info on how you would
like to enter the number and exactly what output you would like.

hth RES

 

[Robert_Steel]

I have given it more work. Plus got some ideas from Ron's approach

this assumes a cell has the SIN in in the format ###-###-###
This will give the check digit
=SUM(INT((MID(A1,{2,5,7,10},1))*2/10))+SUM(MOD((MID(A1,{1,2,3,5,6,7,9,10,11},1))*{1,2,1,2,1,2,1,2,1},10))

I also like Ron's
=mod(A2,10)=0 to give a True False return for the flag.

hth RES

 

[newsgroup2003]

I appreciate everyone's efforts. Basically, I would like to keep the
output simple. True or False as an output is fine. Unfortunately,
with all the examples posted here, I was not able to get any of the
formulas to take ie. excel to accept them.

Don't know what I am doing wrong. But, one of the error messages in the
formula
=SUMPRODUCT(INT((A1:I1)*{1,2,1,2,1,2,1,2,1}/10)+MOD((A1:I1)*{1,2,1,2,1,2,1,­2,1},10))
shows "(MOD, number, divisor) -2" error.

I have tried to enter each digit in it's own field and entered the SIN
in ONE field. In this field, I used a custom format of 000-000-000 or
###-###-###.

 

[Marko]

Oops, I got the formula's to work. I really appreciate everyone's
efforts!! Now, my next challenge is to convert the formula to one that
Quattro Pro understands :-(

Again, thank you too all and Season's Greetings.

Sam