Microsoft Office Tutorials and References

In Depth Information

This book's website contains two hypocycloid workbooks: the simple example shown

in Figure 17-22 (named hypocycloid chart.xlsx), and a much more complex example

(named hypocycloid animated.xlsm) that adds animation and a few other accoutre-

ments. The animated version uses VBA macros.

The chart uses data in columns D and E (the
x
and
y
ranges). These columns contain formulas that rely on data

in columns A through C. The formulas in columns A through C rely on the values stored in E1:E3. The data

column for the
x
values (column D) consists of the following formula:

=(A6–B6)*COS(C6)+B6*COS((A6/B6–1)*C6)

The formula for the
y
values (column E) is as follows:

=(A6–B6)*SIN(C6)–B6*SIN((A6/B6–1)*C6)

Pressing F9 recalculates the worksheet, which generates new random increment values for E1:E3 and creates a

new display in the chart. The variety (and beauty) of charts generated using these formulas may amaze you.

Working with Trendlines

With some charts, you may want to plot a trendline that describes the data. A
trendline
points out general trends

in your data. In some cases, you can forecast data with trendlines. A single series can have more than one trend-

line.

To add a trendline in Excel 2013, select the chart series, click the Chart Elements icon (to the right of the chart),

and select Trendline. The default trendline is Linear, but you can expand the selection choice and choose a dif-

ferent type. For additional options (and more control over the trendline), choose More Options to display the

Format Trendline task pane (see Figure 17-23).

The type of trendline that you choose depends on your data. Linear trends are the most common type, but you

can describe some data more effectively with another type.

On the Trendline Options tab, you can specify a name to appear in the legend and the number of periods that

you want to forecast (if any). Additional options there enable you to set the intercept value, specify that the

equation used for the trendline should appear on the chart, and choose whether the R-squared value appears on

the chart.

When Excel inserts a trendline, it may look like a new data series, but it's not. It's a new chart element with a

name, such as Series 1 Trendline 1. And, of course, a trendline does not have a corresponding SERIES formula.