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.