http://www.uc-council.org/ean_ucc_system/education_support/cdc.html

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

---

Request for Answer Clarification by captcliff-ga on 13 Oct 2006 23:27 PDT

Very well done. Thank you. 

It has been awhile since I used this service. I did not see a way to
pay you so I am assuming that the money is paid automatically now.

If someone is not familier with answers.google, how would they find it
as I do not see it listed under "more" any longer?

---

Clarification of Answer by maniac-ga on 14 Oct 2006 09:28 PDT

Hello Captcliff,

Thanks for the kind words.

About finding Google Answers, I suggest you take that question to the
editors / support staff for Google Answers directly.  You'll find the
email link at the bottom of each question page.

  --Maniac

Maniac-ga was quick, through, and explained the solution is such a way
that I could build on it to solve other questions. I was impressed!!!

if your value is on A1

you put

=RoundUp((value(mid(A1,1,1))+value(mid(A1,3,1))+value(mid(A1,5,1))+value(mid(A1,7,1))+value(mid(A1,9,1))+value(mid(A1,11,1)))*3+(value(mid(A1,2,1))+value(mid(A1,4,1))+value(mid(A1,6,1))+value(mid(A1,8,1))+value(mid(A1,10,1)))/10,0)*10-(value(mid(A1,1,1))+value(mid(A1,3,1))+value(mid(A1,5,1))+value(mid(A1,7,1))+value(mid(A1,9,1))+value(mid(A1,11,1)))*3+(value(mid(A1,2,1))+value(mid(A1,4,1))+value(m
id(A1,6,1))+value(mid(A1,8,1))+value(mid(A1,10,1)))

Just wanted to thank reinedd-ga for his contribution and comment.

Hi guys,

I looked at your formula and though it does solve the problem its a bit lengthy.

Try:

=MOD(10-MOD(SUMPRODUCT(--(MID(A1,{1,3,5,7,9,11},1)))*3+SUMPRODUCT(--(MID(A1,{2,4,6,8,10},1))),10),10)

Type the 11 digit code in cell A1, and enter this formaula in A2,
replicate the formula by copying down.  The formula may be modified
for 12 or 13 digit codes by  adding integers (odd or even) within the
brackets { } as appropriate. Use CONCATENATE to add the two text
strings together in A3 and you are done...

Hope this helps
jgbix