HELP IN CREATING A FORMULA

[Guest]

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.

 

[Guest]

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

 

[Bob Phillips]

=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)

 

[Guest]

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]

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)

 

[Guest]

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.

 

[Guest]

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),10),10)

(the previous formula calculated it to be 10)

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

XL2002, WinXP

 

[Bob Phillips]

=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)

 

[Guest]

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.

 

[Guest]

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


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

XL2002, WinXP