# Acc564 Week 5 Homewrk

Topics: 3, Duodecimal, Prime number Pages: 8 (2054 words) Published: November 16, 2012
10.3 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|
0023| 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:...