Microsoft Office Tutorials and References

In Depth Information

**Adding and Formatting Sparkline Charts**

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

cell instead of the Excel

#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.

Adding and Formatting Sparkline Charts

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