Thanks:  0
Likes:  0

1. ## 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. ## 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.

3. ## 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. ## 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

5. ## 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"))

6. ## 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.

Mike

7. ## 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.

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

8. ## 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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•