Excel Problem The Moose Wings Cooperative Flight Club owns a number of airplanes and gliders. It serves fewer than 2,000 members, who are numbered sequentially from the founder, Tom Eagle (0001), to the newest member, Jacques Noveau (1368). Members rent the flying machines by the hour, and all must be returned on the same day. The following six records were among those entered for the flights taken on September 1, 2010: Member #| Flight DateMM/DD/YY| Plane Used| Takeoff time| Landing time| 1234| 09/10/10| G| 6:25| 8:46| 4111| 09/01/10| C| 8:49| 10:23| 1210| 09/01/10| P| 3:42| 5:42| 023| 09/01/10| X| 1:59| 12:43| 012A| 09/01/10| P| 12:29| 15:32| 0999| 09/01/10| L| 15:31| 13:45| Valid plane codes (plane used column): C = Cessna, G = glider, L = Lear Jet, P = Piper Cub) a. Identify and describe any errors in the data. Five of the six records contain errors as follows: 1st – Wrong date is used (September 10 instead of September 1). 2nd – Member number is outside range (4111 is greater than 1368). 4th – Plane code X is not valid. 5th – Member number contains a character (A). 6th – Plane landing time (13:45) is earlier than the take off time (15:31). b.

For each of the five data fields, suggest one or more input edit controls that could be used to detect input errors. Field 1 – Member number: * Range check to verify that the field contains only four digits within the range of 0001 to 1368. * Validity check on member number if a file of valid member numbers is maintained. Field 2 – Date of flight start: * Check that day, month, and year corresponds to the current date. * Field check that value is a date Field 3 – Plane used: * Validity check that character is one of the legal characters to describe a plane (G, C, P, or L). Field check to verify that only a single character is used. ) Field 4 – Time of take off: * Field check to verify that the field contains valid time format. Field 5 – Time of landing: * Field check to verify that the field contains valid time format. * Reasonableness test that field 5 is greater than field 4. c. Enter the data in a spreadsheet and create appropriate controls to prevent or at least detect the input errors. Field 1 – Member number: * Range check to verify that the field contains only four digits within the range of 0001 to 1368.

Using the Data Validation tool in Excel (under the Data tab) this range check could be programmed as follows: Alternatively, the following IF statement would do the same thing: =IF(AND(A4>0,A4<1369),””,”Error: Values must be between 1 and 1368”) The first argument tests whether the cell value for member numbers is a whole number that is greater than 0 and less than 1369 (you could also code this as greater than or equal to 1 and less than or equal to 1368). If the test is true, no error message is displayed (the two double quotes in argument 2 of the IF function).

If the test is false, the error message displayed in the quotes in the third argument is displayed. * Validity check on member number if a file of valid member numbers is maintained. Using the data validation tool, the validity check would be programmed as follows: This tools says that the value input must match a list of legal values that are found in cells A4:A7 (which would hold the values C, G, L and P) Alternatively, the following IF statement would perform the same test: =IF(OR(G8=”C”,G8=”G”,G8=”L”,G8=”P”),””,”Error: Invalid plane code”)

The OR test checks the value of cell G8 against the four permissible values. If any match, the test is true and nothing is displayed. If none of the four tests matches, then the error message in the third argument is displayed. Field 2 – Date of flight start: * Check that day, month, and year correspond to the current date. In the data validation tool, you would select the cells you want to test and enter the date value you want to compare to, as follows: Field 3 – Plane used: * Validity check that character is one of the legal characters to describe a plane (G, C, P, or L).

This tools says that the value input must match a list of legal values that are found in cells A4:A7 (which would hold the values C, G, L and P) Alternatively, the following IF statement would perform the same test: =IF(OR(G8=”C”,G8=”G”,G8=”L”,G8=”P”),””,”Error: Invalid plane code”) The OR test checks the value of cell G8 against the four permissible values. If any match, the test is true and nothing is displayed. If none of the four tests matches, then the error message in the third argument is displayed. * Check that only a single character is used. (field check)

Alternatively, the following IF statement also checks this: =IF(LEN(S4)=1,””,”Plane character must contain only one character”) The LEN function returns the length of a text string. In this case, it checks the cell containing the plane code to verify that it is only 1 letter. If the test is true, no error message is displayed (the second argument of the IF statement has two double-quotes). If the test is false, it displays the error message in the third argument of the IF function. Field 4 – Time of take off: * Field check to verify that the field contains valid time format.

Field 5 – Time of landing: * Field check to verify that the field contains valid time format. Same as for field 4 * Reasonableness test that field 5 is greater than field 4. a. Suggest other controls to minimize the risk of input errors. * prompting to request each required input item. * preformatting to display an input form including all required input items. * completeness check on each input record to ensure all item have been entered. * default values such as today’s date for the flight date. * closed-loop verification (member name would appear immediately after the member number) 10. 0 Excel Problem Creating and testing check digits. a. Create a spreadsheet that will take as input a five-digit account number and calculate a check digit using this formula: (5 x left-most digit + 4 x next digit + 3 x third digit + 2 x fourth digit + fifth digit) modulus division by 7. (Modulus division returns the remainder – for example: 11 modulus division by 3 = 2). The check digit then becomes the 6th (right-most) digit in the account number. Your spreadsheet should look like this: Explanation: the formula for the check digit calculation is =MOD(((5*C4)+(4*D4)+(3*E4)+(2*F4)+G4),7).

The MOD function is one of Excel’s built-in “Math&Trig” functions. It takes two arguments: the number you are dividing, and the divisor. In this case, the number is a formula (5 x the first digit in cell C4 plus 4 x the second digit in cell D4 plus 3 x the third digit in cell E4 plus 2 x the second digit in cell F4 plus the last digit from cell G4). This result is then divided by 7, and the MOD function returns the remainder. Thus for the first row, the formula yields: (5 x 1) + (4 x 2) + (3 x 3) + (2 x 4) + 5 = 35.

Dividing 35 by 7 yields 5 with a remainder of 0. Therefore, the MOD function returns a value of 0 for the check digit. Appending the zero to raw account number yields the actual account number of 123450. Students should use the text formulas (LEFT and RIGHT) plus the VALUE formula to parse the raw account number from column B in order to automatically fill in columns C through G as follows: * Column C: =VALUE(LEFT(B4)). The LEFT function with one argument is used to return the left-most digit from reference cell (B4). The result is a text value of 1.

Then the VALUE function converts that text into the number 1. * Column D: =VALUE(LEFT(RIGHT(B4,4))). The combination of LEFT and RIGHT functions is used to return the second digit from the left in the reference cell (B4). The RIGHT function can take two arguments: the cell containing the numeric value to be manipulated (in this case B4) and the number of digits, beginning with the rightmost one, to return. In this case, it returns the 4 right-most digits: 2345. Next, the LEFT function lops off the left-most digit in that string, yielding text string of “2”.

Finally, the VALUE function converts that text into the number 2. * Column E: =VALUE(LEFT(RIGHT(B4,3))). The combination of LEFT and RIGHT functions is used to return the third digit in reference cell (B4). The RIGHT function can take two arguments: the cell containing the numeric value to be manipulated (in this case B4) and the number of digits, beginning with the rightmost one, to return. In this case, it returns the 3 right-most digits: 345. Next, the LEFT function lops off the left-most digit in that string, yielding text string of “3”.

Finally, the VALUE function converts that text into the number 3. * Column F: =VALUE(LEFT(RIGHT(B4,2))). The combination of LEFT and RIGHT functions is used to return the fourth digit in the reference cell (B4). The RIGHT function can take two arguments: the cell containing the numeric value to be manipulated (in this case B4) and the number of digits, beginning with the rightmost one, to return. In this case, it returns the 2 right-most digits: 45. Next, the LEFT function lops off the left-most digit in that string, yielding text string of “4”.

Finally, the VALUE function converts that text into the number 4. * Column G: =VALUE(RIGHT(B4)). The RIGHT function with one argument returns the rightmost character from the reference cell (B4). In this case, it returns the text string of “5”. Then the VALUE function converts that text into the number 5. Finally, the actual account number in column I can be created using the CONCATENATE and VALUE functions: * The CONCATENATE function is one of Excel’s built-in text functions that appends two strings together.

Thus, in cell I4, the function CONCATENATE(B4, H4) would append the value in cell H4 (which is the calculated check-digit of 0) to the value in cell B4 (the raw account number 12345) yielding the string 123450. * The VALUE function then transforms that text string of 123450 into the number 123450. 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: =IF(H20=MOD(((C20*5)+(D20*4)+(E20*3)+(F20*2)+G20),7),”Y”,”N”) 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: =IF(H20=MOD(((VALUE(LEFT(B20))*5)+( VALUE(LEFT(RIGHT(B20,5)))*4)+( VALUE(LEFT(RIGHT(B20,4)))*3)+( VALUE(LEFT(RIGHT(B20,3)))*2)+ VALUE(LEFT(RIGHT(B20,2)))),7),”Y”,”N”)