HELP IN CREATING A FORMULA

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
=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)
 
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
 
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)
 
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.
 
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
 
=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)
 
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.
 
Thanks for the feedback....I'm glad that worked for you.


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

XL2002, WinXP
 
Back
Top