In creating a UPC code I have a number with 11 digits, and I need to generate
a 12th digit which is called a check character.
eg. 01234567890 _

Step 1) starting at the left add all characters in the odd positions (first
from the left, third from the left, ect.) eg. 0+2+4+6+8+0=20.

Step2) multiply the sum by 3. 20 x 3 = 60

Step 3) Starting from the left add all the characters in the even positions
(second from left, fourth from left, etc.) eg. 1+3+5+7+9 = 25

Step 4) Add the product of step 2 to the sum of step 3
60 + 25 = 85

Step 5) The check character is the smallest number which, when added to the
sum of Step 4, produces a multiple of ten.
Therefore the check character of the sample is - 5
85 + 5 = 90, a multiple of ten.

Try something like this:

With a starting value in A1

This formula adds the Check Digit to that value:
B1:
=A1&10-MOD(SUMPRODUCT(--MID(A1,{1,3,5,7,9,11},1))*3+SUMPRODUCT(--MID(A1,{2,4,6,8,10},1)),10)

If you will use that formula quite a bit, you may want to set up a User
Defined Function in a VBA gerenal module...

or

Set up a Named Calculation:
With B1 selecteed
From the Excel main menu:
<insert<name<define
Names in Workbook: AddCheckDigit
Refers to:
=A1&10-MOD(SUMPRODUCT(--MID(A1,{1,3,5,7,9,11},1))*3+SUMPRODUCT(--MID(A1,{2,4,6,8,10},1)),10)

Once that is done, entering =AddCheckDigit in any cell will calculate the
base-plus-check-digit for the cell to the left.

Example:
A2: 12345678901
B2: =AddCheckDigit
B2 returns 12345678901

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"paul" wrote:

In creating a UPC code I have a number with 11 digits, and I need to generate
a 12th digit which is called a check character.
eg. 01234567890 _

Step 1) starting at the left add all characters in the odd positions (first
from the left, third from the left, ect.) eg. 0+2+4+6+8+0=20.

Step2) multiply the sum by 3. 20 x 3 = 60

Step 3) Starting from the left add all the characters in the even positions
(second from left, fourth from left, etc.) eg. 1+3+5+7+9 = 25

Step 4) Add the product of step 2 to the sum of step 3
60 + 25 = 85

Step 5) The check character is the smallest number which, when added to the
sum of Step 4, produces a multiple of ten.
Therefore the check character of the sample is - 5
85 + 5 = 90, a multiple of ten.

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

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paul" wrote in message
...
In creating a UPC code I have a number with 11 digits, and I need to
generate
a 12th digit which is called a check character.
eg. 01234567890 _

Step 1) starting at the left add all characters in the odd positions
(first
from the left, third from the left, ect.) eg. 0+2+4+6+8+0=20.

Step2) multiply the sum by 3. 20 x 3 = 60

Step 3) Starting from the left add all the characters in the even
positions
(second from left, fourth from left, etc.) eg. 1+3+5+7+9 = 25

Step 4) Add the product of step 2 to the sum of step 3
60 + 25 = 85

Step 5) The check character is the smallest number which, when added to
the
sum of Step 4, produces a multiple of ten.
Therefore the check character of the sample is - 5
85 + 5 = 90, a multiple of ten.

Bob

Your formula has a type...it misses the 11th character (number).

This formula:
=10-MOD(SUMPRODUCT(--MID(A17,{1,3,5,7,9},1))*3+SUMPRODUCT(--MID(A17,2,4,6,8,10},1)),10)

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

(Also....I don't think you need to CSE in this case)

***********
Regards,
Ron

XL2002, WinXP


"Bob Phillips" wrote:

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

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paul" wrote in message
...
In creating a UPC code I have a number with 11 digits, and I need to
generate
a 12th digit which is called a check character.
eg. 01234567890 _

Step 1) starting at the left add all characters in the odd positions
(first
from the left, third from the left, ect.) eg. 0+2+4+6+8+0=20.

Step2) multiply the sum by 3. 20 x 3 = 60

Step 3) Starting from the left add all the characters in the even
positions
(second from left, fourth from left, etc.) eg. 1+3+5+7+9 = 25

Step 4) Add the product of step 2 to the sum of step 3
60 + 25 = 85

Step 5) The check character is the smallest number which, when added to
the
sum of Step 4, produces a multiple of ten.
Therefore the check character of the sample is - 5
85 + 5 = 90, a multiple of ten.

So it is, I just copied the OPs number which was 10 digits. Thanks.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ron Coderre" wrote in message
...
Bob

Your formula has a type...it misses the 11th character (number).

This formula:

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

Should be:

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

(Also....I don't think you need to CSE in this case)

***********
Regards,
Ron

XL2002, WinXP


"Bob Phillips" wrote:


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

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paul" wrote in message
...
In creating a UPC code I have a number with 11 digits, and I need to
generate
a 12th digit which is called a check character.
eg. 01234567890 _

Step 1) starting at the left add all characters in the odd positions
(first
from the left, third from the left, ect.) eg. 0+2+4+6+8+0=20.

Step2) multiply the sum by 3. 20 x 3 = 60

Step 3) Starting from the left add all the characters in the even
positions
(second from left, fourth from left, etc.) eg. 1+3+5+7+9 = 25

Step 4) Add the product of step 2 to the sum of step 3
60 + 25 = 85

Step 5) The check character is the smallest number which, when added
to
the
sum of Step 4, produces a multiple of ten.
Therefore the check character of the sample is - 5
85 + 5 = 90, a multiple of ten.

Thank you very much Bob and Ron...the formula works.
I had to change it slightly because I have each digit in a seprarate
cell...but after studing the brilliant formula I was able to figure it out.
Here's a kicker I need to add to the formula so that if a result is 10 then
the 12th. number should be equal to 0....the 12th. number has to be a single
digit number.

any thoughts??????
thanks in advance.



ob Phillips" wrote:

So it is, I just copied the OPs number which was 10 digits. Thanks.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ron Coderre" wrote in message
...
Bob

Your formula has a type...it misses the 11th character (number).

This formula:

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

Should be:

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

(Also....I don't think you need to CSE in this case)

***********
Regards,
Ron

XL2002, WinXP


"Bob Phillips" wrote:


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

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paul" wrote in message
...
In creating a UPC code I have a number with 11 digits, and I need to
generate
a 12th digit which is called a check character.
eg. 01234567890 _

Step 1) starting at the left add all characters in the odd positions
(first
from the left, third from the left, ect.) eg. 0+2+4+6+8+0=20.

Step2) multiply the sum by 3. 20 x 3 = 60

Step 3) Starting from the left add all the characters in the even
positions
(second from left, fourth from left, etc.) eg. 1+3+5+7+9 = 25

Step 4) Add the product of step 2 to the sum of step 3
60 + 25 = 85

Step 5) The check character is the smallest number which, when added
to
the
sum of Step 4, produces a multiple of ten.
Therefore the check character of the sample is - 5
85 + 5 = 90, a multiple of ten.

If your 11 digits are in A1:K1

Example:
A1: 0
B1: 1
C1: 2
D1: 3
E1: 4
F1: 5
G1: 6
H1: 7
I1: 8
J1: 4
K1: 0

This formula calculates the Check Digit for those numbers to be 0:
L1: =MOD(10-MOD(SUM(A1,C1,E1,G1,I1,K1)*3+SUM(B1,D1,F1,H1,J1),1 0),10)

(the previous formula calculated it to be 10)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"paul" wrote:

Thank you very much Bob and Ron...the formula works.
I had to change it slightly because I have each digit in a seprarate
cell...but after studing the brilliant formula I was able to figure it out.
Here's a kicker I need to add to the formula so that if a result is 10 then
the 12th. number should be equal to 0....the 12th. number has to be a single
digit number.

any thoughts??????
thanks in advance.



ob Phillips" wrote:

So it is, I just copied the OPs number which was 10 digits. Thanks.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ron Coderre" wrote in message
...
Bob

Your formula has a type...it misses the 11th character (number).

This formula:

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

Should be:

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

(Also....I don't think you need to CSE in this case)

***********
Regards,
Ron

XL2002, WinXP


"Bob Phillips" wrote:


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

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paul" wrote in message
...
In creating a UPC code I have a number with 11 digits, and I need to
generate
a 12th digit which is called a check character.
eg. 01234567890 _

Step 1) starting at the left add all characters in the odd positions
(first
from the left, third from the left, ect.) eg. 0+2+4+6+8+0=20.

Step2) multiply the sum by 3. 20 x 3 = 60

Step 3) Starting from the left add all the characters in the even
positions
(second from left, fourth from left, etc.) eg. 1+3+5+7+9 = 25

Step 4) Add the product of step 2 to the sum of step 3
60 + 25 = 85

Step 5) The check character is the smallest number which, when added
to
the
sum of Step 4, produces a multiple of ten.
Therefore the check character of the sample is - 5
85 + 5 = 90, a multiple of ten.

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paul" wrote in message
...
Thank you very much Bob and Ron...the formula works.
I had to change it slightly because I have each digit in a seprarate
cell...but after studing the brilliant formula I was able to figure it
out.
Here's a kicker I need to add to the formula so that if a result is 10
then
the 12th. number should be equal to 0....the 12th. number has to be a
single
digit number.

any thoughts??????
thanks in advance.



ob Phillips" wrote:

So it is, I just copied the OPs number which was 10 digits. Thanks.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ron Coderre" wrote in message
...
Bob

Your formula has a type...it misses the 11th character (number).

This formula:


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

Should be:


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

(Also....I don't think you need to CSE in this case)

***********
Regards,
Ron

XL2002, WinXP


"Bob Phillips" wrote:



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

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paul" wrote in message
...
In creating a UPC code I have a number with 11 digits, and I need
to
generate
a 12th digit which is called a check character.
eg. 01234567890 _

Step 1) starting at the left add all characters in the odd
positions
(first
from the left, third from the left, ect.) eg. 0+2+4+6+8+0=20.

Step2) multiply the sum by 3. 20 x 3 = 60

Step 3) Starting from the left add all the characters in the even
positions
(second from left, fourth from left, etc.) eg. 1+3+5+7+9 = 25

Step 4) Add the product of step 2 to the sum of step 3
60 + 25 = 85

Step 5) The check character is the smallest number which, when
added
to
the
sum of Step 4, produces a multiple of ten.
Therefore the check character of the sample is - 5
85 + 5 = 90, a multiple of ten.

Yes it does help Ron....brilliant....thanks to you and Bob again.
What field are you guys in? You all must use excel all day long...
Thanks again.

"Ron Coderre" wrote:

If your 11 digits are in A1:K1

Example:
A1: 0
B1: 1
C1: 2
D1: 3
E1: 4
F1: 5
G1: 6
H1: 7
I1: 8
J1: 4
K1: 0

This formula calculates the Check Digit for those numbers to be 0:
L1: =MOD(10-MOD(SUM(A1,C1,E1,G1,I1,K1)*3+SUM(B1,D1,F1,H1,J1),1 0),10)

(the previous formula calculated it to be 10)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"paul" wrote:

Thank you very much Bob and Ron...the formula works.
I had to change it slightly because I have each digit in a seprarate
cell...but after studing the brilliant formula I was able to figure it out.
Here's a kicker I need to add to the formula so that if a result is 10 then
the 12th. number should be equal to 0....the 12th. number has to be a single
digit number.

any thoughts??????
thanks in advance.



ob Phillips" wrote:

So it is, I just copied the OPs number which was 10 digits. Thanks.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ron Coderre" wrote in message
...
Bob

Your formula has a type...it misses the 11th character (number).

This formula:

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

Should be:

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

(Also....I don't think you need to CSE in this case)

***********
Regards,
Ron

XL2002, WinXP


"Bob Phillips" wrote:


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

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paul" wrote in message
...
In creating a UPC code I have a number with 11 digits, and I need to
generate
a 12th digit which is called a check character.
eg. 01234567890 _

Step 1) starting at the left add all characters in the odd positions
(first
from the left, third from the left, ect.) eg. 0+2+4+6+8+0=20.

Step2) multiply the sum by 3. 20 x 3 = 60

Step 3) Starting from the left add all the characters in the even
positions
(second from left, fourth from left, etc.) eg. 1+3+5+7+9 = 25

Step 4) Add the product of step 2 to the sum of step 3
60 + 25 = 85

Step 5) The check character is the smallest number which, when added
to
the
sum of Step 4, produces a multiple of ten.
Therefore the check character of the sample is - 5
85 + 5 = 90, a multiple of ten.

Thanks for the feedback....I'm glad that worked for you.


***********
Regards,
Ron

XL2002, WinXP


"paul" wrote:

Yes it does help Ron....brilliant....thanks to you and Bob again.
What field are you guys in? You all must use excel all day long...
Thanks again.

"Ron Coderre" wrote:

If your 11 digits are in A1:K1

Example:
A1: 0
B1: 1
C1: 2
D1: 3
E1: 4
F1: 5
G1: 6
H1: 7
I1: 8
J1: 4
K1: 0

This formula calculates the Check Digit for those numbers to be 0:
L1: =MOD(10-MOD(SUM(A1,C1,E1,G1,I1,K1)*3+SUM(B1,D1,F1,H1,J1),1 0),10)

(the previous formula calculated it to be 10)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"paul" wrote:

Thank you very much Bob and Ron...the formula works.
I had to change it slightly because I have each digit in a seprarate
cell...but after studing the brilliant formula I was able to figure it out.
Here's a kicker I need to add to the formula so that if a result is 10 then
the 12th. number should be equal to 0....the 12th. number has to be a single
digit number.

any thoughts??????
thanks in advance.



ob Phillips" wrote:

So it is, I just copied the OPs number which was 10 digits. Thanks.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ron Coderre" wrote in message
...
Bob

Your formula has a type...it misses the 11th character (number).

This formula:

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

Should be:

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

(Also....I don't think you need to CSE in this case)

***********
Regards,
Ron

XL2002, WinXP


"Bob Phillips" wrote:


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

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paul" wrote in message
...
In creating a UPC code I have a number with 11 digits, and I need to
generate
a 12th digit which is called a check character.
eg. 01234567890 _

Step 1) starting at the left add all characters in the odd positions
(first
from the left, third from the left, ect.) eg. 0+2+4+6+8+0=20.

Step2) multiply the sum by 3. 20 x 3 = 60

Step 3) Starting from the left add all the characters in the even
positions
(second from left, fourth from left, etc.) eg. 1+3+5+7+9 = 25

Step 4) Add the product of step 2 to the sum of step 3
60 + 25 = 85

Step 5) The check character is the smallest number which, when added
to
the
sum of Step 4, produces a multiple of ten.
Therefore the check character of the sample is - 5
85 + 5 = 90, a multiple of ten.