Hi M. Jack,
To modify the formula to calculate the 13th digit (check digit) for a 13 digit UPC A code, you can use the following formula:
Formula:
=A1&MOD(10-MOD(SUMPRODUCT(MID(A1,IF(MOD(ROW($1:$12),2)=0,2,1)+INT((ROW($1:$12)-1)/2),1)*IF(MOD(ROW($1:$12),2)=0,3,1)),10),10)
Here's a breakdown of the formula:
- A1& - This concatenates the original 12 digits with the calculated 13th digit.
- SUMPRODUCT - This function multiplies each digit by a corresponding weight and then sums the products.
- MID - This function extracts a single digit from the original 12 digits.
- IF - This function checks if the row number is even or odd and returns the appropriate weight.
- MOD - This function checks if the row number is even or odd and returns the appropriate weight.
- INT - This function rounds down the row number to the nearest integer.
- 10-MOD - This function subtracts the sum from the nearest higher multiple of 10 and returns the difference.
- MOD - This function calculates the remainder when the sum is divided by 10.
- 10-MOD - This function subtracts the remainder from 10 and returns the difference.
Hope this helps!