Tags: assign, calculate, excel, microsoft, msdn, number, sheet, software, trailers, values, vin, vins

Assign values to letters

On Microsoft » Microsoft Excel

2,627 words with 2 Comments; publish: Thu, 22 May 2008 05:33:00 GMT; (30693.75, « »)

Hello I?m trying to set up an excel sheet to calculate VIN?s fo

trailers.

The VIN has 17 number/letters.

Number 9 is a ?check? number in the VIN, you get by taking al

numbers/letters and:

1) Take the assigned value, number or a letter converted to a number

2) Multiply by constant number

3) Add all there products

4) Divide by 11

5) Take the numerator of the reduced fraction and that is my 9t

number/letter

I would like to convert the letter to a number for a calculation tha

is required.

A=1

T=3

No real pattern, letters I,O,Q are not used

Is there a way assign a value to a letter then covert it to a number

if not already one?

And can I take just the numerator digit (reduced) from the cell, if i

has one?

Any help would be most appreciate

--

Message posted from http://www.ExcelForum.com

All Comments

Leave a comment...

  • 2 Comments
    • Hi

      to assign values to a letter you may use the following if there is no

      pattern in your assigment:

      =INDEX({1,3},MATCH(A1,{"A","T"},0))

      Cell A1 stores the actaul letter for which a value should be returned.

      Adapt the ranges to you needs

      --

      Regards

      Frank Kabel

      Frankfurt, Germany

      > Hello I'm trying to set up an excel sheet to calculate VIN's for

      > trailers.

      > The VIN has 17 number/letters.

      > Number 9 is a "check" number in the VIN, you get by taking all

      > numbers/letters and:

      >

      > 1) Take the assigned value, number or a letter converted to a number

      > 2) Multiply by constant number

      > 3) Add all there products

      > 4) Divide by 11

      > 5) Take the numerator of the reduced fraction and that is my 9th

      > number/letter

      >

      > I would like to convert the letter to a number for a calculation that

      > is required.

      > A=1

      > T=3

      > No real pattern, letters I,O,Q are not used

      >

      > Is there a way assign a value to a letter then covert it to a number,

      > if not already one?

      > And can I take just the numerator digit (reduced) from the cell, if

      it

      > has one?

      > Any help would be most appreciated

      >

      > --

      > Message posted from http://www.ExcelForum.com/

      #1; Thu, 22 May 2008 05:35:00 GMT
    • Thank you Frank, that worked out great.

      I matched numbers with numbers also

      =INDEX({1,3,1,3),MATCH(A1,{"A","T",1,3},0))

      so i could get a number back if one was entered.

      Thanks again

      --

      Message posted from http://www.ExcelForum.com

      #2; Thu, 22 May 2008 05:36:00 GMT