Results 1 to 8 of 8

VIN calculations

This is a discussion on VIN calculations within the Excel Questions forums, part of the Question Forums category; Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; ...

  1. #1
    New Member
    Join Date
    Jul 2010
    Posts
    4

    Default VIN calculations

    Hi, I am the general manager of a trailer manufacturing plant and I need some help calculating Vehicle Identification Numbers in Excel.

    First, find the numerical value associated with each letter in the VIN. (I, O and Q are not allowed.) Digits use their own values.

    A=1, B=2, C=3, D=4, E=5, F=6, G=7, H=8,
    J=1, K=2, L=3, M=4, N=5, P=7, R=9,
    S=2, T=3, U=4, V=5, W=6, X=7, Y=8, Z=9

    Second, look up the weight factor for each position in the VIN except the 9th (the position of the check digit).

    1st=8 2nd=7 3rd=6 4th=5 5th=4 6th=3
    7th=2 8th=10 10th=9 11th=8 12th=7 13th=6
    14th=5 15th=4 16th=3 17th=2
    Example;
    1
    T
    9
    B
    V
    2
    3
    2
    Check Digit
    A
    U
    9
    2
    8
    0
    5
    6

    1
    3
    9
    2
    5
    2
    3
    2
    1
    4
    9
    2
    8
    0
    5
    6
    associated values
    8
    7
    6
    5
    4
    3
    2
    10
    9
    8
    7
    6
    5
    4
    3
    2
    weight factors
    8
    21
    54
    10
    20
    6
    6
    20
    9
    32
    63
    12
    40
    4
    15
    12
    products

    The check digit is then calculated by dividing the sum of products which in this case is 332 by 11 which give me 30 and 2/11 the remainder of 2 then becomes the check digit. So the VIN in this case would be
    1
    T
    9
    B
    V
    2
    3
    2
    2
    A
    U
    9
    2
    8
    0
    5
    6
    If this calculation produces a whole number the check digit = 0 and if it produces 10/11 it = X
    Using v-lookup and simple formulas I can create the VIN’s with the exception of the check digit, is there away to calculate this and have it placed in its position in the VIN? I will be having our secretary producing these VIN’s and would like to keep it to where all she has to do is enter the length of the trailer and the year it was produced. Any help would be appreciated.
    Thanks Mike

  2. #2
    Board Regular indiantrix's Avatar
    Join Date
    Oct 2002
    Location
    Philadelphia 'Burbs
    Posts
    943

    Default Re: VIN calculations

    Hi Timberline,
    First off, I calculate a different sum of the products. In column 15 you multiply 0 by 4 and get a 4. My calculation puts this portion at 0, and my sum of the products becomes 328. Anyway, here is my formula to generate the checksum digit:
    =IF(MOD(SUMPRODUCT(B4:I4,B5:I5)+SUMPRODUCT(K4:R4,K5:R5),11)=10,"X",MOD(SUMPRODUCT(B4:I4,B5:I5)+SUMPRODUCT(K4:R4,K5:R5),11))
    In this example, this equates to 9.
    Have I missed something, or are we on the right path here?
    Larry.
    Last edited by indiantrix; Jul 20th, 2010 at 09:24 PM. Reason: extended formula to include 0 and X

  3. #3
    New Member
    Join Date
    Jul 2010
    Posts
    4

    Default Re: VIN calculations

    Hi indiantrix,
    Thanks for the correction, you are right it should be a 0 in column 15, it has been a long day. That being said it looks like to me that not only are you on the right path but you are paving the road. Would you mind taking a look at the spread sheet I have made up and tell me if there is a better way etc... I just want to make this as simple as possible for the lady that is going to be generating the VIN's and as fool proof as possible, I have already had to deal with the Federal Government because some incorrect VIN's were issued. Please email me mike@trccamps.com and I will email you an attachment of the spread sheet.
    Thanks
    Mike

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,485

    Default Re: VIN calculations

    Hi Mike

    It's not a good idea to post your email, because of spam.

    I wrote the table with the value of the characters in columns E:F. Column E has text values, including the digits at the end (format the column as text before entering the digits).

    In B2:

    =SUBSTITUTE(A2,"_",SUBSTITUTE(MOD(SUM((MID(A2,TRANSPOSE(ROW(INDIRECT("1:17"))),1)=$E$2:$E$34)*$F$2:$F$34*{8,7,6,5,4,3,2,10,0,9,8,7,6,5,4,3,2}),11),10,"X"))

    This is an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

    Copy down

    Please test with some VIN's and if you find an error post it.


     ABCDEFG
    1       
    21T9BV232_AU9280561T9BV2329AU928056  A1 
    31M8GDM9A_KP0427881M8GDM9AXKP042788  B2 
    41FALP62W_WH1287031FALP62W7WH128703  C3 
    5    D4 
    6    E5 
    7    F6 
    8    G7 
    9    H8 
    10    J1 
    11    K2 
    12    L3 
    13    M4 
    14    N5 
    15    P7 
    16    R9 
    17    S2 
    18    T3 
    19    U4 
    20    V5 
    21    W6 
    22    X7 
    23    Y8 
    24    Z9 
    25    00 
    26    11 
    27    22 
    28    33 
    29    44 
    30    55 
    31    66 
    32    77 
    33    88 
    34    99 
    35       
    [VIN.xlsm]Sheet2
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,485

    Default Re: VIN calculations

    The same formula, but non-array, entered as usual just with ENTER and not with the CTRL+SHIFT+ENTER. In B2:


    =SUBSTITUTE(A2,"_",SUBSTITUTE(MOD(SUMPRODUCT((MID(A2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17},1)=$E$2:$E$34)*$F$2:$F$34*{8,7,6,5,4,3,2, 10,0,9,8,7,6,5,4,3,2}),11),10,"X"))
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #6
    New Member
    Join Date
    Jul 2010
    Posts
    4

    Default Re: VIN calculations

    ****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"> Thanks PGC,
    I have set up a spread sheet that through v-lookup pulls all of the information for the VIN, model year, trailer type, length, sequence of production, etc… These numbers along with the manufacture ID number make up the VIN with the exception of the check digit. There are only six digits that will change in the calculation of the check digit. The model year (yearly change) positions 6&7, Length position 10, and sequence of production position 15, 16, &17. What I have done is a simple equation for this to calculate the product of the VIN multiplied by the various weights.
    1T9BV2322AU928058
    =SUM((D3*8)+(3*7)+(F3*6)+(2*5)+(5*4)+(I3*3)+(J3*2)+(K3*10)+(1*9)+(4*8)+(O3*7)+(P3*6)+(Q3*5)+(R3*4)+(S3*3)+(T3*2))
    The sum of this equation is 332 which is then divided by 11 giving me 30 and 2/11 making the check digit 2 which is then put in as the 9th digit.
    Customer
    Trailer Size
    VIN Calculation
    Check Digit Calculation
    WMI
    WMI
    Inventory
    18
    1
    T
    9
    B
    V
    2
    3
    2
    2
    A
    U
    9
    2
    8
    0
    5
    8
    332
    30 2/11
    2
    Inventory
    14
    1
    T
    9
    B
    V
    1
    9
    2
    2
    A
    U
    9
    2
    8
    0
    5
    9
    343
    31 2/11
    2
    Inventory
    12
    1
    T
    9
    B
    V
    1
    7
    2
    5
    A
    U
    9
    2
    8
    0
    6
    0
    324
    29 5/11
    5
    Inventory
    16
    1
    T
    9
    B
    V
    2
    1
    2
    9
    A
    U
    9
    2
    8
    0
    6
    1
    317
    28 9/11
    9

    1
    T
    9
    B
    V
    0
    0
    2
    0
    A
    U
    9
    2
    8
    0
    6
    2
    311
    28 3/11

    1
    T
    9
    B
    V
    0
    0
    2
    0
    A
    U
    9
    2
    8
    0
    6
    3
    313
    28 5/11

    1
    T
    9
    B
    V
    0
    0
    2
    0
    A
    U
    9
    2
    8
    0
    6
    4
    315
    28 7/11

    Here is the spread sheet I have been working on, once the trailer length is entered it will look up and add the 6 &7 digits. I will change the 10th digit at the first of the model year. So it is very simple with the exception of the check digit which at this point we have to manually put into the right hand column of the check digit calculation section and then the formula which is in the 9th digit column of =V3 which of course is the number we manually put in.
    I am just trying to make this as simple as I can for the lady who will be generating the VIN’s for our federally labels. I would like to get it to where all she has to do is enter the size of the trailer and that the spread sheet she works with looks basically like the one above minus the check digit calculation portion. This sure would be easier if I could just attach the spread sheet as good as you guys are at this you would have me helped out in no time.

    Thanks for your help,
    Mike

  7. #7
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,485

    Default Re: VIN calculations

    See if this helps. I used columns W:X as auxilliary, with a table with the correspondences you posted. Make sure the digits in W25:W34 are entered as text (first format the cells as text and then enter the digits). The numbers in column X are real numbers, not text (format the column X as General and then enter the numbers).

    The sum, in T3:

    =SUMPRODUCT(N(OFFSET($X$2,MATCH(CHOOSE({1;2},C3:J3,L3:S3)&"",$W$2:$W$34,0)-1,0))*{8,7,6,5,4,3,2,10;9,8,7,6,5,4,3,2})

    The check digit, in U3:

    =SUBSTITUTE(MOD(T3,11),10,"X")

    The check digit, in K3:

    =U3

    Copy the formulas in T3, U3 and K3 down.

    You can, if you want, not use the T3:U3 cells and just use a formula in K3. I just followed your layout.

    Please test.


    ABCDEFGHIJKLMNOPQRSTUVWXY
    1CustomerTrailer SizeVIN CalculationCheck Digit Calculation
    2WMIWMISumA1
    3Inventory181T9BV2322AU9280583322B2
    4Inventory141T9BV1922AU9280593432C3
    5Inventory121T9BV1725AU9280603245D4
    6Inventory161T9BV2129AU9280613179E5
    71T9BV0023AU9280623113F6
    81T9BV0025AU9280633135G7
    91T9BV0027AU9280643157H8
    10J1
    11K2
    12L3
    13M4
    14N5
    15P7
    16R9
    17S2
    18T3
    19U4
    20V5
    21W6
    22X7
    23Y8
    24Z9
    2500
    2611
    2722
    2833
    2944
    3055
    3166
    3277
    3388
    3499
    35
    [VIN.xlsm]Sheet1
    Last edited by pgc01; Jul 21st, 2010 at 03:11 PM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  8. #8
    New Member
    Join Date
    Jul 2010
    Posts
    4

    Default Re: VIN calculations

    Thanks PGC,
    This helped emensly. I understand the formula and how that you are calculating the VIN by converting the Alpha digits as part of the formula. I really don't need to do this. at this point in the construction of trailers, the only alpha character that changes is the one for the production year. I did however use the formula "The check it, in U3:
    =SUBSTITUTE(MOD(T3,11),10,"X") " that you provided. works great, moved some columns around, hide some others and now I have a simple spread sheet that all the secretary has to do is punch in the length of the trailer and just like magic it generates the VIN, give a nice neat list so that we can track the the trailers and VINs along with any customer info we need to all on the same sheet. It's great.
    Thanks so much, one less thing I have to worry about.
    Thanks again to both PGC and indiantrix,
    Mike

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
  •  


DMCA.com