Using the data validation tool, a limit check to ensure that hours worked must be less than or equal to 40 can be designed
as follows:
Alternatively, the following IF statement would enforce the same limit check:
Deductions too high relative to gross pay
This reasonableness test would be programmed using the data validation tool and choosing “custom” in the allow field, as
follows:
The formula would limit the deductions in cell M7 to be less than or equal to 40% of the gross pay in cell L7.
Alternatively, the following IF statement would perform the same reasonableness test:
Error in calculating net pay
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, 2017:
Member #
Flight Date
MM/DD/YY Plane Used Takeoff time Landing time
1234 09/10/17 G 6:25 8:46
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).
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:
Field 2 – Date of flight start:
Check that day, month, and year correspond to the current date.
Field 3 – Plane used:
Field 4 – Time of take off:
Field 5 – Time of landing:
c. Enter the data in a spreadsheet and create appropriate controls to prevent or at least detect the input errors.
Field 1 – Member number:
Alternatively, the following IF statement would do the same thing:
Alternatively, the following IF statement would perform the same test:
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.
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)
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.