Using names in a SERIES formula

You can substitute range names for the range references in a SERIES formula. When you do so, Excel changes the reference in the SERIES formula to include the workbook name. For example, the SERIES formula shown here uses a range named MyData (located in a workbook named budget.xlsx). Excel added the workbook name and exclamation point.

=SERIES(Sheet1!$B$1,,budget.xlsx!MyData,1)

Using names in a SERIES formula provides a significant advantage: if you change the range reference for the name, the chart automatically displays the new data. In the preceding SERIES formula, for example, assume that the range named MyData refers to A1:A20. The chart displays the 20 values in that range. You can then use the Name Manager to redefine MyData as a different range—say, A1:A30. The chart then displays the 30 data points defined by MyData. (No chart editing is necessary.)

As noted previously, a SERIES formula cannot use worksheet functions. You can, however, create named formulas (which use functions) and use these named formulas in your SERIES formula. As you see later in this chapter, this technique enables you to perform some useful charting tricks.

Unlinking a chart series from its data range

Normally, an Excel chart uses data stored in a range. If you change the data in the range, the chart updates automatically. In some cases, you may want to “unlink” the chart from its data ranges and produce a static chart—a chart that never changes. For example, if you plot data generated by various what-if scenarios, you may want to save a chart that represents some baseline so you can compare it with other scenarios.

There are two ways to create a static chart:

• Paste it as a picture. Activate the chart and then choose Home ➜ Clipboard ➜ Copy ➜ Copy as Picture. (Accept the default settings from the Copy Picture dialog box.) Then activate any cell and choose Home ➜ Clipboard ➜ Paste (or press Ctrl+V). The result is a picture of the copied chart. You can then delete the original chart if you like. When a chart is converted to a picture, you can use all of Excel’s image editing tools. Figure 17.2 shows an example.
• Convert the range references to arrays. Click a chart series and then click the Formula bar to activate the SERIES formula. Press F9 to convert the ranges to arrays. Repeat this for each series in the chart. This technique (as opposed to creating a picture) enables you to continue to edit and format the chart. Here’s an example of a SERIES formula after the range references were converted to arrays:

=SERIES(,{"Jan","Feb","Mar"},{1869,2085,2451},1)

Adding a chart title link

The chart title is normally not linked to a cell. In other words, it contains static text that changes only when you edit the title manually. You can, however, create a link so that a title refers to a worksheet cell.

Here’s how to create a linked title:

1. Select the title in the chart.
2. Activate the Formula bar and type an equal sign (=).
3. Click the cell that contains the title text.
4. Press Enter.

The result is a formula that contains the sheet reference and the cell reference as an absolute reference (for example, =Sheet3!$A$1). Figure 17.3 shows a chart in which the chart title is linked to cell A1 on Sheet3.

Adding axis title links

The axis titles are optional and are used to describe the data for an axis. The process for adding a link to an axis title is identical to that described in the previous section for a chart title.

Adding text links

You can also add a linked text box to a chart. The process is a bit tricky, however. Follow these steps exactly:

1. Select the chart and then choose Insert ➜ Text ➜ Text Box.
2. Drag the mouse inside the chart to create the text box.
3. Press Esc to exit text entry mode and select the text box object.
4. Click in the Formula bar and then type an equal sign (=).
5. Use your mouse and click the cell that you want linked.
6. Press Enter.

You can apply any type of formatting you like to the text box.

Adding a linked picture to a chart

A chart can display a “live” picture of a range of cells. When you change a cell in the linked range, the change appears in the linked picture. Again, the process isn’t exactly intuitive. Start by creating a chart. Then do this:

1. Select the range that you want to insert into the chart.
2. Press Ctrl+C to copy the range.

3. Activate a cell (not the chart) and choose Home ➜ Clipboard ➜ Paste ➜ Linked Picture (I).

   Excel inserts the linked picture of the range on the worksheet’s draw layer.

4. Select the linked picture and press Ctrl+X.

5. Activate the chart and press Ctrl+V.

   The linked picture is cut from the worksheet and pasted into the chart. However, the link no longer functions.

6. Select the picture in the chart, activate the Formula bar, type an equal sign, and select the range again.
7. Press Enter, and the picture is now linked to the range.

Single data point charts

Effective charts don’t always have to be complicated. This section presents some charts that display a single data point.

Figure 17.4 shows five charts, each of which uses one data point. These are minimalistic charts. The only chart elements are the single data point series, the data label for that data point, and the chart title (displayed on the left). The single column fills the entire width of the plot area.

One of the charts is grouped with a shape object that contains text.

Figure 17.5 shows another single data point chart. The chart, which shows the value in cell B21, is actually a line chart with markers. A shape object was copied and pasted to replace the normal line marker. The chart contains a second series, which was added for the secondary axis.

Figure 17.6 shows another chart based on a single cell. It’s a pie chart set up to resemble a gauge. Although this chart displays only one value (entered in cell B1), it actually uses three data points (in A4:A6).

One slice of the pie—the slice at the bottom—always consists of 50 percent. The pie has been rotated so that the 50 percent slice is at the bottom. Then that slice was hidden by specifying No Fill and No Border for the data point.

The other two slices are apportioned based on the value in cell B1. The formula in cell A4 is

=MIN(B1,100%)/2

This formula uses the MIN function to display the smaller of two values: either the value in cell B1 or 100 percent. It then divides this value by 2 because only the top half of the pie is relevant. Using the MIN function prevents the chart from displaying more than 100 percent.

The formula in cell A5 simply calculates the remaining part of the pie—the part to the right of the gauge’s “needle”:

=50%–A4

The chart’s title (Percent Completed) was moved below the half-pie. A linked text box displays the percent completed value in cell B1.

Displaying conditional colors in a column chart

This section describes how to create a column chart in which the color of each column depends on the value that it’s displaying. Figure 17.7 shows such a chart. (It’s more impressive when you see it in color.) The data used to create the chart is in range A2:F14.

This chart actually displays four data series, but some data is missing for each series. The data for the chart is entered into column B. Formulas in columns C:F determine which series the number belongs to by referencing the cut-off values in row 1. For example, the formula in cell C3 is

=IF(B3<=$C$1,B3,"")

If the value in column B is less than the value in cell C1, the value goes in this column. The formulas are set up such that a value in column B goes into only one column in the row.

The formula in cell D3 is a bit more complex because it must determine whether cell C3 is greater than the value in cell C1 and less than or equal to the value in cell D1:

=IF(AND($B3>C$1,$B3<=D$1),$B3,"")

The four data series are overlaid on top of each other in the chart. The trick involves setting the Series Overlap value to a large number. This setting determines the spacing between the series. Use the Series Options section of the Format Data Series task pane to adjust this setting. That section has another setting, Gap Width. In this case, the Gap Width essentially controls the width of the columns.

Creating a comparative histogram

With a bit of creativity, you can create charts that you may have considered impossible. For example, Figure 17.8 shows a chart sometimes referred to as a comparative histogram chart. Such charts often display population data.

Here’s how to create the chart:

1. Enter the data in A1:C8, as shown in Figure 17.8.

   Notice that the values for females are entered as negative values.

2. Select A1:C8 and create a bar chart. Use the subtype labeled Clustered Bar.
3. Select the horizontal axis and display the Format Axis task pane.

4. Expand the Number section and specify the following custom number format:

   0%;0%;0%

5. This custom format eliminates the negative signs in the percentages.

6. Select the vertical axis and display the Format Axis task pane.

   In the Tick Marks section, set all tick marks to None, and in the Labels section, set the Label Position option to Low.

7. This setting keeps the vertical axis in the center of the chart but displays the axis labels at the left side.
8. Select either of the data series and display the Format Data Series task pane.
9. In the Series Options section, set the Series Overlap to 100% and the Gap Width to 0%.
10. Delete the legend and add two text boxes to the chart (Females and Males) to substitute for the legend.
11. Apply other formatting and labels as desired.

Creating a Gantt chart

A Gantt chart is a horizontal bar chart often used in project management applications. Although Excel doesn’t support Gantt charts per se, creating a simple Gantt chart is fairly easy. The key is getting your data set up properly.

Figure 17.9 shows a Gantt chart that depicts the schedule for a project that is in the range A2:C13. The horizontal axis represents the total time span of the project, and each bar represents a project task. The viewer can quickly see the duration for each task and identify overlapping tasks.

Column A contains the task name, column B contains the corresponding start date, and column C contains the duration of the task, in days. Note that cell A1 does not have a descriptive label. Leaving that cell empty ensures that Excel does not use columns A and B as the category axis.

Follow these steps to create this chart:

1. Select the range A1:C13 and create a Stacked Bar Chart.
2. Delete the legend.
3. Select the category (vertical) axis and display the Format Axis task pane.
4. In the Axis Options section, specify Categories in Reverse Order to display the tasks in order, starting at the top. Choose Horizontal Axis Crosses at Maximum Category to display the dates at the bottom.
5. Select the Start Date data series and display the Format Data Series task pane.

6. In the Series Options section, set the Series Overlap to 100%. In the Fill section, specify No Fill. In the Border section, specify No Line.

   These steps effectively hide the data series.

7. Select the value (horizontal) axis and display the Format Axis task pane.

8. In the Axis Options, adjust the Minimum and Maximum settings to accommodate the dates that you want to display on the axis.

   You can enter a date value, and Excel converts it to a date serial number. In the example, the Minimum is 5/2/2016, and the Maximum is 7/24/2016.

9. Apply other formatting as desired.

Creating a box plot

A box plot (sometimes known as a quartile plot) is often used to summarize data. Figure 17.10 shows a box plot created for four groups of data. The raw data appears in columns A through D. The range G2:J7, used in the chart, contains formulas that summarize the data. Table 17.1 lists the formulas in column G (which were copied to the three columns to the right).

Follow these steps to create the box plot:

1. Select the range F1:J7 and create a line chart with markers.
2. Choose Chart Tools ➜ Design ➜ Data ➜ Switch Row/Column to change the orientation of the chart.
3. Choose Chart Tools ➜ Design ➜ Chart Layouts ➜ Add Chart Element ➜ Up/Down Bars to add up/down bars that connect the first data series (25th Percentile) with the last data series (75th Percentile).
4. Remove the markers from the 25th Percentile series and the 75th Percentile series.
5. Choose Chart Tools ➜ Design ➜ Chart Layouts ➜ Add Chart Element ➜ Lines ➜ High-Low Lines to add a vertical line between each point to connect the Minimum and Maximum data series.
6. Remove the lines from each of the six data series.
7. Change the series marker to a horizontal line for the following series: Minimum, Maximum, and 50th Percentile.
8. Make other formatting changes as required.

The chart shown here does not have a legend. It’s been replaced with a graphic that explains how to read the chart. The legend for this chart displays the series in the order in which they are plotted—which is not the optimal order and can be very confusing. Unfortunately, you can’t change the plot order because the order is important. (The up/down bars use the first and last series.) Creating a descriptive graphic seems like a good alternative to a confusing legend.

Plotting every nth data point

Normally, Excel doesn’t plot data that resides in a hidden row or column. You can sometimes use this to your advantage because it’s an easy way to control what data appears in the chart.

Suppose you have a lot of data in a column and you want to plot only every 10th data point. One way to accomplish this is to use filtering in conjunction with a formula. Figure 17.11 shows a two-column table with filtering in effect. The chart plots only the data in the visible (filtered) rows and ignores the values in the hidden rows.

Cell A1 contains the value 10. The value in this cell determines which rows to hide. Column B contains identical formulas that use the value in cell A1. For example, the formula in cell B4 is as follows:

=MOD(ROW()–ROW($A$4),$A$1)

This formula subtracts the current row number from the first data row number in the table and uses the MOD function to calculate the remainder when that value is divided by the value in A1. As a result, every nth cell (beginning with row 4) contains 0. Use the filter drop-down list in cell B3 to specify a filter that shows only the rows that contain a 0 in column B.

Although this example uses a table (created using Insert ➜ Tables ➜ Table), the technique also works with a normal range of data as long as it has column headers. Choose Data ➜ Sort & Filter ➜ Filter to enable filtering.

Identifying maximum and minimum values in a chart

Figure 17.12 shows a line chart that has its maximum and minimum values identified with a circle and a square, respectively. These identifiers are the result of using two additional series in the chart. You can achieve this effect manually, by adding two shapes, but using the additional series makes it fully automated.

Start by creating a line chart using the data in range A1:B13:

1. Enter the following formula in cell C2:

   =IF(B2=MAX($B$2:$B$13),B2,NA())

2. Enter this formula in cell D2:

   =IF(B2=MIN($B$2:$B$13),B2,NA())

3. Copy range C2:D2 down, ending in row 13. These formulas display the maximum and minimum values in column B, and all other cells display #NA.
4. Select C1:D13 and press Ctrl+C.
5. Select the chart and choose Home ➜ Clipboard ➜ Paste ➜ Paste Special. In the Paste Special dialog box, choose New series, Values (Y) in Columns, and Series Names in First Row. This adds two new series, named Max and Min.
6. Select the Max series and access the Format Data Series task pane. Specify a circle marker, with no fill, and increase the size of the marker.
7. Repeat step 6 for the Min series, but use a large square for the marker.
8. Add data labels to the Max and Min series. (The #NA values will not appear.)
9. Apply other cosmetic formatting as desired.

Plotting mathematical functions

The examples in this section demonstrate how to plot mathematical functions that use one variable (a 2D line chart) and two variables (a 3D surface chart). Some of the examples make use of Excel’s Data Table feature, which enables you to evaluate a formula with varying input values.

Plotting functions with one variable

An XY chart (also known as a scatter chart) is useful for plotting various mathematical and trigonometric functions. For example, Figure 17.14 shows a plot of the SIN function. The chart plots y for values of x (expressed in radians) from –5 to +5 in increments of 0.5. Each pair of x and y values appears as a data point in the chart, and the points connect with a line.

The function is expressed like this:

y = SIN(x)

The corresponding formula in cell B2 (which is copied to the cells below) is

=SIN(A2)

Figure 17.15 shows a general-purpose, single-variable plotting application. The data for the chart is calculated by a data table in columns I:J. Follow these steps to use this application:

1. Enter a formula in cell B7. The formula should contain at least one x variable.

   In the figure, the formula in cell B7 is

   =SIN(x^3)*COS(x^2)

2. Type the minimum value for x in cell B8.
3. Type the maximum value for x cell B9.

The formula in cell B7 displays the value of y for the minimum value of x. The data table, however, evaluates the formula for 200 equally spaced values of x, and these values appear in the chart.

Plotting functions with two variables

The preceding section describes how to plot functions that use a single variable (x). You also can plot functions that use two variables. For example, the following function calculates a value of z for various values of two variables (x and y):

z = SIN(x)*COS(y)

Figure 17.16 shows a surface chart that plots the value of z for 25 x values ranging from –1 to 1 (in 0.1 increments) and for 25 y values ranging from –2 to 2 (in 0.2 increments).

Figure 17.17 shows a general-purpose, two-variable plotting application, similar to the single-variable workbook described in the previous section. The data for the chart is a 25 × 25 data table in range M7:AL32 (not shown in the figure). To use this application

1. Enter a formula in cell B3. The formula should contain at least one x variable and at least one y variable.

   In the figure, the formula in cell B3 is

   =SIN(SQRT(x^2 + y^2))

2. Enter the minimum x value in cell B4 and the maximum x value in cell B5.
3. Enter the minimum y value in cell B6 and the maximum y value in cell B7.

The formula in cell B3 displays the value of z for the minimum values of x and y. The data table evaluates the formula for 25 equally spaced values of x and 25 equally spaced values of y. These values are plotted in the surface chart.

Plotting a circle

You can create an XY chart that draws a perfect circle. To do so, you need two ranges: one for the x values and another for the y values. The number of data points in the series determines the smoothness of the circle. Or you can simply select the Smoothed Line option in the Format Data Series dialog box (Line Style tab) for the data series.

Figure 17.18 shows a chart that uses 13 points to create a circle. If you work in degrees, generate a series of values such as the ones shown in column A. The series starts with 0 and increases in 30-degree increments. If you work in radians (column B), the first series starts with 0 and increments by π/6.

The ranges used in the chart appear in columns D and E. If you work in degrees, the formula in cell D2 is

=SIN(RADIANS(A2))

The formula in cell E2 is

=COS(RADIANS(A2))

If you work in radians, use this formula in cell D2:

=SIN(B2)

And use this formula in cell E2:

=COS(B2)

The formulas in cells D2 and E2 are copied down to subsequent rows.

To plot a circle with more data points, you need to adjust the increment value and the number of data points in column A (or column B if working in radians). The final value should be the same as those shown in row 14. In degrees, the increment is 360 divided by the number of data points minus 1. In radians, the increment is π divided by (the number of data points minus 1, divided by 2).

Figure 17.19 shows a general circle plotting application that uses 37 data points. In range H27:H29, you can specify the x origin, the y origin, and the radius for the circle (these are named cells). A second series plots the origin as a single data point. In the figure, the circle’s origin is at 2,3, and it has a radius of 7.25.

The formula in cell D2 is

=(SIN(RADIANS(A2))*radius)+x_origin

The formula in cell E2 is

=(COS(RADIANS(A2))*radius)+y_origin

Creating a clock chart

Figure 17.20 shows an XY chart formatted to look like a clock. It not only looks like a clock but also functions like a clock. There is really no reason why anyone would need to display a clock such as this on a worksheet, but creating the workbook was challenging, and you may find it instructive.

The chart uses four data series: one for the hour hand, one for the minute hand, one for the second hand, and one for the numbers. The last data series draws a circle with 12 points (but no line). The numbers consist of manually entered data labels.

The formulas listed in Table 17.2 use basic trigonometry to calculate the data series for the clock hands. (The range G4:L4 contains zero values, not formulas.)

This workbook uses a simple VBA procedure that schedules an event every second, which causes the formula to recalculate.

In addition to the clock chart, the workbook contains a text box that displays the time using the NOW function, as shown in Figure 17.21. Normally hidden behind the analog clock, you can display this text box by deselecting the Analog Clock check box. A simple VBA procedure attached to the check box hides and unhides the chart, depending on the status of the check box.

When you examine the workbook, keep the following points in mind:

• The ChartObject, named ClockChart, covers up a range named DigitalClock, which is used to display the time digitally.
• The two buttons on the worksheet are from the Forms group (Developer ➜ Controls ➜ Insert), and each has a VBA procedure assigned to it (StartClock and StopClock).
• Selecting the check box control executes a procedure named cbClockType_Click, which simply toggles the Visible property of the chart. When the chart is hidden, the digital clock is revealed.
• The UpdateClock procedure uses the OnTime method of the Application object. This method enables you to execute a procedure at a specific time. Before the UpdateClock procedure ends, it sets up a new OnTime event that occurs in one second. In other words, the UpdateClock procedure is called every second.

• The UpdateClock procedure inserts the following formula into the cell named DigitalClock:

  =NOW()

• Inserting this formula causes the workbook to calculate, updating the clock.

Creating awesome designs

Figure 17.22 shows an example of an XY chart that displays hypocycloid curves using random values. This type of curve is the same as that generated by Hasbro’s popular Spirograph toy, which you may remember from childhood.

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.

Linear trendlines

Figure 17.24 shows two charts. The first chart depicts a data series without a trendline. As you can see, the data seems to be “linear” over time. The next chart is the same chart but with a linear trendline that shows the trend in the data.

The second chart also uses the options to display the equation and the R-squared value. In this example, the equation is as follows:

y = 53.194x + 514.93

The R-squared value is 0.6748.

What do these numbers mean? You can describe a straight line with an equation of the form:

y = mx +b

For each value of x (the horizontal axis), you can calculate the predicted value of y (the value on the trendline) by using this equation. The variable m represents the slope of the line, and b represents the y-intercept. For example, when x is 3 (for March), the predicted value of y is 674.47, calculated with this formula:

=(53.19*3)+514.9

The R-squared value, sometimes referred to as the coefficient of determination, ranges in value from 0 to 1. This value indicates how closely the estimated values for the trendline correspond to the actual data. A trendline is most reliable when its R-squared value is closer to 1.

Calculating the slope and y-intercept

This section describes how to use the LINEST function to calculate the slope (m) and y-intercept (b) of the best-fit linear trendline.

Figure 17.25 shows ten data points (x values in column B, actual y values in column C).

The formula that follows is a multicell array formula that displays its result (the slope and y-intercept) in two cells:

{=LINEST(C2:C11,B2:B11)}

To enter this formula, start by selecting two cells (in this example, G2:H2). Then type the formula (without the curly brackets) and press Ctrl+Shift+Enter. Cell G2 displays the slope; cell H2 displays the y-intercept. Note that these are the same values displayed in the chart for the linear trendline.

Calculating predicted values

After you know the values for the slope and y-intercept, you can calculate the predicted y value for each x. Figure 17.26 shows the result. Cell E2 contains the following formula, which is copied down the column:

=(B2*$G$2)+$H$2

The calculated values in column E represent the values used to plot the linear trendline.

You can also calculate predicted values of y without first computing the slope and y-intercept. You do so with an array formula that uses the TREND function. Select D2:D11, type the following formula (without the curly brackets), and press Ctrl+Shift+Enter:

{=TREND(C2:C11,B2:B11)}

Linear forecasting

When your chart contains a trendline, you can instruct Excel to extend the trendline to forecast additional values. You do this on the Trendline Options section of the Format Trendline task pane. (Read earlier in this section to see how to open this task pane.) Just specify the number of periods to forecast. Figure 17.27 shows a chart with a trendline that’s extended to forecast two subsequent periods.

If you know the values of the slope and y-intercept (see the “Calculating the slope and y-intercept” section, earlier in the chapter), you can calculate forecasts for other values of x. For example, to calculate the value of y when x = 11 (November), use the following formula:

=(53.194*11)+514.93

You can also forecast values by using the FORECAST function. The following formula, for example, forecasts the value for November (that is, x = 11) using known x and known y values:

=FORECAST(11,C2:C11,B2:B11)

Forecasting future values with forecasting functions

The preceding example uses the FORECAST function to predict future months’ values. Excel 2016 introduces five new forecasting functions to give you more algorithm options. The new functions use advanced machine learning algorithms, which are beyond the scope of this book. But we include a brief summary and the syntax of the new functions here:

FORECAST.LINEAR(x, known_y's, known_x's)

FORECAST.LINEAR is the direct replacement for the deprecated FORECAST function. It uses linear regression to predict a y value for the given x value:

FORECAST.ETS(target_date, values, timeline, seasonality, data_completion, aggregation)

FORECAST.ETS uses the AAA version of an algorithm called Exponential Smoothing, or ETS. The first three arguments—target_date, values, and timeline—are similar to the arguments for FORECAST.LINEAR. Target_date can be any number, not just a date, but it must be larger than the largest date in timeline. That is, it only predicts future values.

The timeline argument is a series of dates that have a consistent step. That is, each element of the series must be one day apart, one month apart, one year apart, or any other value as long as it’s consistent. There are a few exceptions to this rule depending on the values you provide for the optional arguments.

The remaining arguments are optional.

• Seasonality: Adjusts the forecast for changes in values based on seasons. A seasonality of 0 indicates no seasonality, 1 tells the algorithm to detect seasonality based on the provided values, and any other positive number indicates the interval to use for seasonality. For example, a seasonality of 3 instructs the algorithm to use three periods as a season.
• Data completion: Accounts for up to 30% missing data in timeline. You can indicate that missing values are zeros or tell the algorithm to use an average of the missing data’s neighbors.
• Aggregation: Allows you to have duplicate values in the timeline. If you have duplicate data, aggregation will combine it into one data point using an aggregate you specify here:

FORECAST.ETS.SEASONALITY(target_date, values, timeline, seasonality, data_completion)

FORECAST.ETS.SEASONALITY uses many of the same arguments as FORECAST.ETS. If you specify 1 as the seasonality argument of FORECAST.ETS, the algorithm determines the seasonality. This function returns what the algorithm calculates for seasonality. In Figure 17.28, cell G12 shows that two periods is the seasonality computed from the data.

FORECAST.ETS.CONFINT(target_date, values, timeline, confidence_level, seasonality, data_completion, aggregation)

FORECAST.ETS.CONFINT returns the confidence interval for the forecasted data using ETS. It has the same arguments as FORECAST.ETS except for confidence_level. Confidence_level is a number between 0 and 1. (The default is 95%.) Cell H12 in Figure 17.28 uses a confidence_level of .9 (90%) and calculates that there is 90% confidence that the actual future values will be +/-131 of the predicted values in column F:

FORECAST.ETS.STAT(target_date, values, timeline, statistic_type, seasonality, data_completion, aggregation)

FORECAST.ETS.STAT returns one of several statistical measures from the data provided. Again, the arguments are mostly the same as FORECAST.ETS. The exception is statistic_type, where you indicate which statistic you want the formula to return. The list of statistics can be found in Excel’s help. Figure 17.28 uses the Step size detected statistic in cell I12 to report that it determined that the values in column B incremented by 1.

=RSQ(B2:B11,C2:C11)

Working with nonlinear trendlines

Besides linear trendlines, an Excel chart can display trendlines of the following types:

• Logarithmic: Used when the rate of change in the data increases or decreases quickly and then flattens out.
• Power: Used when the data consists of measurements that increase at a specific rate. The data cannot contain zero or negative values.
• Exponential: Used when data values rise or fall at increasingly higher rates. The data cannot contain zero or negative values.
• Polynomial: Used when data fluctuates. You can specify the order of the polynomial (from 2 to 6) depending on the number of fluctuations in the data.

Figure 17.29 shows charts that depict each of the trendline options.

Earlier in this chapter, you learned how to calculate the slope and y-intercept for the linear equation that describes a linear trendline. Nonlinear trendlines also have equations, and they are a bit more complex.

Summary of trendline equations

This section contains a concise summary of trendline equation. These equations assume that your sheet has two named ranges: x and y.

Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)

Equation: y = (c * LN(x)) + b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)

Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)

Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),x),1)

Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
c1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = INDEX(LINEST(y,x^{1,2}),1,3)

Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
c1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

Selecting a series from a drop-down list

Figure 17.30 shows a chart that displays data as specified by a drop-down list in cell F2. Cell F2 uses data validation, which allows the user to select a month from a list. The chart uses the data in F1:I2, but the values depend on the selected month. The formulas in range G2:I2 retrieve the values from columns B:D that correspond to the selected month.

The formula in cell G2, which was copied to the two cells to the right, is

=INDEX(B2:B13,MATCH($F$2,$A$2:$A$13,0))

Plotting the last n data points

You can use a technique that makes your chart show only the most recent data points in a column. For example, you can create a chart that always displays the most recent six months of data. Figure 17.31 shows a worksheet set up so the user can specify the number of data points.

The workbook uses three names. N is the name for cell E3, which holds the number of data points to plot.

MonthRange is a dynamic named formula, defined as

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)–Sheet1!N,0,Sheet1!N,1)

SalesRange is a dynamic named formula, defined as

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)–Sheet1!N,0,Sheet1!N,1)

The chart’s SERIES formula uses the two named formulas:

=SERIES(,Sheet1!MonthRange,Sheet1!SalesRange,1)

Choosing a start date and number of points

This example uses dynamic formulas to allow the user to display a chart with a specified number of data points, beginning with a selected starting date. Figure 17.32 shows a worksheet with 365 rows of daily sales data.

The start date is indicated in cell E2 (named StartDay), and the number of data points is specified in cell G2 (named NumDays). Both cells use data validation to display a drop-down list of options.

What makes this work is two named formulas. The formula named Date is

=OFFSET(Sheet1!$B$2,MATCH(StartDay,Sheet1!$B:$B,1)–2,0,NumDays,1)

The formula named Sales is

=OFFSET(Sheet1!$B$2,MATCH(StartDay,Sheet1!$B:$B,1)–2,1,NumDays,1)

These are both dynamic names that return a range that depends on the value of StartDay and NumDays. The two names are used in the chart’s SERIES formula. (The filename qualifier was removed for simplicity.)

=SERIES(,Date,Sales,1)

Displaying population data

The example in this section uses the population pyramid technique described earlier in this chapter. (See “Creating a comparative histogram.”) Here, the user can choose two years to view population by age groups side by side.

Figure 17.33 shows the age distribution for 1950 and 2013.

The years are entered in cells C2 and G2. The years can be entered manually or specified by using a scrollbar linked to a cell. Formulas below the chart retrieve data from a table that has population statistics (and projections) from 1950 through 2100.

For each year, the chart also displays the total population and the percentage of people age 65 or older. That information is displayed by using text boxes linked to cells.

Displaying weather data

The example shown in Figure 17.34 is a useful application that allows the user to choose two U.S. cities (from a list of 284 cities) and view a chart that compares the cities by month in any of the following categories: average precipitation, average temperature, percent sunshine, and average wind speed.

The cities are chosen from a drop-down list using Excel’s data validation feature, and the data option is selected using four OptionButton controls, which are linked to a cell. All the pieces are connected using a few formulas.

The key to this application is that the chart uses data in a specific range. The data in this range is retrieved from the appropriate data table by using formulas that use the VLOOKUP function.

The formula in cell A23, which looks up data based on the contents of City1, is

=VLOOKUP(City1,INDIRECT(DataTable),COLUMN(),FALSE)

The formula in cell A24 is the same except that it looks up data based on the contents of City2:

=VLOOKUP(City2,INDIRECT(DataTable),COLUMN(),FALSE)

These formulas were entered and then copied across to the next 12 columns (see Figure 17.34).

Row 25 contains formulas that calculate the difference between the two cities for each month. Conditional formatting was used to apply a different color background for the largest difference and the smallest difference.

The label above the month names is generated by a formula that refers to the DataTable cell and constructs a descriptive title. The formula is

="Average " & LEFT(DataTable,LEN(DataTable)–4)

After you’ve completed the previous tasks, the final step—creating the actual chart—is a breeze. The line chart has two data series and uses the data in A22:M24. The chart title is linked to cell B21. The data in A23:M24 changes, of course, whenever an OptionButton control is selected or a new city is selected from either of the Data Validation lists.