Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
CHAPTER 10
PROCESSING INTEGRITY AND AVAILABILITY CONTROLS
SPECIAL INTRODUCTION TO EXCEL
This chapter includes a number of problems that use Excel’s built-in Data Validation tool to help
students better understand processing integrity controls by programming them in a spreadsheet.
Some students will already be familiar with this tool, others will not. Therefore, this brief
introductory tutorial may be useful as a hand-out prior to assigning the Excel questions in this
chapter.
This brings up the following window, which can be used to design a variety of processing
integrity controls that will apply to the currently selected cell (in the example above, the Data
Validation controls will be applied to cell C2):
Clicking on the drop-down arrow in the “allow” box yields the following choices:
Choosing either whole numbers or decimals, yields the following additional choices:
This default window can be used to create a “range check” with minimum and maximum values.
List – permissible values must be selected from a list that the control designer creates
If the “In-cell dropdown” box is not checked, users will still be restricted to entering values from
the list indicated in the source box, but will have to manually type in those values rather than
selecting from a drop-down menu.
Date – only date values
Time – only time values
Text Length – length of text string
If the “In-cell dropdown” box is
checked, the values will appear in a
drop-down list when a user clicks
on that cell.
The list of permitted values in the
drop-down box can be found in the
portion of the spreadsheet as
indicated in the “Source” box
Choosing either Date, Time, or Text Length yields the same set of choices as for “whole
numbers” or “decimals”, making it easy to create limit checks, range checks, size checks, etc.:
Custom – formulas can be used to limit input values
Once the processing integrity control has been designed, the “Input Message” tab can be used to
create a message explaining the permissible input values that will appear whenever a user selects
that cell:
The message can have a title, plus as much text as desired. In addition, there are three action
choices:
SUGGESTED ANSWERS TO DISCUSSION QUESTIONS
10.1 Two ways to create processing integrity controls in Excel spreadsheets are to use the
built-in Data Validation tool or to write custom code with IF statements. What are
the relative advantages and disadvantages of these two approaches?
The same range check could be programmed using an IF statement, as follows:
An IF statement consists of three arguments, separated by commas: =IF(first argument,
second argument, third argument). The first argument is the test to be performed, the
second controls what happens if the test is true, and the third argument controls what
happens if the test is false. In this example, the first argument is testing whether the value
in cell A1 is between 18 and 65, inclusive. The second argument directs that if the test is
The Data Validation tool is easier to use. However, it is limited to performing tests of just
one condition. More complex tests require the IF function. For example, perhaps we want
to treat values of 18, 19, and 20 different from values 21-65. This can be done by nesting
IF statements, as follows:
This formula works as follows:
Step 1: the first IF statement tests whether the value in cell A1 is greater than or equal to
Step 2: If the first IF statement is true (i.e., the value in cell A1 is greater than or equal to
