Pucinneli Supermarkets is similar to most other grocery store chains that use the 12-digit UPC code on packages to check out customers. For a variety of reasons, it is important that the computer systems using these codes validate them for accuracy and completeness. To perform this task, UPCs automatically include a €œcheck digit€ that can also be computed from the other digits in the code. Using the bar code shown in Figure 2-3 as an example, the system works as follows:

1. The UPC includes all numbers in the bar code, and therefore is 064200115896 for the example here. The length is 12 digits as required.
2. The check digit is the last digit in the code€”for example, a €œ6€ for this example.
3. Starting on the left, add the digits up to, but not including, the check digit in the odd-numbered positions (i.e., the numbers in the first, third, fifth, etc., positions) together. Multiply this sum by three.
4. Add the digits up to, but not including, the check digit in the even-numbered positions (i.e., the second, fourth, sixth numbers, etc.).
5. Add the values found in steps 3 and 4.
6. Examine the last digit of the sum. If it is 0, the computed check digit is also 0. If the last digit of the result is not zero, subtract this digit from 10. The answer is the computed check digit and must equal the last number in the UPC code.

UPC code: ........................... 064200115896
Length test: ..........................          Ok
Check digit is: .....................            06
Sum of odd digits ................           19
Sum of even digits ..............           17
Odd digits ×3 ......................            57
Sum ...................................            74
Last digit ............................            04
Computed check digit .......             06
Conclusion ........................    Valid number

To illustrate, suppose the UPC barcode is 064200115896 as shown. The steps for this example are
1. The length is 12 as required.
2. The last value is €œ6€ so this is the check digit.
3. Add the odd-position digits: 0 + 4 + 0 + 1 + 5 + 9 = 19. Multiply this sum by 3: 19 × 3 = 57.
4. Add the even-position digits: 6 + 2 + 0 + 1 + 8 = 17.
5. We add these two values together. The sum is 57 + 17 = 74.
6. The last digit in this sum is €œ4,€ the check digit is not 0, and we therefore subtract 4 from 10 to get €œ6.€ The computed value of €œ6€ found in step 5 matches the check digit €œ6€ in step 2, and we therefore conclude that this UPC code is valid.

Requirements:

Develop a spreadsheet to perform the tests described here and test your model with the following UPC codes: (1) 639277240453, (2) 040000234548, (3) 034000087884, (4) 048109352495, and (5) one UPC value of your own choosing (drawn from something you own or see). For each number, indicate whether the UPC number is valid or invalid. Include a print out of all your work, plus a copy of the formulas for at least one of your tests.
(1) You should enter your initial UPC code as €œtext€€”not a number.
(2) You can use Excel€™s LEN function to perform the desired length test and Excel€™s IF function to test whether the entered value passes it.
(3) You should use Excel€™s MID function to parse each digit for these computations.
(4) You can use Excel€™s IF test again to reach the conclusion (e.g., €œvalid number€).

Transcribed Image Text:

16 64200 "11589