Pulling Digits from Decimals/Fractions

[NATHANW]

Is there a way to get the first two digits after a decimal?

Example: 26.636363… Need the .63

Or get the numerator from a reduced fraction?

Example: 26 7/11 Need the 7


If I could get the decimal it will then be turned into a number between
0-9. In the case of the 26.636363… it would be a 7.

If I could get the numerator it would just be 7, which happens to be
the what I need.


This is one step for calculating VIN number for trailers

 

[Frank Kabel]

Hi
1.a. get the first two decimals (without rounding): Try
=KÜRZEN(A1*100)/100
1.b get the first two decimals with rounding
=ROUND(A1*100,0)/100

2. to get the rest of a division try
=MOD(A1,11) e.g. in your case
=MOD(293,11) = 7

 

[NATHANW]

Hello again Frank,

Can’t seem to get them to work.

KÜRZEN, do you know what that is in English?

Here is the key I must us for converting the decimals,

.09 = 1
.18 = 2
.27 = 3
.38 = 4
.45 = 5
.54 = 6
.63 = 7
.72 = 8
.81 = 9
.90 = X
.00 = 0

Every number I will be using, the first 2 digits to the right of th
decimal point will be in the first column of the key chart. Digits t
the left of the decimal point could be anything. I need to take th
number I have and turn it to what’s on the right side of the chart.

So I just need to be able to single out the 2 digits to the right

 

[Frank Kabel]

Hi
sorry forgot to translate: 'KÜRZEN' = 'TRUNC' in English

In respect of you key conversion: How did you get theses numbers (seems
that a simple division by 9 would get you the desired result, with the
exception for '90').
Maybe you can post the basis number and the algorithm you want to use

 

[NATHANW]

'How to calculate the VIN' (http://www.menschmfg.com/Assets/VIN.pdf)
Its PDF file 114k, probably better than me explaining.

.'Example' (http://www.menschmfg.com/Assets/Sample.gif) VIN

I'm just stuck on the check digit, everything else i can handle

 

[Frank Kabel]

Hi
if you just have to get the check digit for a specific number use
=MOD(sum_value,11)
e.g. using the example from the PDF file:
=MOD(411,11) returns 4

 

[NATHANW]

Unfortunately it is not a specific number, but I did come up wit
something that works. May not be the simplest or best way, but i
works.

=INDEX({1,2,3,4,5,6,7,8,9,"X",0},MATCH(ROUND(((SUM(B52:R52)/11(ROUNDDOWN(SUM(B52:R52)/11*1,0)/1))*11),1),{1,2,3,4,5,6,7,8,9,10,0},0))

Add products and divide by 11
Round the answer down, elimination the fraction
Subtract the whole number from the mixed number, leaving just th
fraction
Multiply by 11 to get just your numerator
Round to get ride of decimals
Use the Index/Match command to assign its value, 6 is 6, but if it wa
a 10 it would be converted to an X



Thank you for your help and tim