Hello Captcliff,
Hmm. I tried the comment from reinedd, but it did not work on my system.
Let's work out a few different ways to solve this.
[1] If you can use five cells - you can reproduce the steps described
at the web site you referenced in the following way:
- A1 has your 11 digit value (step 1)
- B1 has the sum of odd digits (step 2)
=MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1)
- C1 has 2x the results of step 2
=B1*3
- D1 has 1x the even digits (step 4)
=MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1)
- E1 has the sum of steps 3 & 4 (step 5)
=C1+D1
- F1 computes the check digit (step 6)
=IF(MOD(E1,10)=0,0,10-MOD(E1,10))
The last computes the remainder after dividing E1 by 10, if zero -
return zero, otherwise subtract that remainder from 10 which is the
smallest number that will round the result of step 5 up to a multiple
of 10.
You can compress this quite easily into fewer cells - two is perhaps
the most efficient with
[note - I relocated the "two cells" to allow an easy comparison
between the two methods]
[2] The "two cell" solution
- A1 has the 11 digit number
- H1 has steps 2 though 5 - split into two lines, combine into a single cell.
=3*(MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1))
+MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1)
- G1 has
=IF(MOD(H1,10)=0,0,10-MOD(H1,10))
In this way, G1 and F1 are adjacent and should have the same result
for easy comparison.
If you must do this with a single formula, the following can be used
which repeats the formula in H1 twice in the IF statement. Its been
split into several lines to make it a little more clear the parts of
the formula - combine into a single line / single cell for proper
operation.
=IF(MOD(3*(MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1))
+MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1),10)=0,0,10-
MOD(3*(MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1))
+MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1),10))
Let's walk through the parts of the formula so you can build similar
formulas for other needs. I suggest you search the Excel online help
for additional information on these functions.
The IF function takes three values (V1, V2, V3). If V1 is true, the
result is V2; if V1 is false, the result is V3.
The MOD function takes two values (V1, V2). The result is the
remainder of dividing V1/V2. In our case, we're dividing by 9, so the
result is between 0 and 9 inclusive.
The MID function takes three values (V1, V2, V3). MID normally
operates on a string valued V1 - but Excel will also convert a number
to a string automatically. The result is the sequence of V3 letters of
V1 starting at position V2. In this example, we always extract a
single letter (V3=1) at a specific position - different for each MID
function call.
The + operator normally operates on numbers, but Excel also
automatically converts strings (letters) to numbers so the digits of
the value are summed properly.
I've compared several values to the results from that web page you
referenced. The formulas (all three forms) appear to work quite well -
and produce consistent results.
If you have any problems understanding the answer or if you need more
information on some part of the question I did not adequately cover -
please make a clarification request. I would be glad to help further.
Good luck with your work.
--Maniac |