Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
? Link attached - How to validate SIN (Social Insurance Number)
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/...pim-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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
? Link attached - How to validate SIN (Social Insurance Number)
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,1 0},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 " wrote: 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/...pim-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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
? Link attached - How to validate SIN (Social Insurance Number)
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
? Link attached - How to validate SIN (Social Insurance Number)
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
? Link attached - How to validate SIN (Social Insurance Number)
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 ###-###-###. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
? Link attached - How to validate SIN (Social Insurance Number)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Social Security Number format | Excel Worksheet Functions | |||
Link Text to an Associated Number | Excel Worksheet Functions | |||
(social security number) 000-00-0000 to 000000000 | Excel Discussion (Misc queries) | |||
how do i link a number of worksheets to one master worksheet? | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |