*I have a worksheet that has data in a particular arrangement and a chart that plots this data. I have another worksheet with the identical data arrangement. How can I copy the chart to the new worksheet and have the chart link to the data on the new worksheet?*

Here is my simulation of the problem. Worksheet “Original Data” has a particular data arrangement with a “nice” chart that plots this data.

If I click on a series in the chart and peek at the formula bar, I can see that it plots data from “Original Sheet”. Here are the two series formulas:

```
=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)
```

Great, just what I expect.

I went to a lot of trouble drawing this chart exactly to the boss’ specifications, and I don’t want to spend another hour and a half duplicating the chart for the data on the worksheet named “New Data”. So I copy the chart, and paste it on “New Data”. But that looks like it plots the numbers from “Original Data”.

I can verify this behavior by checking the series formulas in the chart:

```
=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)
```

Most people don’t expect this behavior (though it does make sense, given the history and nature of charts).

In this simple case it wouldn’t take too long to actually edit the series formulas to make the new chart match the new data. But in real life, the chart contains 37 series, and you need to put the chart onto 47 different worksheets. (It’s not too hard to write some VBA to help you edit series formulas, but that’s not today’s lesson.)

So I’m going to show you an easier way to make sure the chart links to the data on the new sheet.

The original chart links to the data on the original sheet, right? If we make a copy of that original sheet, call it “Original Data (2)”, we can verify that the chart on this copied sheet links to the data on the copied sheet. See, series formulas:

```
=SERIES('Original Data (2)'!$C$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$C$3:$C$8,1)
=SERIES('Original Data (2)'!$D$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$D$3:$D$8,2)
```

I didn’t even have to touch the chart. Now, copy the new data from “New Data”, and paste it on top of the data in “Original Data (2)”. The data arrangements are identical on the two worksheets, so we should be fine. I usually use Paste Special – Values, but it doesn’t seem to matter.

Look, the chart now is showing the data we just pasted on top of the copied data:

All we need to do is remove the current “New Data” sheet, and rename this copied sheet to “New Data”.

It’s a small amount of work, but isn’t copying and pasting data a lot easier than futzing with a chart’s series formulas and source data?

The post Copy Chart to New Sheet and Link to Data on New Sheet appeared first on Peltier Tech Blog.

]]>I came across a new (for me) style or waterfall chart last month, which I call the paired waterfall chart. It is designed to show how an investment fund’s total holdings, comprised of stocks or other vehicles and cash, changes during a time period, based on changes in value of its stocks and on proceeds from the sale of some stocks.

Here is an example of such a chart. The holdings of the fund totaled 1350 in 2015: 1100 in stock plus 250 in cash. The holdings of the same fund in 2016 totaled 1450: 675 in stock and 775 in cash. Stock A didn’t change in value. Stock B appreciated by 50 in value. Proceeds from the sale of some Stock C were 275, while the fund’s total value of Stock C declined by 250; the difference of 25 must have been additional appreciation of Stock C. Sales of some Stock D resulted in proceeds of 250 and corresponding loss in value of 250. Stock E went down 25 in value, while Stock F went up 50.

There is no provision for taking cash and buying more of a stock already in the portfolio or a new stock, so the upper set of floating bars are green only, that is, you can only gain cash. The lower bars can be red or green, but the value of your stock holdings can never drop below zero, because at worst the stock is worth nothing. These constraints make the chart slightly easier to build than a standard waterfall, though the horizontal and slanted lines are trickier than in the standard one.

For want of a better name, I call this the “Paired Waterfall Chart”. In hundreds of images of waterfall charts that Google found for me, none had this same structure. I tried to crowd source the chart’s name on Twitter, but the only suggestions that weren’t lame attempts at humor were “stacked waterfall” and “cascade”, and these refer to other chart types. If you know what this chart is really called, or if you have some examples you can share, please let me know in the comments below this article.

Since I’ve never seen this kind of waterfall, and since nobody asked me how to make one, I have not prepared a tutorial. But someone did ask whether my charting software, Peltier Tech Charts for Excel 3.0, could make these charts. The answer was, not yet, but with the thought of perhaps selling a few licenses, I took on the task.

The Advanced Edition of Peltier Tech Charts for Excel 3.0 now includes a Paired Waterfall Chart feature, and that’s what I used to make the chart at the top of this article. The data consists of three columns of data, with labels for the horizontal axis, entries for value of the holdings, and entries for proceeds due to any sales. The top row consists of labels, and the totals in the bottom row are omitted, because the program can add better than you can.

If you are a licensee of the Advanced Edition of Peltier Tech Charts for Excel 3.0, shoot me an email and I’ll send you the link to download the latest build, which includes the Paired Waterfall Chart. If you are a licensee of the Standard Edition, let me know and I’ll send a coupon code so you can upgrade to the Advanced Edition for the difference in price. If you are a licensee of an older Peltier Tech utility, let me know and I’ll send a discount coupon code so you can upgrade. And you should upgrade, because Peltier Tech Charts for Excel 3.0 works in Excel 2016, and the same add-in file works on both Windows and Mac.

Like all of my utility’s charts, it is easy to make the Paired Waterfall Chart. Here I’ve selected the data, clicked the Waterfall dropdown, then clicked the Paired Waterfall Chart button.

This very simple dialog came up.

I clicked OK, and the program generated a new worksheet with a bunch of formulas in a nice Excel Table, and the Paired Waterfall Chart (click on the image to see it full-sized).

I’m gradually transitioning as many of my program’s chart output sheets to use Excel Tables, because they make it easier for users to adjust the chart’s data after they’ve already built the chart. Tables will also make it easier when I finally come out with Peltier Tech Charts for PowerPoint and Word. The structured referencing in the tables can be a challenge, but Zack and Kevin’s book about Excel Tables helped a lot.

The post Paired Waterfall Chart appeared first on Peltier Tech Blog.

]]>*Note: A minor bug was added in Build 3008 and an old bug was supposed to be fixed but wasn’t. Build 3009 remedies this and adds a bit of flexibility to box plot source data.*

This is not a major upgrade, but it seems like more than a minor one. There are big enhancements to Waterfall and Rotated Waterfall charts, there are new chart resizing and alignment features, and Loess Smoothing can now perform moving quadratic regression.

Here’s what’s new…

There are no new chart types in this update, but there are major enhancements to some existing charts.

The Waterfall Chart dialog has been changes, with a new “First Bar” setting and updated “Value Labels” settings. These are described below.

When selecting data, if you choose to output the chart on a new worksheet, you are allowed to use non-contiguous data ranges. For example, you could select category labels in the range A4:A12 and values in the range C4:C12. If you create the chart on the active sheet, the data still must be in adjacent columns.

When the waterfall chart is created on a new worksheet, the data and calculations are placed in an Excel Table. This makes it easier for you to modify the data, by inserting or removing table rows.

Placing the output into a Table will also make it easier for me to build versions of Peltier Tech Charts for PowerPoint and Word. I hope to have these available in a few months.

Regardless of the output location of the chart, the two new chart features are available from the dialog and also using the checkbox or listbox next to the output range.

If you choose the First Bar is Subtotal option, the first bar of the data set is treated like any other total or subtotal in the chart, and it has the color used for the full height bars (blue in this default chart):

If you do not choose this option, the first bar is treated like an other changing value, and it has the color of an up br or a down bar (green or red in this default chart):

As before, you can choose to center the value labels on the bars:

You may also place the labels above the bars:

The new option is that you can apply positive labels above the bars and negative labels below the bars:

Finally, you can hide the labels altogether:

The Waterfall Chart changes apply to both the Standard and Advanced Editions of Peltier Tech Charts for Excel 3.0.

Rotated Waterfall Charts have been enhanced in the same way as regular Waterfall Charts.

The new “First Bar” setting and updated “Labels” settings are highlighted in the new Rotated Waterfall Chart dialog:

When selecting data, if you choose to output the chart on a new worksheet, you are allowed to use non-contiguous data ranges. For example, you could select category labels in the range A4:A12 and values in the range C4:C12. If you create the chart on the active sheet, the data still must be in adjacent columns.

When the rotated waterfall chart is created on a new worksheet, the data and calculations are placed in an Excel Table. This makes it easier for you to modify the data, by inserting or removing table rows.

Regardless of the output location of the chart, the two new chart features are available from the dialog and also using the checkbox or listbox next to the output range.

If you choose the First Bar is Subtotal option, the first bar of the data set is treated like any other total or subtotal in the chart, and it has the color used for the full height bars (blue in the chart below left). If you do not choose this option, the first bar is treated like an other changing value, and it has the color of an up br or a down bar (green or red in the chart below right):

As before, you can choose to center the value labels on the bars (above left chart below), you may place the labels above (to the right of) the bars (above right chart), you can apply positive labels above (right of) the bars and negative labels below (left of) the bars (bottom left chart), or you can hide the labels altogether:

The Rotated Waterfall Chart changes apply only to the Advanced Editions of Peltier Tech Charts for Excel 3.0.

A few features have been added to help you resize and align charts. These can be found in a new dropdown on the Move and Resize Chart Elements button on the Peltier Tech ribbon.

Here are some typical charts of various sizes and positions before using the new features.

Resize Charts makes all of the selected charts the same size as the first selected chart, without moving any of the charts.

Resize and Align Selected Charts makes all selected charts the same size as the first selected chart, and arranges them in a grid with the first selected chart serving as the top left chart in the grid. A small dialog asks how many charts in each row of the grid.

Resize Charts and Margins makes all selected charts the same size as the first selected chart, without moving any charts. The interior plot areas of the charts are all resized so all charts have the same margins around the plot areas. Note that the bottom right chart has a margin for its missing title, and the top right has one for its missing legend; all charts have a wider left margin matching the first chart’s longer axis labels, and the blank right margins all match up.

These chart resizing features apply only to the Advanced Editions of Peltier Tech Charts for Excel 3.0.

There is now an option to use a quadratic weighted moving regression as a basis for Loess smoothing.

Quadratic regression takes slightly longer to calculate, but it may result in the smoothed line being closer to the middle of the input points. Curvature in the input data is smoothed more closely, especially at the ends of the input data.

This update to the Loess Smoothing feature applies only to the Advanced Editions of Peltier Tech Charts for Excel 3.0.

The post Peltier Tech Charts for Excel Upgrade – April 2016 appeared first on Peltier Tech Blog.

]]>The first thing I have to say is that our major sponsor PwC was a perfect host. Their facilities were excellent. Our sessions were in conference rooms on about the 20th floor of their towers in each city (the views sometimes making concentration difficult).

I had a chance to present my on my specialties (VBA and, uh, charting), but also to see what my fellow MVPs had to say on topics I should learn more about. I also got a glimpse into financial modeling, which is not something I usually think much about. The attention to detail and aversion to mistakes are things I aspire to, and it was instructive to see these in a new context.

Besides myself, the Excel MVPs in attendance were Liam Bastick (from Australia), Ken Puls (Canada), Charles Williams (UK), Bill Jelen (US), Ingeborg Hawighorst (New Zealand), and Zack Barresse (US). Liam talked about avoiding spreadsheet layout errors, problems with Excel’s various lookup functions, and studying sensitivity and running simulations without VBA. Ken and I did a tag-team presentation, where he started by showing how to use Power Query to fix up some ugly data from disparate sources and I showed how to turn his cleaned up data into a flexible dashboard. Ken later did a session on Power Pivot. Bill “Mr. Excel” Jelen did a session on data visualization using conditional formatting, another on pivot tables, and finally he showed some cool Power Map analyses. Ingeborg showed us how powerful are Microsoft’s web and standalone Power BI tools. Zack showed off some little known features of Excel tables, and Zack and I both discussed using VBA to make life easier. Finally Charles presented on making VBA run like the wind.

Other experts included Felienne Hermans (Delft University), Ian Bennett (PwC, Australia), Smila Baliga (PwC, US), and Andrew RevFeim (F1F9 in New Zealand). Felienne described her research on testing spreadsheets. Smila discussed ways organizations can cope with their overabundance of spreadsheets. Ian and Andrew covered modeling standards and best practices.

Ben Rampson and Carlos Otero from the Microsoft Excel product team were at the sessions. The presented information about where Excel is going, with the emphasis on BI and with the new versions running on whatever platform you can name (Windows, Mac, iOS, Android, etc.). Their presence is evidence of Microsoft’s recent opening up to customers and users, sharing their plans and listening to feedback and suggestions.

Attendance was pretty good, increasing in the later dates as word about the sessions spread. The technical level of attendees at this kind of session is usually high, since the audience is self-selected. But at these meetings I felt the people who attended were very good at their jobs and at Excel. They asked challenging questions, and conversations between and after the sessions were stimulating.

I met a number of people in person that I’ve known for years over the internet. Jeff Weir(d), who has posted on Chandoo’s blog and Daily Dose of Excel. Doug Jenkins, who writes about using Excel for Engineering problems at Newton, Excel, Bach. Ed Ferrero, a former Excel MVP who now runs a winery. Probably more as well, and when I remember I’ll add them here.

One of the great things about this kind of trip is getting to visit new places. I didn’t get to do a lot of sightseeing, so I plan to come back. I’ll see if my wife wants to come along.

Besides the scenery, the cities we visited had nice restaurants. Among noteworthy meals we had outstanding Middle Eastern food in Auckland, an extensive feast in a Brazilian barbecue in Sydney, and delicious Indian food in Melbourne. The coffee in even the simplest shops was far superior than most donut shops in the States, but I guess that’s to be expected. Somehow I only managed to put on a couple pounds.

I got to visit Tahiti, but only for two hours while they refueled the plane. The airport was very small, with one landing strip and a taxiway. On one side of the runway was ocean, on the other were neighborhoods and the small terminal building. I don’t suppose they get more than one or two jet flights a day, and you need to use the portable stairways to board the plane.

It was raining hard, with temperature in the 90s (or as they say, the 30s) and humidity close to 100%.

Our first real stop was in Auckland, New Zealand. It rained for a couple days, which was okay, because Ken and I were stuck getting our joint presentation working.

The view of Auckland Harbor from the PwC tower was awesome.

The Sky Tower looms over central Auckland. Our hotel was a block or two away, as were numerous restaurants and shops.

The weather in Sydney was gorgeous. My wife told me I’d better enjoy it, since it was snowing back in Worcester.

On a beautiful Sunday afternoon we took a ferry ride through the harbor. On the way we went under the Harbor Bridge and past the Opera House.

Our ferry ride took us to Manly Beach, which was very crowded because of a surfing competition.

I got to sick my feet in the Pacific Ocean. Being from Massachusetts I usually only get to the Atlantic, but they’re equally wet.

On the ferry ride back we again went past the Opera House.

This mother and baby humpback were gracefully swimming over a stairwell in Darling Harbor, a small branch of the enormous Sydney Harbor. I was captivated by this intriguing sculpture, showing skeleton inside translucent plastic skin.

Early in the morning, you can see rowers on the Yarra River, like this single sculler and the half dozen or so in the distance.

We took this scenic footbridge across the Yarra from our hotel to the PwC facilities.

During a street festival on Friday night, Zack Barresse met up with a bagpipes-playing Cookie Monster.

Zack wrote about our travels in Excel Summit South in Review.

All good things have to come to an end, so after two weeks I returned home again (which is also a good thing). On the first day of spring, a week after my return, Mother Nature greeted me with this springtime scene.

It was a very mild winter in Massachusetts, especially compared with last year. But since the springtime snow pictured above, we’ve had snow on two more days.

The post Excel Summit South 2016 Summary appeared first on Peltier Tech Blog.

]]>I’ll illustrate a simple combination chart with this simple data. The chart will use the first column for horizontal axis category labels, the second column for actual values plotted using lines with markers, and the third column using columns (vertical bars).

We start by selecting the data and inserting a column chart.

We finish by right clicking on the “Actual” data, choosing Change Series Chart Type from the pop-up menu, and selecting the new chart type we want. I’ve also used a lighter shade of orange for the columns, to make the markers stand out better.

Let’s do the same for a bar chart. Select the data, insert a bar chart.

Okay, the category labels are along the vertical axis, but we’ll continue by changing the Actual data to a line chart series. That didn’t work out at all. The markers are not positioned vertically along the centers of the horizontal bars, nor horizontally where the data lies in the Actual column of the worksheet.

In the chart below I’ve shown all axis scales and axis titles to illustrate the problem. When we converted the Actual series to a line type, Excel assigned it to the secondary axis, and we have no ability to reassign it to the primary axis. The primary axes used for the bar chart are not aligned with the secondary axes used for the line chart: the X axis for the bars is vertical and the X axis for the line is horizontal; the Y axis for the bars is horizontal and the Y axis for the line is vertical.

We can’t use a line chart at all. If we want to line up the markers horizontally with their proper position along the lengths of the bars, we need to use the Actual data as the X values of an XY series. We will need to generate some additional data for the Y values of the XY series.

We will not try to make a Bar-Line combination chart, because the Line chart type does not position the markers where we want them. We will make a Bar-XY chart type, using an XY chart type (a/k/a Scatter chart type) to position markers.

Here is the new data needed for our Bar-XY combination chart. The factor labels and Target values will be used by the Bar chart series, and the Actual values and Heights for the XY series. Don’t worry about the Height values: I’ll show how they are derived in a moment. The nice thing is that we can use dummy values now and type in the proper values later and the chart will update.

Select the first two columns of the data and insert a bar chart.

Since we probably want the categories listed in the same order as in the worksheet, let’s select the vertical axis (which in a bar chart is the X axis) and press Ctrl+1, the shortcut that opens the Format dialog or task pane for the selected object in Excel. Check the box for Categories in Reverse Order and also select Horizontal Axis Crosses at Maximum Category to move it next to Factor 5.

I’ve also recolored the bars orange, because blue markers show up better against light orange than orange markers against light blue.

Now copy the last two columns (Actual and Height), select the chart, and on the Home tab of the ribbon, click the Paste dropdown arrow, choose the options in this dialog (add cells as new series, values in columns, series names in first row, categories in first column), and click OK.

The data is added as another set of bars, which I’ve colored blue, but we’ll change that in a second.

Right-click on the added series, select Change Series Chart Type from the pop-up menu, and select XY with markers and lines.

We see that the horizontal positions of the markers is just what we want to show.

Now we can see where the values in the Heights column comes from. The right hand vertical axis is used for the Y values of the XY series. Looking at the positions of the horizontal bars and the markers in their correct positions, we can see that the Factor 1 bar is centered on Y=4.5, the Factor 2 bar is centered on 3.5, etc. If you hadn’t guessed this at the beginning, type these values into your data range, and let the chart update.

A few minor changes and we’ll be done. First, change the name of the XY series from Heights to Actual. The easiest way is to click on the series, then look at the highlighted ranges in the chart. The X values (C2:C6) are highlighted purple, the Y values (D2:D6) are highlighted blue, and the series name (cell D1) is highlighted red (highlight colors in Excel 2010 and earlier are different, but the concept is the same). Click on the red border of cell D2, and drag the highlighting rectangle to cover cell C2 to change the series name.

Click on the red border of cell D2, and drag the highlighting rectangle to cover cell C2 to change the series name.

Then, use a lighter shade of orange for the bars, so the blue markers stand out. Finally, hide the right-hand vertical axis: format it so it has no labels and no line color.

And there’s our completed Bar-XY Combination Chart.

The post Bar-Line (XY) Combination Chart in Excel appeared first on Peltier Tech Blog.

]]>If you select a pivot table and insert a chart, Excel inserts a pivot chart. A pivot chart is a special Excel chart, with some strengths and some limitations. I used to avoid pivot charts because of these limitations, which included not being able to hide the field buttons and not being able to resize the plot area or move axis and chart titles. But Microsoft has kept improving them, and now the few remaining limitations seem pretty reasonable given the power and constraints of pivot tables themselves.

- A pivot chart is linked to its parent pivot table. Changes to the pivot table are reflected in the pivot chart, and vice versa. If the pivot table changes size, the pivot chart changes the number of its plotted series, and changes the lengths of these series, to accommodate the updated pivot table size.
- The pivot chart has optional field buttons that allow the same filtering capabilities directly in the chart that are available to the pivot table.
- All data in the pivot table is plotted in the pivot chart, except for subtotals and grand totals. No data from outside the pivot table is allowed in the pivot chart.
- Pivot charts are plotted with category labels and series values in columns only. Pivot charts cannot be plotted by row.
- Not all chart types are available in pivot charts. Line, column, area, bar, and pie charts are available, for example, but XY scatter charts and bubble charts are not.

Often, pivot charts are exactly what is needed. Sometimes, however, a regular chart must be used. For example, you might want a scatter plot of the pivot table’s data. Or you might want to add data from another source.

My colleague Debra Dalgleish has a brief Pivot Chart Tutorial on her Contextures.com web site, as well as a FAQ page on Pivot Tables and Pivot Charts. Debra also wrote Using Pivot Tables in Microsoft Excel on this blog.

This article will answer the following common questions about pivot charts in Excel.

- How do I disconnect a pivot chart from its pivot table?
- How do I copy a pivot chart and link it to another pivot table?
- How do I convert a pivot chart into a regular chart and preserve its links to the pivot table?

The last question could be answered by Making Regular Charts from Pivot Tables. But the question is often asked by someone who has spent significant time formatting his pivot chart, and doesn’t want to lose this formatting or be forced to recreate it.

We’ll start by reviewing regular charts and how their data is handled. Then we’ll examine differences between regular charts and pivot charts. Finally we’ll investigate answers to the questions above.

The screenshot below shows a table with some simple data located in B2:D14. The data is rearranged in F3:I7 (a pivot table could have done this). Below that is a regular Excel chart which plots the data from this second range.

When you select a chart that has a well-behaved* source data range, the chart’s data range is highlighted in the worksheet. The highlighting for our simple chart in Excel 2013 and 2016 is shown below: the X values (category labels) are purple, the Y values are blue, and the series names are red. You can click and drag the highlighted borders to move the chart data, and you can click and drag on the highlighted corners to resize the chart data.

** Well-behaved* means that the Y values of the series are in adjacent rows or columns, in order. Y values and X values (if present) must be aligned: in the chart below, the X values and all sets of Y values all begin on the same row and all end on the same other row, and the series names are aligned with the Y values.

When you select a plotted series, the data for that series is highlighted in the worksheet. The highlighting for the first series of our simple chart in Excel 2013 and 2016 is shown below: the X values (category labels) are purple, the Y values are blue, and the series names are red. You can click and drag the highlighted borders to move the chart data, and you can click and drag on the highlighted corners to resize the chart data. Note that our series is plotted by columns.

When a series is selected, you can also see the corresponding SERIES formula in the formula bar. This series formula has the following components:

- Series Name: Pivot!$G$3
- Category Labels (X Values): Pivot!$F$4:$F$7
- Y Values: Pivot!$G$4:$G$7
- Plot Order: 1

You can edit this formula in place to adjust the chart data.

Another way to adjust a chart’s data is the Select Data Source dialog. To open this dialog, click the Chart Tools > Design tab > Select Data button, or tight-click on the chart and click Select Data from the pop-up menu.

Here is the Select Source Data dialog for our regular chart. The box at the top shows the entire source data, which was highlighted when we selected the entire chart. You can edit this as text, or select another source data range in a worksheet. Caveat: if your selection in the Chart data range box intersects a pivot table, your chart will be converted into a pivot chart based on that pivot table.

Click the Edit button under Axis Labels in the bottom right part of the dialog, and the Axis Labels dialog appears, showing the range containing the axis labels. You can edit this as text, or select another axis label range.

Select a series in the bottom right part of the dialog and click the corresponding Edit button, and the Edit Series dialog appears, showing the range containing the Y values. You can edit this as text, or select another range of values.

If you click the Switch Row/Column button, the same data is used as the source data, but its orientation is switched. The category axis labels become the series names, and the series names become the axis labels. Note that our chart now has four series with three points each (and three axis labels), and the red and purple highlighted regions have changed places.

When we select the first series, we see that it is now aligned in rows.

If your chart’s source data intersects a pivot table, clicking Switch Row/Column will convert your chart into a pivot chart based on that pivot table.

Here’s a little-known debugging trick for Excel formulas. You can use the F9 function key or the Ctrl+= shortcut (hold the Ctrl key while you press the = key) to evaluate part or all of a formula in the formula bar. Fortunately this works for a SERIES formula.

If you click in the formula bar and click F9 or Ctrl+=, every section of the formula is evaluated (and the links are disconnected), as shown in this before-and-after screenshot.

Click Esc to restore the original formula, or Enter to keep the evaluated formula.

If you select just part of a formula and then click F9 or Ctrl+=, just the selected part of the formula is evaluated. In this screenshot, the Y value range of G4:G7 is converted to the array {1;3;2;4}.

To unlink a regular chart from its worksheet data, select each series, click in the formula bar, and press the F9 key.

You can copy a chart and paste it anywhere in the same workbook, even onto a different worksheet, and nothing happens to the chart. It shows the same data that it was linked to (on the original sheet), and the series formulas still link back to the original data. This is familiar, expected behavior, although when you want to link the chart to the data on its new parent worksheet, it’s not so welcome. But see Make a Copied Chart Link to New Data if that’s what you need to do.

When you copy a regular chart to a new workbook, it still points back to the original data, which in the SERIES formula is referenced to the original workbook as well as the original worksheet, as “[Pivot.xlsx]Pivot”. So the regular chart behaves exactly as expected.

The screenshot below shows a table with the same simple data located in B2:D14. A pivot table in F2:I7 has rearranged the data. Below the pivot table is an Excel pivot chart which plots the data from the pivot table. Note the field buttons in the pivot chart, corresponding to the controls in the pivot table.

We can hide the field buttons (Pivot Chart Tools > Analyze ribbon tab > Field Buttons) and the chart will look just like our regular chart. But it still has the capabilities and limitations of a pivot chart.

When the pivot chart is selected, no chart data highlights appear in the worksheet.

When a series is selected in the pivot chart, no series data highlights appear in the worksheet. The SERIES formula appears in the formula bar, but you cannot edit the series data by editing the series formula. You can only change the series plot order by changing the last parameter in the series formula.

Here is the Select Source Data dialog for our pivot chart. The box at the top shows that the source data is our pivot table; this cannot be changed. The axis labels cannot be edited, nor can the series values be edited.

If you click the Switch Row/Column button, the chart changes its appearance to match how our regular chart changed: three series of four categories becomes four series of three categories. But the chart’s data orientation didn’t change, because pivot charts can only plot columns of data. Instead, Excel switched the fields in the rows area of the pivot table with those in the columns area. The X and Series field buttons in the chart have changed places as well.

The series formula shows that the first series of our pivot chart is still plotted by column, with the category labels in column F and Y values in column G.

Excel does not let you evaluate part or all of a pivot chart’s SERIES formula using the F9 or Ctrl+= trick, so you can’t use it to disconnect the pivot chart from its pivot table.

You can copy a pivot chart and paste it anywhere in the same workbook, even onto a different worksheet, and nothing happens to the chart. It shows the same data that it was linked to (in the original pivot tables), and the series formulas still link back to this pivot table.

Interesting things happen when you copy a pivot chart to a different workbook. The first thing you may notice is that the field buttons have disappeared, because the pivot chart has been converted to a regular chart. The second thing you’ll notice, if you check out the SERIES formula, is that the links to worksheet ranges have been changed into literal arrays of strings and numbers, as if we used our F9 (Ctrl+=) trick to evaluate the formula.

There’s the answer to our first question, how to unlink a pivot chart from its pivot table. Simply copy the pivot chart to a different workbook. Once the links are broken, you can copy it anywhere, even into the original workbook, and it will remain disconnected from the pivot table.

In the screenshot below, I’ve copied my unlinked chart and pasted it into the original workbook, in a different worksheet with a different Table of data and a different Pivot Table. As noted above, it’s still disconnected.

Here is the Select Source Data dialog for our unlinked chart. The Chart Data Range box at the top is empty, because the chart’s data is hard-coded into the chart’s SERIES formulas. You can click in the box and select a data range from the worksheet.

If you select a cell or range that overlaps with a pivot table and click OK, the chart will become a pivot chart and use the data from the selected pivot table. In the screenshot below the chart now has field buttons, so we know it has been converted into a pivot chart. The SERIES formula shows links to the data in the pivot table.

There’s the answer to our second question, how to copy a pivot chart but link it to a new pivot table. Copy the pivot chart to a different workbook to disconnect it from the first pivot table, then copy the chart to the sheet with the second pivot table, then use the Select Data dialog and select the new pivot table in the Chart Data Range box.

If we avoid the Chart Data Range box, we can still use the Select Data Source dialog to reconstruct links to the pivot table data. This is essentially the technique in Making Regular Charts from Pivot Tables, but we’re using the pivot chart which may have had custom formats applied.

Under Horizontal (Category) Axis Labels, click Edit, and the Axis Labels mini-dialog will appear, showing the literal array of labels.

You can clear the box and select the axis label range from the worksheet using your mouse. Then click OK.

Now under Legend Entries (Series), select the first series from list, and click Edit. The Edit Series mini-dialog appears with the series name as a string, and the series values as a literal array of numbers.

You can clear each box and select the cell containing the series name and the column of cells containing the series values. Click OK, then repeat for the rest of the series in the chart.

The Select Data Source dialog now looks like this, with the Chart Data Range box displaying the range containing all of the pivot table data. Don’t click in this box, and don’t click Switch Row/Column, or your chart will become a pivot chart.

Click OK, and notice how the chart now plots the pivot table data. The data is highlighted in the worksheet, and the chart has no field buttons, because it remains a regular chart.

Selecting a single series shows the data is plotted by column, but again, the series highlights verify that the chart is not a pivot chart.

And this is the answer to our last question, how to convert our pivot chart to a regular chart but maintain links to the pivot table’s data. Actually, I’ve linked it here to a new pivot table, but I could link it to the original pivot table in the same way. Strictly speaking, this approach didn’t actually maintain the links, as we had to reconstruct them. There is no way to maintain the links while converting the pivot chart into a regular chart.

The post Working with Pivot Charts in Excel appeared first on Peltier Tech Blog.

]]>Bottom line: There are several ways to make dynamic charts in Excel, and there seems to be no difference other than cosmetic in how they work between different versions of Excel, and between operating system. The protocols are the same for Mac Excel and Windows Excel, and perhaps it’s time for a quick review. This exercise was done completely in Mac Excel 2016, and other than not knowing a few of the shortcuts I use everyday, it was not very different from working in Windows Excel 2016.

It’s pretty easy to set up data and create a chart in Excel. But once you’ve created a chart, it keeps plotting data from the same cells. If the data in the cells changes, so does the chart, but if the data extends to more cells (or shrinks to fewer cells), the chart doesn’t seem to notice.

There are a couple ways to create charts that will grow with your data. The easiest way is to use Tables as the chart source data. A bit more complicated is to use Excel’s Names to define the series data for your chart. Using Names can lead to more flexibility in defining the data in your charts. I’ll describe how to make dynamic charts using Tables, using Names, and using Names in a more flexible way.

The easiest way to make a chart’s contents reflect the size of a range of data is to put the data into a Table.

Tables made their appearance in Excel 2003, and were called “Lists”. These lists were a more structured container for your data, with a database structure of fields (columns) and records (rows), field headers (column headers) and filtering tools. You could sort and filter your data range easily, and any formula that used a whole column of your List updated to automatically keep using that whole column of the list. Lists became the favored source data for charts and also for pivot tables.

In Excel 2007, Lists became known as “Tables”, and their capabilities have been expanded in every version since.

The screenshot below shows the same data and chart as above, but the data is now in a Table.

To get your data into a table, you select it (or select one cell and let Excel figure out how far it stretches), and on the Insert tab of the ribbon, click Table. Excel asks if your table has headers, then it applies a Table style (the yellow style is shown below), it adds AutoFilter dropdown arrows to the field headers, and it puts a small backwards “L” bracket at the bottom right corner of the table.

You can change the size of the Table by clicking and dragging the bracket at the bottom left corner of the Table. If you type or paste data directly below the Table, the Table will automatically expand to include this new data. And a chart that uses all rows of the existing Table will expand accordingly.

If you type or paste data directly to the right of the Table, the Table will also automatically expand to include this new data. A chart that uses all of the existing Table will expand accordingly.

This little trick of adding a new series if the data expands accordingly is nice, but it requires that the chart already contain all of the Table’s data.

A Name is what Excel calls a variable that resides in a worksheet or a workbook.

Names are often assigned to cells or ranges; for example, you might place a sales tax rate into a cell and name the cell `SalesTax`

, and subsequently use the cell’s name rather than its address in a formula. Because of this Names have been nicknamed “Named Ranges”.

However, the definition of the name includes a formula. If my sales tax rate was stored in cell A1, then my Name SalesTax would have a definition of “=A1”. Because of this, John Walkenbach proposed that Names should be called “Named Formulas”, but he’s smarter than the rest of us, so his suggestion didn’t stick.

We can use Names in our charts, but we need a distinct name for each dynamic range that the chart will need. We’ll need one Name for the X values if the series use the same X values range, and we’ll need one Name for the Y values of each series. In our sample, we will need three Names. I’ll call them XValues, Y1Values, and Y2Values, and I will define them as follows:

**XValues**
=OFFSET(Names!$A$1,1,0,COUNTA(Names!$A:$A)-1,1)

This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by one row and right by zero rows, then makes it as many rows tall as the number of alphanumeric cells in column A minus one (we don’t want to include the “Category” label), and one column wide.

So starting with cell A1, our range begins in cell B1, and is 6 rows tall and one column wide; our final range is A2:A7. It’s easy to see that adding another value into cell A8 will expand this range to A2:A8. However, if we add a value in cell A57, it will also expand our range to A2:A8, so we need to make sure the rows below our data are kept blank.

The other two definitions are easier:

**Y1Values**
=OFFSET(Names!XValues,0,1)

**Y2Values**
=OFFSET(Names!XValues,0,2)

We’ve already figured out how large each range needs to be, since the X and Y values have the same number of cells, so both of these OFFSET formulas start with the first name `XValues`

as an anchor, and offset no rows down but one or two columns to the right. If we don’t specify sizes, then the new Names will define ranges the same size as the anchor.

Okay, that’s how to build a formula definition for a Name. Let’s actually create a Name.

On the Formulas tab of the ribbon, click the Define Name dropdown, and select Define Name… This pops up the Define Name dialog, shown below for the Mac. The Windows dialog is a bit more extensive, and Windows Excel has a much better Names Manager (this dialog happens to serve as the Mac’s Names Manager). For a truly powerful Name Manager, you should try out the free Name Manager add-in at the website of my colleague, Excel MVP Jan Karel Pieterse.

If you’ve selected data before opening the dialog, Excel tries to guess how you want to name data based on labels in the top row and left column of the selection. But I’ve cleared all of this so we’re starting fresh.

Here I’ve typed the name of the Name. Note that I’ve included the worksheet name and exclamation point, which means the Name will be “in scope” (i.e., available) for the worksheet “Names”. Otherwise it would be “in scope” for the entire workbook.

Then enter the formula where it says “Select the range of cells”. You can enter any formula that refers to cells, or a formula that calculates a value, or a constant value. I don’t capitalize my function names when I enter them; that way, if there’s an error, Excel won’t capitalize a bad function name. A misspelled keyword is easier to recognize if it is not capitalized (“offfset” vs. “OFFFSET”).

Click the OK button to add the Name and exit the dialog, or click the Plus icon to add the name and keep the dialog open.

The name is listed in the box at the left; the worksheet name is listed as well to remind us that the scope of the Name is limited to that worksheet. The sheet name is removed from the name in the top right box.

You can make sure the name refers to the intended range if you click in the box with the formula defining the Name. With the cursor in the formula, the range A2:A7 is highlighted in the worksheet. Perfect.

Now enter the name and formula for the next Name, and don’t forget to include the worksheet name.

Click the plus icon to add the name, and click in the formula to make sure that the Name references the desired range, B2:B7. Check.

Enter the name and formula for the last Name, remembering to include the worksheet name. Click the plus icon, and check that the formula refers to C2:C7.

Whew! Now we’re finally ready to make our dynamic chart.

Every chart series has a formula that defines the data in the chart. The blue series in the static chart below is

`=SERIES(Names!$B$1,Names!$A$2:$A$7,Names!$B$2:$B$7,1)`

This means it uses cell B1 of the sheet Names for the series name (“Alpha”), A2:A7 for the X values, B2:B7 for the Y values, and it’s the first series in the chart. The formula for the orange series is

`=SERIES(Names!$C$1,Names!$A$2:$A$7,Names!$C$2:$C$7,2)`

We can use the Select Data dialog to modify these, but it’s easiest to simply edit the formula directly.

Select the blue series of the dynamic chart, and observe the formula in the Formula Bar. It probably looks like the first SERIES formula above (I invariably start with a static chart of the data I want to plot dynamically). Edit the formula to read as follows, and press Enter.

`=SERIES(Names!$B$1,Names!XValues,Names!Y1Values,1)`

If Excel doesn’t like the new formula, make sure you’ve spelled the Names correctly.

Similarly edit the formula for the orange series to read

`=SERIES(Names!$B$1,Names!XValues,Names!Y2Values,2)`

At first the two charts look the same.

When we select the static chart, we can see the chart’s source data highlighted in the worksheet.

We see the same data highlighted when the dynamic chart is selected. It’s convenient that Excel is smart enough to highlight the chart data even if it is defined by dynamic Names. I’ll select the dynamic charts in the rest of this tutorial to show the range included in these charts.

Now let’s extend the data by a couple of rows. The static chart isn’t clever enough to notice, but the dynamic chart keeps up nicely, illustrated by the highlighted data in the worksheet.

If we extend the data by a new column, the static chart doesn’t change, and the dynamic chart doesn’t add a series to represent the new data.

I’ve added a third chart which shows the new data. I had to add an extra Name to the worksheet:

**Y3Values**
=OFFSET(Names!XValues,0,3)

and then I had to add a third series to the chart with the following SERIES formula:

`=SERIES(Names!$B$1,Names!XValues,Names!Y3Values,3)`

My new chart contains all the data, though I had to include it in the chart manually.

Before Excel 2003, the only way to get a dynamic chart was by using Names. It’s a lot of work, and hardly seems worthwhile if using Tables is so easy. But if we know how to make a chart using Names, we can make a dynamic chart that’s more complicated than just expanding to add a row.

How about a chart that doesn’t show all the data, but only the last several points. This might be useful if you want to show the last six months of sales, or high temperature for the previous week.

We’ll make a dynamic chart that plots the last five values. The setup is almost identical to the previous dynamic chart, but our definition of `XValues`

will change.

**XValues**
=OFFSET(Names!$A$1,COUNTA(Names!$A:$A)-1,0,-5,1)

This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by the number of alphanumeric cells in column A minus one, and right by zero rows. This means now we’re starting at cell A7 instead of A2. Then we make the range -5 rows high, meaning we count upward, and one column wide. Our new X values range should be A3:A7. After entering the new name as before, check to make sure the correct range is highlighted.

Edit the SERIES formulas of your dynamic chart as we did above. We see that our static chart shows all six points of the data, but the dynamic chart shows only the last five points, categories B through F.

Let’s add a couple rows of data. The static chart is, well, static, but our dynamic chart shows the new last five points, categories D through H.

Dynamic charts can easily be created in Excel using data ranges from Tables.

With a bit more work dynamic charts can be created using skillfully defined Names. These charts can be more flexible than Table-based dynamic charts, depending on the formula skills of the Excel user. There are a few gotchas that I didn’t mention: some Name definitions seem like they should work, for example, but Excel charts won’t recognize certain functions. Also, some Name names may cause problems, especially those beginning with the letter “c”; you can’t enter them into the SERIES formula, but you can use them in the Select Data dialog.

The post Dynamic Charts in Excel 2016 for Mac appeared first on Peltier Tech Blog.

]]>This chart is crisp and well suited for publication in a technical journal, hence the name I’ve given it, “Technical” Dot Plot. The chart below show results of a hypothetical clinical trial, where the X values (categories) are three different cleansing approaches, and the Y values are the individual responses, where a lower value indicates fewer incidences of infection.

This “technical” dot plot chart shows each individual response, to give you an idea of the distribution of results. This is more detailed than a simple average, or even a box plot, which simplifies the data distribution into its min, max, median, and quartiles. If desired, each category could have different marker (dot) shapes, sizes, or colors. However, that isn’t necessary.

If you Google “Dot Plot”, or search Wikipedia or any other resource, you’ll learn that the phrase “dot plot” can mean many different things. In addition to what I call the “Technical” Dot Plot shown above, there are also “Cleveland” Dot Plots, “Kindergarten” Dot Plots, and Scatter Plots. Technical Dot Plots, Cleveland Dot Plots, and Scatter Plots are all effective means of displaying data. Unfortunately people who display data effectively do not always come up with effective and unique names for their charts.

This type of graphic is named for William Cleveland who described them in a 1984 paper with coauthor Robert McGill. They were presented nicely by Naomi Robbins in Dot Plots: A Useful Alternative to Bar Charts.

Peltier Tech Charts for Excel can create this type of chart as easily as any built-in Excel chart.

These are often a better alternative to horizontally-oriented line charts, especially since the category labels can be reasonably long and still remain horizontal for improved readability.

I call this the “Kindergarten” Dot Plot, because it feels more like a fingerpainted art project than a serious means of visualizing data. It is built by adding a dot to the chart every time you encounter the given value in a set of numbers. See another value, dip your finger in paint and put another blot on the paper.

To me, the histogram below is a better representation of the distribution above. The simple bars show the values without distracting me into counting all those stupid little dots.

Peltier Tech Charts for Excel can create histograms easily in Excel. Excel’s old Analysis Toolpak used to make column charts that were passed off as histograms, and Excel 2016 for Windows has finally introduced native histograms.

You can represent a histogram as a line chart, below left, often called a “Probability Polygon”. It’s not a terrible representation, though I prefer the histogram.

Often you’ll see a probability polygon shown with markers and without line segments, and it’s also called a dot plot, below right. I find this inferior, because the dots are unconnected and seem to be strewn across the chart.

A Scatter Plot (a/k/a XY Chart, Scatter Chart, etc.) is often called a Dot Plot because dots (markers) are used to indicate individual data points.

I’ll describe a few different ways to create Technical Dot Plots. The end result is the same, but the protocol differs because you may have different data layouts available for use.

Regardless of data layout, we will make a combination chart, using a column chart type to get the nice horizontal axis labels and XY Scatter types to get the dots.

The first data layout has separate X and Y values for each category in the chart. The X and Y ranges for each set of dots may be next to each other:

or the X and Y ranges for each set of dots may be separated from each other:

You also need a small table with the category names and zero values. Select this small table, and insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Copy and select the orange shaded data range, either B2:C12 in the example with the X and Y ranges together or B2:B12 and E2:E12 in the example with the separated X and Y ranges. To select multiple areas, select the first area, then hold Ctrl while selecting additional areas.

Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as a new column series (below left). The categories are temporarily messed up: the three original categories are forced to the left because the new series has more points.

Right click on the added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Select the series of dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1 align the points above the first category along the horizontal axis (below left).

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly reduces steps needed for this in a combination chart like this.

Copy and select the gray shaded data range, either D2:E12 in the example with X and Y ranges together or C2:C12 and F2:F12 in the example with separated X and Y ranges. Select the chart, and use Paste Special to add the data as new series, values in columns, series name in first row, categories in first column. The result is a set of gray dots added to the chart (below right). Excel remembers that the previous added series was changed to an XY type with markers and no lines on the primary axis, so it uses these settings for the new series. The X values of 2 position the gray dots above the second category along the X axis.

Copy and select the gold shaded data range, either F2:G12 in the example with X and Y ranges together or D2:D12 and G2:G12 in the example with separated X and Y ranges. Select the chart, and use Paste Special to add the data as before. The result is a set of gold dots added to the chart (below left). Excel again applies an XY chart type with markers and no lines and assigns the series to the primary axis. The X values of 3 position the gold dots above the third category along the X axis.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

The technical dot plot is ready for publication.

The second data layout has a single set of X values with three sets of Y values, as shown below. This is easier than the previous data layout, because it requires only one Copy-Paste-Special cycle. Don’t worry about the blank cells; Excel will ignore them in the chart.

You also need a small table with the category names and zero values. From this small table, you insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Select and copy the data range (B2:E32 in the example above). Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as three new column series (below left). The categories are temporarily messed up: the new series have many more points, forcing the three original categories to the left.

Right click on the first added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Change the chart type of the second and third added series from column to XY Scatter, so that there are three sets of colored dots on the secondary axes (below left).

Select the first series of dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1 align the points above the first category along the horizontal axis (below right).

Format the second and third set of dots so they are also plotted on the primary axis (below left). The X values of 2 and 3 position the dots above the second and third categories on the X axis.

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly streamlines the process to build a combination chart like this.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

The technical dot plot is ready to go.

The third data layout has a single set of X values and a single set of Y values, as shown below. This is the easiest of all, because it requires only one Copy-Paste-Special cycle, and there is only one series to be modified once it’s been added to the chart.

You also need a small table with the category names and zero values. From this small table, you insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Select and copy the data range (B2:E32 in the example above). Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as a new column series (below left). The categories are temporarily messed up: the three original categories are forced to the left because the new series has way more points.

Right click on the added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Select the series of orange dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1, 2, and 3 align the points above the first, second, and third category along the horizontal axis (below right).

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly simplifies formatting of a combination chart like this.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

Your new technical dot plot is good to go.

The post Make Technical Dot Plots in Excel appeared first on Peltier Tech Blog.

]]>In Substitute, we learn of a substitute teacher’s “real-life” math problems, which include the following:

Ha, ha, funny commentary on a frustrated old substitute math teacher. (Disclosure: my wife is an 8th grade math teacher, and frustrated is often descriptive of her job.)

So how far could you get before you become Velociraptor Chow? Rhett Allain, a science writer at Wired Magazine, shows us how to figure this out in Here’s How to Solve the xkcd Velociraptor Problem With Code. Prof. Allain describes a numerical approach to the problem using some physics and a bit of python code. Essentially you have some initial conditions, such as speed and position of predator and prey, and some boundary conditions, such as acceleration and maximum speed of each. You select a suitably short time increment, compute the speed due to acceleration and distance traveled based on speed, and update the conditions. If the dinosaur hasn’t yet caught the primate, you again increment the time, update the conditions, and so on, until the two participants coincide in space and time.

You could use your best 8th grade algebra (or my wife’s) and compute the solution analytically, but that’s more suited to paper and pencil, not to a computer. And the numerical approach can be applied to many phenomena, physical and other (including financial).

An algebraic solution?

We’ll use Excel to carry out these same calculations, both as a set of worksheet formulas and as a VBA routine.

In his Calvin and Hobbes comic strip, Bill Watterson has touched on velociraptor-human conflict, not as a physics problem but as a solution to an ecological problem (human overpopulation).

Using an Excel table allows you to make iterative calculations easily. Once you get the formulas right, you can then extend the calculations by adding rows to the table.

Below is the initial setup of the velociraptor problem. Calculations are compiled in a table in the top left of the worksheet. Parameters for the problem are listed in a range nearby and are used in definitions for Names that make setting up the problem easier (see definitions below). The chart plots positions of the velociraptor and of the human on the Y axis vs. time on the X axis.

This screen shot of Excel’s Name Manager dialog shows the Names which were defined to facilitate calculations. `Delta_t`

is the time increment between calculated time points in the table. `XstartV`

and `XstartH`

are the initial positions of the velociraptor and of the human. `AccelV`

and `AccelH`

are the acceleration for the velociraptor and for the human. `VmaxV`

and `VmaxH`

are the maximum running speeds for the velociraptor and for the human.

The table contains these column formulas:

**Time**
=IF(ROW()-ROW(Table1[#Headers])=1,
0,
OFFSET([@Time],-1,0)+Delta_t)

**Vvel** (velocity of the velociraptor)
=IF([@Time]=0,
0,
IF(OFFSET([@Vvel],-1,0)>=VmaxV,
VmaxV,
MIN(OFFSET([@Vvel],-1,0)+AccelV*Delta_t,VmaxV)))

**Xvel** (position of the velociraptor)
=IF([@Time]=0,
XstartV,
OFFSET([@Xvel],-1,0)+[@Vvel]*Delta_t)

**Vhum** (velocity of the human)
=IF([@Time]=0,
0,
IF(OFFSET([@Vhum],-1,0)>=VmaxH,
VmaxH,
MIN(OFFSET([@Vhum],-1,0)+AccelH*Delta_t,VmaxH)))

**Xhum** (position of the human)
=IF([@Time]=0,
XstartH,
OFFSET([@Xhum],-1,0)+[@Vhum]*Delta_t)

This screenshot shows the table populated to an elapsed time of 2 seconds. The human has just reached his maximum velocity, but the velociraptor is still accelerating. The dinosaur has gotten slightly closer to the human.

You can extend the chase by extending the table, simply by clicking on the small angle-iron at the bottom right corner of the tab and dragging it down as far as needed.

Below, the table has been filled down to 6.5 seconds (the rows between 1 sec and 5 sec have been hidden). We see from the chart that the velociraptor has overtaken the human, and I’ve indicated with red text the row in the table where the position of the velociraptor has first passed the position of the human (row 62, 5.9 sec).

This simplistic model doesn’t stop when the velociraptor reaches his dinner. We need to insert some intelligence into the formulas in our table.

Using a more detailed table allows you to calculate the point of intersection of the paths of the velociraptor and the human, and stop the chase at that time.

Below is the initial setup of the velociraptor problem.

There are a few additional columns:

**TTime** (adjusted time)
=IF(ROW()-ROW(Table14[#Headers])=1,
0,
IF(OFFSET([@TTime],-1,0)<>OFFSET([@Time],-1,0),
NA(),
IF([@Xvel]<=[@Xhum],
[@Time],
(OFFSET([@Xhum],-1,0)-OFFSET([@Xvel],-1,0))/
(OFFSET([@Xhum],-1,0)-OFFSET([@Xvel],-1,0)
-[@Xhum]+[@Xvel])*Delta_t+OFFSET([@Time],-1,0))))

**XXvel** (adjusted position of the velociraptor)
=IF([@Time]=[@TTime],
[@Xvel],
OFFSET([@Xvel],-1,0)+[@Vvel]*([@TTime]-OFFSET([@TTime],-1,0)))

**XXhum** (adjusted position of the human)
=IF([@Time]=[@TTime],
[@Xhum],
OFFSET([@Xhum],-1,0)+[@Vhum]*([@TTime]-OFFSET([@TTime],-1,0)))

The formula for `TTime`

does a lot of work. It’s equal to zero in the first data row, it fills with #N/A after the velociraptor reaches the human, and if the velociraptor catches the human during the current time increment, it interpolates to find the precise time that this happens (see the red entries in row 61).

The chart shows the paths of the predator and prey only up to the point of capture, at 5.8375 sec, or 29.325 m from the human’s initial position.

You can use Excel VBA to solve this velociraptor problem, and animate the chart which illustrates the chase. Initial and boundary conditions are found in the small table in columns G and H.

Columns A through E are the calculations, as in the first approach, except the results are not calculated by worksheet formulas, but instead are calculated by VBA and output to the table as values. These values are plotted in the larger chart.

The small table in columns J through L show the initial and current (i.e., in the most recently calculated iteration) positions of human and velociraptor. These values are plotted in the smaller chart.

Clicking the `Reset Chase`

button runs the `ResetChase`

procedure (below), which sets conditions back to the start of the chase, by deleting all table rows after the first data row.

```
Sub ResetChase()
Application.ScreenUpdating = False
With ActiveSheet.ListObjects(1)
Do Until .ListRows.Count = 1
.ListRows(.ListRows.Count).Delete
Loop
End With
Application.ScreenUpdating = True
End Sub
```

Clicking the `Start Chase`

button runs the `StartChase`

procedure (below), which starts the chase and runs it until the end.

```
Sub StartChase()
Dim tTime0 As Double, tTime1 As Double
Dim xXhum0 As Double, xXhum1 As Double
Dim xXvel0 As Double, xXvel1 As Double
Dim vVhum0 As Double, vVhum1 As Double
Dim vVvel0 As Double, vVvel1 As Double
Dim vVmaxH As Double, AccelH As Double
Dim vVmaxV As Double, AccelV As Double
Dim DeltaT As Double, DDelTT As Double
Dim iDelay As Long, iLooper As Long
Dim ws As Worksheet
ResetChase
' initialize
Set ws = ActiveSheet
tTime0 = 0
iLooper = ws.Range("Looper").Value2
xXhum0 = ws.Range("XstartH").Value2
xXvel0 = ws.Range("XstartV").Value2
vVhum0 = 0
vVvel0 = 0
vVmaxH = ws.Range("VmaxH").Value2
vVmaxV = ws.Range("VmaxV").Value2
AccelH = ws.Range("AccelH").Value2
AccelV = ws.Range("AccelV").Value2
DeltaT = ws.Range("Delta_t").Value2
' loop
Do
tTime1 = tTime0 + DeltaT
' calculate human velocity and position
If vVhum0 >= vVmaxH Then
vVhum1 = vVmaxH
Else
vVhum1 = vVhum0 + AccelH * DeltaT
If vVhum1 > vVmaxH Then
vVhum1 = vVmaxH
End If
End If
xXhum1 = xXhum0 + vVhum1 * DeltaT
' calculate velociraptor velocity and position
If vVvel0 >= vVmaxV Then
vVvel1 = vVmaxV
Else
vVvel1 = vVvel0 + AccelV * DeltaT
If vVvel1 > vVmaxV Then
vVvel1 = vVmaxV
End If
End If
xXvel1 = xXvel0 + vVvel1 * DeltaT
' has velociraptor caught human?
If xXvel1 > xXhum1 Then
DDelTT = DeltaT * (xXhum0 - xXvel0) / _
((xXhum0 - xXvel0) + (xXvel1 - xXhum1))
tTime1 = tTime0 + DDelTT
xXhum1 = xXhum0 + vVhum1 * DDelTT
xXvel1 = xXvel0 + vVvel1 * DDelTT
End If
' add new time point data to row below table
With ws.ListObjects(1)
.ListRows(.ListRows.Count).Range.Offset(1).Value = _
Array(tTime1, vVvel1, xXvel1, vVhum1, xXhum1)
End With
' exit if we're done
If xXvel1 >= xXhum1 Then
Exit Do
End If
' build in delay if animation runs too quickly on screen
For iDelay = 1 To iLooper
DoEvents
Next
DoEvents
' persist previous loop's data
tTime0 = tTime1
vVhum0 = vVhum1
xXhum0 = xXhum1
vVvel0 = vVvel1
xXvel0 = xXvel1
Loop
End Sub
```

As the VBA code runs and data is added to the table, you can watch the chase progress in the two charts. Here is the chase after two seconds.

Here is the chase after four seconds.

And here is the chase at its conclusion.

Same result as the table based approaches, except for the added benefit of watching the animation as the VBA calculations proceed.

Obviously the accuracy of our numerical solution depends on the size of the time increment we use in our calculations. If we take smaller time increments, we can reduce the error resulting from treating nonlinear behavior (acceleration) as linear. On the other hand, taking smaller time increments means our program has to make more calculations, and therefore it will run more slowly.

I ran a modified `StartChase`

procedure to determine how time increment size affected elapsed time, distance, number of iteractions, total calculation time, and calculation time per iteration. I turned off screen updating during this procedure and did not output the results of each increment to the worksheet, to ignore the time VBA spends communicating with the Excel worksheet.

Here are the effects of increment time on solution accuracy and calculation time. The blue shaded row shows the solution used in the examples above, an increment of 0.1 sec.

We learn some interesting things if we plot this data.

If we plot computed elapsed time to capture of the human vs time increment (Delta_T), we see a straight line with an almost perfect correlation (below left). The computed elapsed time gets closer and closer to the Y intercept as the time increment decreases. We could say that this Y-intercept is the actual time of capture, and our solutions get closer and closer to predicting it as the error in the incremental calculations is minimized. In fact, the difference between the time increment used above (0.1 sec) and the Y-intercept is only 0.05 sec. Maybe we can decide that our analytical solution using an increment of 0.1 sec is “accurate enough”.

If we plot distance to capture vs time increment (below right), we see a trend, but not a nice linear trend as with the elapsed time chart. However, we see that for time increments of 0.1 sec or shorter, there is barely any deviation from the 0.1 sec computation of 29.325 m.

Naturally, a smaller time increment will result in more iterations, in an inverse relationship. The chart below left shows a linear scatter chart, which isn’t very interesting; the data for a large increment (0.5 s) is found out on the X axis. Shortening the increment brings the points back along the X axis until they curve around and head up the Y axis. What is happening is shown better in the log-log plot below right. This is a straight line, and the exponent on X is -0.9995, extremely close to the inverse 1/X relationship we expect.

We would expect a similar inverse relationship between total calculation time and time increment. The linear chart below left shows the same axis-hugging behavior as above, and the log-log chart below right gives us another straight line. This one isn’t as perfectly straight, but a regression on the points for smaller increments (filled points) has a strong correlation and a nearly 1/X relationship.

For the time increment of 0.1 sec from the examples above, we have a total calculation time of 0.05 sec, which is pretty fast for an “accurate enough” solution. If we really want more accuracy, we could even go to a 1 sec solution based on a o.oo2 time increment.

Keep in mind that these calculation times are for a modified procedure, not the procedure that updates the table and chart after each calculation increment. Our 0.1 sec time increment may give the solution in 0.05 sec, but the time we spend watching each increment update the table and chart is more like 4 or 5 seconds.

Finally, we see that as the number of iterations increases, the calculation time increases linearly.

The post Solve xkcd’s Velociraptor Problem with Excel appeared first on Peltier Tech Blog.

]]>There are plenty of topics that I want to cover. New chart types in Excel 2016. Charting and programming examples. Dozens and dozens of old articles that were written for Excel 97 and need to be updated.

An article that I started in October and still haven’t gotten around to finishing is an Excel-based solution to xkcd’s velociraptor problem, inspired by an article in Wired magazine. I have a formula-based approach and a VBA approach, plus plenty of cool images from a Google search.

I’ve written about how Microsoft has released Excel 2016 for Mac and Excel 2016 for Windows.

The Mac version finally looks enough like the Windows version that I don’t feel totally hobbled while using it. In fact, the look and feel is very much like the Windows equivalent. Of course, it was released prematurely, and a lot of things were not really working yet, particularly on the VBA side. But each month Microsoft releases an update (the latest is 15.17), which fixes a bunch of stuff, hasn’t yet fixed a bunch of other stuff, and into which a few new bugs have crept in. The latest big improvements are that the ribbon can now be customized by add-ins like my own charting utilities, though there is not yet a capability for users to modify the ribbon themselves.

The Windows version is pretty good; I’m using it most of the time now, except for testing. There are a few things I don’t like, for example the way they handle pinned files and folders in the File-Open and File-Save functions. But all in all, it’s working well. The Windows version has monthly updates too, if you’re using an Office 365 subscription.

The coolest thing Microsoft has done is started up a User Voice section on Excel, which allows us, as regular users, to make suggestions for features that would make Excel even better. If you see an idea you like, you can vote on it, and the items with the most votes get attention from Microsoft. Here are some suggestions I’ve made, voted for, or commented on.

Give us a proper NULL() worksheet function – This would let you use NULL() in a formula, for example, and a chart would treat the formula as if the cell were totally blank, and leave a gap in the line. You know what’s cool? Within a couple weeks of posting this idea, someone from Microsoft called me to discuss this function, and now it’s actually being implemented.

Recent Files pane in Excel 2013 was pretty good, but Excel 2016 broke it – This is about how Excel 2016 messed up how pinned files and folders are displayed, which I complained about above. It’s only got 61 votes, so follow the link and add your vote.

Sensible date formatting on X-axis of XY-scatter charts – This would give you a nicer date format for XY charts, without having to use the less-flexible line chart. Only three votes, so I don’t know about this one.

Chart series formatting – UI overhaul – I think the intent of this one is to provide a single dialog to format all of a chart’s series, the way Excel 2013 introduced one dialog to manipulate chart type and axis for all series. 36 votes, so people, share the love.

Go to the User Voice site, read the ideas people have posted, and vote for your favorites. Microsoft is reading these, and commenting on even the ideas without too many votes.

I announced the release of Peltier Tech Charts for Excel 3.0, the latest major upgrade to my popular and awesome Excel charting utilities. This major upgrade makes it compatible with Excel 2016, such that one add-in works on both Windows and Mac computers; no need to buy two licenses if you can’t decide on an operating system. Because Mac Excel 2016 is evolving monthly, I have been spending a lot of time making sure that my software takes advantage of the fixes Microsoft makes (and removing workarounds). I have also been addressing a lot of dumb little problems, and I’ve started outlining some new features. The documentation is admittedly pretty lame, so I have started outlining that work as well.

If you are a licensee of my earlier utilities, email me and I’ll set you up with a discount coupon so you can upgrade on the cheap.

In November, Microsoft hosted the annual Microsoft Global MVP Summit. I met up with a few dozen Excel MVP colleagues, and we got caught up, had a beer or three, and talked Excel with the Excel Product Group at Microsoft. These folks are working on some cool things, not just the Windows and Mac stuff, but also Excel on all kinds of platforms: I let them talk me into installing Excel on my Android phone, and I was amazed that I could actually do a little work on it. They’re working on improved simultaneous co-authoring of documents, and on more new chart types, and this new chart engine they’ve been developing will make charting faster and more flexible.

In March 2016 I and several of my Excel MVP colleagues will be traveling to the Southern Hemisphere to present a series of conferences on Excel. We will have two-day sessions in Auckland, Sydney, and Melbourne. Follow the link to read more about Excel Summit South 2016. There will be presentations by the experts, including MVPs, industry leaders, and Microsoft. We’ll have panel discussions and Q&A sessions, and lots of time for off-line chats.

On May 26, 2016, MVPs Tony de Jonker and Jan Karel Pieterse will host the third annual Amsterdam Excel Summit. I missed the 2014 session, but I was there last year, presented a couple of sessions, and met a lot of people that I’d only known through email or blogs. I’ll be there again in 2016, so come by and visit; I’m really friendly, not as nerdy as my blog would make you think.

On May 27, 2016, I will lead the Excel Charting And Dashboard Masterclass along with Tony de Jonker and David Hoppe. We’ll be teaching about charting, visualization, and dashboards.

Microsoft has changed a lot, especially in the past couple of years. They’ve really opened up about what they’re working on, and they’re listening much more closely to what others are telling them. Of course, Microsoft pays a lot of attention to us MVPs, both at the Summit and in the mailing lists they host to discuss things with us. But they also are interested in what their regular users are saying. I mentioned User Voice above, where Microsoft program managers are reading the suggestions that users are posting. They also pay attention to the Send-a-Smile/Frown feedback; I always include my email in the things I send in, and I’ve gotten responses on at least a couple of the items I’ve submitted.

This is not the same Microsoft we’ve known all these years.

The post Peltier Tech Update December 2015 appeared first on Peltier Tech Blog.

]]>