<<

. 55
( 62 .)



>>

302




for which we have three input rows: hard-coded, as a percentage
of revenues, and as days of sales. We will do scenarios for the
latter two:


A B C D E F G
93
94 Accounts receivable 60.0 75.0 90.0
As % revenues 7.3% 8.3% 9.0%
95
As % revenues
96
As % revenues
97
Days of sales 26.5 30.4 32.9 30.0 30.0 30.0
98
Days of sales
99
Days of sales
100
101 Accounts receivable 60.0 75.0 90.0 90.4 99.5 109.4
Input
 


E101 ¼IF(E94, E94,IF(CHOOSE($B$2,E95,E96,E97),
CHOOSE($B$2,E95,E96,E97)*E$10,
CHOOSE($B$2,E98,E99,E100)/365*E$10))
This is not an elegant formula. You can see that
the middle nested IF statement has to test all
three possibilities, and this makes for a very
dense formula.
Often, the solution to this kind of modeling
problem is to rethink the original situation.
Do we really need two types of alternate inputs?
If we keep the principle of KISS in mind,
perhaps not.



DATA VALIDATION
With Data Validation, you can make your data entries more fool-
proof by limiting the types of data that can be entered. The
simplest limit may be the type of data (e.g., allow only date
types, or integers), but you can also have it function with more
sophistication, such as providing you with easy-to-build lists of
the items that the worksheet can accept.




TLFeBOOK
Bells and Whistles 303




Additionally, because Data Validation also has pop-up boxes
that alert you to the wrong type of data being input, you can use
these pop-up boxes as a type of cell comment in their own right.
Start Data Validation by the following steps:
1. Put your cursor on the cell for which you want to vali-
date the data. Data Validation works on individual cells
only, so if you have a range of cells where you want this
feature, you can do it in the first cell and then copy the
cell itself, or just its format, to the other cells.
2. Press the sequence Data > Validation, and you will see
this user form:




3. Click on the Settings tab.
4. Specify the type of data that can be entered by clicking
on the list box ˜˜Allow:™™ Let™s say we choose ˜˜whole
number.™™ Specify under ˜˜Data:™™ the filter criterion. For
example, you want to allow the data only to be ˜˜greater
than or equal to.™™ The drop-down list will show you
the full list of criteria. Depending on what you chose in
this step, the user form will show you additional edit
boxes for specifying the value or values required.
At this step, one of the choices under ˜˜Allow:™™ is
˜˜list.™™ This allows you to build a drop-down list very




TLFeBOOK
Chapter 18
304




easily. After selecting this, enter the items that will
appear in the list in the ˜˜Source:™™ edit box. Enter them
each as a text string separated by commas. A check box
option automatically appears on the user form marked
˜˜in-cell dropdown.™™ If this is checked, then the items
you selected will appear as a drop-down list when the
cursor is on this cell. If not, no drop-down list appears.
The primary Data Validation settings have now been set.
The next two steps are optional, but they add a nice level of
control for what happens before and after you enter the data.
1. Select the ˜˜Input Message™™ tab. If the check box for ˜˜Show
input message when cell is selected™™ is checked, then Data
Validation can function as another form of cell comments.
On this tab, you can enter the message or information that
will appear when you put the cursor on this cell.
You may want to have this function by itself, in lieu of
cell comments, without the data filtering that we set on the
˜˜Settings™™ tab. In that case, simply leave the ˜˜Allow:™™
entry on that tab to read ˜˜Any value.™™
2. Select the ˜˜Error Alert™™ tab. If this is undefined, Excel
will show a default error message after the invalid
message is entered. But you can define the message
yourself by entering it here. This tab also has the
setting for what to do if invalid data are entered. Under
the ˜˜Style:™™ list box, you can choose to have the
sequence stopped, or only have a warning or infor-
mation icon show up. The first one will not allow
invalid data to be extended and is the default, but in
some cases where you only need to remind the user
that the data may not be right but you still want the
model to accept them, then either of the latter two
would be appropriate.


FORMATTING NUMBERS
Excel™s features for formatting numbers are quite well developed.
The menus under Format show the different ways that values




TLFeBOOK
Bells and Whistles 305




can be formatted. In addition to this, you should know that Excel
can make a distinction among positive, negative, and zero values
and give different formats to each type of value. In addition, you
can directly format a number to appear as if it were a text or
a combination of number and text. So 0 can appear as ˜˜n/a™™ and
so will not cause any formulas reading the value to go into error,
which they would do if the cell actually did contain the text
˜˜n/a.™™ Likewise, you can make a number, say 8, appear as
˜˜8 months™™ and still be read as a value.



Different Formats for Different Values
Excel™s formats have three different sections. They are in the
following order, separated by semicolons:
Format for positives; format for negatives; format for zero.
The format for positive numbers is required, but the other
two are optional. If the other two are not specified, Excel will use
the format for the positive as the default for all types of numbers.
You can set a different format, including a different color, for
each of the three. To do this, you will have to define a custom
format in Excel.
Let™s look at a format. This one is to show numbers with
one decimal place, with a comma for the thousand separators,
and with parentheses as negative indicators:

#,##0.0_);(#,##0.0)

This format is only for the positive (left of the semicolon)
and the negative (right of same). In this case, no format
has been set for the zero value and a zero will follow the posi-
tive format. The negative is set so that parentheses show up;
the positive formatting includes information in the ˜˜_)™™ that
tells Excel to add a blank space at the end of the positive
number equal to the space taken up by the ˜˜)™™ character.
In this way, the last digit of the positive and negative numbers
in a column will line up. Because of the syntax, we can make
the format change to anything we want. Go to a cell in your




TLFeBOOK
Chapter 18
306




Excel screen, then do Format > Cells > Number tab. Select
Custom from the list of formats, and then type this in the
˜˜Type:™™ box:

#,##0.0_);(0.000);“n/a”

Positive: one decimal place, thousand separators
u

Negative: three decimal places, no thousand separators
u

Zero: show as the text ˜˜n/a™™
u


Or try this in the same ˜˜Custom™™ setting:

#,##0.0_);(#,##0.0);“--”

Positive: one decimal place, thousand separators
u

Negative: one decimal place, thousand separators,
u

parentheses
Zero: show as a dash. This will give you a nice effect
u

of showing those cells with zeroes only a dashes,
making it easier for you to see the cells which do
have numbers. You can enter just one hyphen, or two
if you want to make it a dash. You should enter
additional spaces after the hyphen(s) if you would
like to see the dash farther away from the right border of
the cell.
Don™t forget to explore the other formatting categories.
The Currency and Accounting formats share the same ability
to include the currency symbol for virtually all possible cur-
rencies. The list of symbols also includes the standard three-
letter abbreviations (˜˜USD™™ for U.S. dollar, for example). The
Accounting format has an added touch, however: it will set
the currency symbol separate from the number, so that the
symbol is flush-left in the cell, while the number is flush-right.
A column of numbers set in the Accounting format will look very
neat, with all the currency symbols hugging the left border of
the column. The Accounting format also gives some choices for
setting the currency symbols to appear after the number. The
euro™s E, for example, can be set to be a prefix or suffix to the
number.




TLFeBOOK
Bells and Whistles 307




We can even add colors to each part. Try typing this as a
custom format:
#,##0.0_);[Red](0.000);[Blue]“n/a”
If no color is specified, then Excel will use the default color.
These other color settings are available, too:
[Black]
u

[Cyan]
u

[Magenta]
u

<<

. 55
( 62 .)



>>