HELP IN CREATING A FORMULA
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.
|