b. Add another panel to the spreadsheet that takes as input a six-digit account number and uses the check digit formula in part a to test whether or not the account number is valid. Your solution should look like this:
Solution: This formula in the “Valid? (Y/N)” column will test any six-digit account number:
Ideally, all a user should need to do is input a six-digit account number in the “Account number” column and then the spreadsheet will display the individual digits in the appropriate columns. This requires the following formulas to parse the six-digit account number entered in cell B20 (you can copy these formulas down for as many rows as desired):
“First digit” column: =VALUE(LEFT(B20))
“Second digit” column: =VALUE(LEFT(RIGHT(B20,5)))
“Third digit” column: =VALUE(LEFT(RIGHT(B20,4)))
“Fourth digit” column: =VALUE(LEFT(RIGHT(B20,3)))
“Fifth digit” column: =VALUE(LEFT(RIGHT(B20,2)))
“Check digit” column: =VALUE(RIGHT(B20))
Alternatively, if you only want a two-column display with the account number and the “Valid? (Y/N)” column, you could modify the formula in the “Valid? (Y/N)” column so that the cell references were replaced as follows:
