Microsoft Office Tutorials and References
In Depth Information
Nested Forms of the IF Function
A nested IF function is one in which the action to be taken for the true or false
case includes yet another IF function. The second IF function is considered to be nested,
or layered, within the i rst. Study the nested IF function below, which determines the
eligibility of a student to go on a i eld trip. The school permits the student to attend the
i eld trip if the student’s age is at least 14 and the student has provided a signed permis-
sion form. Assume the following in this example: (1) the nested IF function is assigned
to cell L9, which instructs Excel to display one of three messages in the cell; (2) cell L7
contains a student’s age; and (3) cell L8 contains a Y or N, based on whether the person
provided a signed permission form.
Using IFERROR
Similar to the IF function,
the IFERROR function
checks a formula for
correctness. For example,
=IFERROR(formula, “Error
Message”) examines the
formula argument. If an
error appears (such as
#N/A), Excel displays the
Error Message text in the
#N/A error.
=IF(L7>=14, IF(L8="Y","Allowed","Can Travel, but No Permission"),"Too Young to Travel")
The nested IF function instructs Excel to display one, and only one, of the
following three messages in cell L9: (1) Allowed; or (2) Can Travel, but No Permission;
or (3) Too Young to Travel.
You can nest IF functions as deep as you want, but after you get beyond a nest of
three IF functions, the logic becomes difi cult to follow and alternative solutions, such as
the use of multiple cells and simple IF functions, should be considered.
Sometimes you may want to condense a range of data into a small chart in order to show
a trend or variation in the range. Excel’s standard charts may be too large or extensive for
your needs. An Excel Sparkline chart provides a simple way to show trends and variations
in a range of data within a single cell. Excel includes three types of Sparkline charts: Line,
Column, and Win/Loss. Because they exist in only one cell, you should use Sparkline
charts to convey succinct, eye-catching summaries of the data they represent.
To Add a Sparkline Chart to the Worksheet
Each of the rows of monthly data, including those containing formulas, provides useful information easily
summarized by a Line Sparkline chart. A Line Sparkline chart is a good choice because it shows trends over the
six-month period for each row of data.
The following steps add a Line Sparkline chart to cell I13 and then use the i ll handle to create Line Sparkline
charts in the range I14:I25 that represent the monthly data in rows 13 through 25.
1
Line button
Insert tab
Scroll the worksheet
so that both columns B
and I are displayed on
the screen.
Sparklines group
prepare to insert a
Sparkline chart in
the cell.
Select cell I13 to
Create Sparklines
dialog box
Data Range box
Display the Insert
Collapse Dialog
Box button
tab and then click
Line (Insert tab |
Sparklines group) to
display the Create
Sparklines dialog box
(Figure 3 –35).
Cell I13 selected
Cell I13 is default location
for Sparkline chart
Figure 3–35

Search JabSto ::

Custom Search