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.

]]>I have not personally devoted a lot of time with the Excel 2016 for Windows preview. It looks to me a lot like Excel 2013, and what I did confirmed that it behaved very much like Excel 2013, both in Excel itself and in VBA. I spent more time with Excel 2016 for Mac, which also looks a lot like Excel 2013 for Windows, but the back end (VBA) has a lot of differences and a lot of shortcomings which Microsoft is hustling to address. I’ve had to figure out lots of workarounds to make my programs work the way I want them to, and in fact, some of my programs don’t really work yet.

For Excel 2016, Microsoft has introduced several new chart types, described in Introducing new and modern chart types now available in Office 2016 Preview. Several of them are also included in Peltier Tech Charts for Excel, including waterfalls, histograms, Paretos, and box plots. Should I be worried that I’ll lose business to Microsoft? Well, maybe not too worried. I will keep these charts in my product, because people may prefer my chart styles and defaults, and they may have written their own VBA to use my add-in to create charts. And of course, Peltier Tech Charts for Excel has lots more than these few charts.

I did “get to” foray into Office 365, because that was the only way to get either the Mac or Windows preview of Office 2016. That was a little frustrating, because I felt like I no longer had control over my account (seems I have half a dozen accounts with Microsoft, and I never guessed the right credentials whenever I had to log in), and updates could be an adventure. Recently, though, things seem to work more smoothly.

Here are a handful of links to articles on Microsoft’s Office Blog that talk about Office 2106, including topics related to business analytics and some new charts introduced in Excel 2016.

- What is new in business analytics – Excel 2016
- Helping business analysts take full advantage of Excel 2016
- 11 blogs on Power Map
- New charts: Treemap and Sunburst
- New charts: Visualize statistics with Histogram, Pareto, Box and Whisker
- New charts: Introducing Waterfall chart
- More details on forecasting models in Power View

Here are a few articles from users outside of Microsoft

- Excel 2016: Ten Heartwarming Improvements in which Rob Collie describes improvements to Power Pivot and other data features
- My three favorite things in Excel 2016 where my new colleague Gašper Kamenšek describes four new things in Excel 2016, that’s right, four, three of his favorites and one that everyone else seems excited by
- Chris Webb’s What’s New In The Excel 2016 Preview For BI?
- Excel 2016 for Mac review: Spreadsheet app can do the job—as long as you don’t rely on macros at Macworld

Microsoft is implementing a new development cycle. They’re going to move away from the 3- to 4-year new version cycle, and roll out more substantial updates monthly or so. This will help eliminate bugs and add features more rapidly. It also means they will make Office on all platforms increasingly compatible.

Microsoft has added a new communication feature. If you have a great idea for a new feature, you can suggest it at the Excel Idea Box at UserVoice.com. Other users can review, comment on, and vote on your ideas. I’ve posted Give us a proper NULL() worksheet function; please go and vote for it. I’ve also supported Bring VBA into the modern world, Link the min and max values of a chart axis to cell value, Make it easier to find external links, and Improve the Concatenate Function, among others, and they could also use your votes.

The post Microsoft Releases Excel 2016 for Windows appeared first on Peltier Tech Blog.

]]>You can skip this article and go straight to Peltier Tech Charts for Excel 3.0 to license your copy. From now until September 30, the 30th anniversary of Microsoft Excel, you can get $10 off using one of these coupon codes:

`NPTX30A10`

to get $10 off the Standard Edition`NPTX30B10`

to get $10 off the Advanced Edition

If you have a valid license to any version of the Peltier Tech Chart Utility 2.0 (Advanced or Standard, Windows or Mac), you are eligible for a 50% discount, on top of the $10 discount above. Email Peltier Tech for a discount coupon.

If you have a valid license to any of the older standalone Peltier Tech *Whatever* Chart Utilities (Waterfall, Cluster-Stack, Box Plot, etc.), you are eligible for a 25% discount, on top of the $10 discount above. Email Peltier Tech for a discount coupon.

If you’ve licensed the Peltier Tech Chart Utility in the past month or two, email Peltier Tech and we’ll work out a fair price for an upgrade.

There are several major changes and a lot of minor ones.

First, the utility has been updated to run in Excel 2016 for Windows (out next week) and for Mac (out last month).

Second, the same add-in can run in both Windows and Mac. In the past I had to support two different files, because of incompatibilities between Windows VBA and Mac VBA. But I dug into all of these incompatibilities and found ways around them. Users can now license the utility once and use it on both platforms.

Third, I’ve added a feature to the advanced version of the utility that summarizes your chart’s data, lets you select some or all series in a chart, then moves or resizes their data ranges by a number of rows or columns or to a different sheet in a single operation.

I built it for myself, then decided people might like it, so I put it into the program.

Fourth, I’ve added one new chart type, Clustered Box Plots. This is a nice enhancement to box plots, allowing categories to be grouped and color coded to show patterns more readily.

Although Peltier Tech Charts for Excel has been released, there are more things being done to it.

First, there is still a lot of testing to be done in Excel 2016 for Mac. A lot of bugs and inconsistencies have been addressed, but there are still incompatibilities, and there are features that need more development. Exporting charts as image files, for example, which is a victim of an increasingly harsh lockdown by Apple on its hardware.

Second, there are a lot more features, both chart types and other functions, under development. Tornado sensitivity charts, Gantt charts, panel charts, step charts, and run charts. Features to clean up charts, arrange charts and chart elements, extract chart data, quickly apply favorite series formats.

Third, under development are versions of this software that work directly in other Office applications, linking to your Excel data, but building charts in PowerPoint or Word.

Visit Peltier Tech Charts for Excel 3.0 to license your copy. From now until September 30, the 30th anniversary of Microsoft Excel, you can get $10 off using one of these coupon codes:

`NPTX30A10`

to get $10 off the Standard Edition`NPTX30B10`

to get $10 off the Advanced Edition

If you have a valid license to any version of the Peltier Tech Chart Utility 2.0 (Advanced or Standard, Windows or Mac), you are eligible for a 50% discount, on top of the $10 discount above.

If you have a valid license to any of the older standalone Peltier Tech *Whatever* Chart Utilities (Waterfall, Cluster-Stack, Box Plot, etc.), you are eligible for a 25% discount, on top of the $10 discount above.

If you are a user of any previous Peltier Tech product, email Peltier Tech for your discount coupon.

The post Peltier Tech Charts for Excel 3.0 appeared first on Peltier Tech Blog.

]]>We’re going to use a dummy (hidden) XY series on the chart, whose data labels will become the labels we want our axis to display.

We’ll start with two sets of data. The first (below left) is the data for the stacked columns, using simple counting numbers for the categories. The second (below right) is XY data where X is the list of values where we want our labels, and Y is zero.

Select the first set of data and insert a stacked column chart.

Copy the second range, select the chart, and use Paste Special (Home tab of the ribbon > Paste button dropdown > Paste Special), and select the following options: Add data as series, series in columns, series names in first row, categories in first column.

The new series is added as a third set of stacked bars, which don’t show up because their height is zero.

Select the added series by selecting the green bars and clicking the up arrow key. Click the menu key (between the right Alt and Ctrl buttons on most Windows keyboards) or hold Shift and click the F10 function key to pop up the context menu. Click Change Series Chart Type, and choose XY Scatter. This adds a set of markers along the bottom of the chart (I used blue circles in the chart below) and it adds secondary X and Y axes.

Format the scale of the secondary horizontal axis (top of chart) so it fits the data: min = -115, max = -50. Note that the blue circles are now aligned between the bars, where the labels will go.

Hide the secondary X and Y axes by formatting their label position as No Label, and their line color as No Line. This hides the labels and reduces the margin around the chart that previously held the labels.

Hide the primary horizontal labels by using a custom number format of ” ” (that’s right, a space surrounded by double quotes). This hides the labels but keeps the margin in place for the other labels we’re going to add.

Right-click the series of blue dots, and choose Add Data Labels. Excel adds the default Y values (zeros) to the right of the markers.

Format the labels so they are in the Below position, and so they show the X values instead of the Y values.

Finally format the series of dots so they use no markers. And we’re done.

This technique can be used to customize axis labels and add many other labels to your charts.

The post Column Chart with Category Axis Labels Between Columns appeared first on Peltier Tech Blog.

]]>*I want to visualize a series of prices on single axis:*

*I want to portray these in correct proportion, horizontally along a single line – much like points on a thermometer.*

These prices are going to be the X values. You need some kind of dummy Y values, so Excel knows where to put the points.

In the data range shown below, I’ve put a set of dummy values in the row below the prices, using 0.5 as a dummy value. I picked 0.5 to float the points in a line above the horizontal axis; if you do in fact want the points right on the axis, you could use 0.0 for the dummy Y values.

I also changed the product numbers to alphabetic labels for this illustration. Short names will work best as data labels in the chart.

I made a chart using the prices and the dummy Y values (the shaded cells in the sheet). That’s the big chart below the data.

First I shrunk the chart and the chart title, and changed the title to something more descriptive.

I removed the gridlines, and set the Y axes to a min of 0.0 and a max of 0.7, which made the labels fit better.

The Y axis scale is something that can be easily adjusted as needed. In fact, I didn’t really set it here, I set it later, after I saw how my chart looked with the labels. If you plot the points along (not above) the X axis, you may want to pick a Y axis minimum which is the negative of the maximum.

Next I hid the vertical axis by using no line color and no labels.

I also set the X axis scale to a min of 1.5, to spread out the points a bit.

Next I set the plotted points so the format varied by point; this setting is found where you set the fill color of the markers.

I also added data labels; by default Excel uses the values in the labels, and placed them above the points.

Finally I set up the custom labels I wanted. I used the X values instead of the default Y values and also used the Value from Cells option to get the product names into the labels, with a new line separating the cell values and the X values. These settings are shown in the Format Labels task pane shown below. Unfortunately if you do not have Excel 2013, you don’t have the Values from Cells option, but you have other ways to Apply Custom Data Labels in Excel Charts.

I also changed the font color of each label to match the corresponding point, and staggered labels where they were too close by placing a couple below the points.

The post Plot Values Along the Axis of an Excel Chart appeared first on Peltier Tech Blog.

]]>Microsoft Office 2016 for Mac and Microsoft Office 2016 for Windows have been released over the past couple of months.

Peltier Tech Chart Utility 2.0 (the current version) will not work in the commercial release of Office 2016, though it may load in Previews.

Peltier Technical Services has released an upgraded utility, **Peltier Tech Charts for Excel 3.0**, to correspond with the upgrade to Microsoft Office.

**Peltier Tech Charts for Excel 3.0** will work in Office 2007, 2010, 2013, and 2016 for Windows and Office 2011 and 2016 for Mac. Note that Office 2007 for Windows will no longer be “officially” supported after Microsoft’s Extended Support End Date of 10 October 2017, though the utility should still run fine.

In the past, the Peltier Tech Utility had separate add-ins for Windows and for Mac. Users of both platforms had to purchase both add-ins (at a discount, of course). In contrast, the **Peltier Tech Charts for Excel 3.0** will have a single add-in that runs equally well in both operating systems.

There will be a PowerPoint edition of **Peltier Tech Charts for Excel 3.0**, running right in PowerPoint. You will be able to insert and modify great charts right in PowerPoint, using your Excel data or entering your own in the PowerPoint chart’s datasheet. There may also be a Word edition of Peltier Tech Charts for Excel 3.0. Non-Excel versions of the utility will be available some time after the Excel version, and will be described elsewhere.

**Peltier Tech Charts for Excel 3.0** is not just a new number assigned to the same old program. There will be many new charts and features.

The upgraded utility will add simple Controls Charts. These will not replace the superb Statistical Process Control packages available for Excel, but they will satisfy the needs of users who only want a few simple run charts.

The utility will also introduce Grouped Box Plots, allowing multiple color-coded groups of boxes and whiskers.

Other chart types under consideration for the new utility are:

- Sensitivity Tornado Plots
- Floating Pareto Charts
- Gantt Charts
- Stacked Histograms and Cumulative Histograms
- Trellis Charts

Even though Microsoft Office 2016 will offer Waterfalls, Paretos, Histograms, and Box Plots (finally, right?), the Peltier Tech Utility will continue to offer these charts, for consistency and for users who are still using Excel 2013 and 2010.

Did I forget something? Let me know.

Numerous new features are being evaluated for **Peltier Tech Charts for Excel 3.0**, including:

- Move or Extend Chart Data for One or More Series
- Convert Pivot Charts to Regular Charts
- Extract Chart Data
- Enhanced Color Chooser
- Chart Deformatter (Cleaner)
- Regression with Confidence Intervals
- Chart Alignment Tools
- Series Namer
- Drag a Point to Change its Data
- Chart Zoomer: Draw a Box to Rescale Axes or Highlight Points
- Directory Tools
- Updates: when a new update is available, the utility will notify you, then install the new software

Something missing? Tell me about it.

There are **Standard** and **Advanced** Editions of **Peltier Tech Charts for Excel 3.0**. The Standard Edition has a lot of great custom charts and features, and the Advanced Edition has twice as much. A few features now found in the Advanced Edition may be migrated to the Standard Edition. New features developed during the Beta program and later will be introduced to the Advanced Edition.

The **Peltier Tech Charts for Excel 3.0** Beta program has concluded.

You can read about the new utility and get your license at the following link:

Peltier Tech Charts for Excel 3.0

1a. Developer tab > Add-Ins

1b. Tools menu > Add-Ins

2. Browse > navigate to folder where the add-in file was downloaded, click OK a couple times to return to Excel

3. If new buttons don’t appear on a new Add-Ins tab, restart Excel, and they will become available. This glitch will be fixed when Microsoft finishes making the Mac ribbon fully customizable, sometime over the next few weeks or months.

The Peltier Tech Charts for Excel 3.0 Add-ins tab in Mac Excel 2016, showing (top to bottom) Main, Chart Data, Chart Format, Export, and Misc toolbars. This arrangement of multiple toolbars was necessitated because Mac Excel 2016 does not yet support a custom ribbon tab, yet it no longer supports custom menus. Click the image above to view full-size in a new browser tab.

Below is the dialog for selecting a specific custom chart type, full size.

1. Tools menu > Add-Ins

2. Select > navigate to folder where the add-in file was downloaded, click OK a couple times to return to Excel

3. A menu will appear on the main Excel menu bar at the top of the screen, without the need to restart.

The Peltier Tech Charts for Excel 3.0 Menus in Mac Excel 2011. Click the image above to view full-size in a new browser tab.

1a. Developer tab > Add-Ins

1b. Ribbon > File tab > Options > Add-Ins > select Excel Add-Ins in the dropdown > Go

2. Browse > navigate to folder where the add-in file was downloaded, click OK a couple times to return to Excel

3. A Peltier Tech tab will appear on the Excel ribbon, without the need to restart.

1. Large round Office button in top left of screen > Excel Options > Add-Ins > select Excel Add-Ins in the dropdown > Go

2. Browse > navigate to folder where the add-in file was downloaded, click OK a couple times to return to Excel

3. A Peltier Tech tab will appear on the Excel ribbon, without the need to restart.

The Peltier Tech Charts for Excel 3.0 Ribbon in Windows. Click the image above to view full-size in a new browser tab.We expect that Excel 2016 for Mac will closely resemble this ribbon.

Find a bug? Think of a feature that is missing? Mention it in the comments below.

Include details like which version of Excel you’re using, what feature you were trying to use, what you expected to happen, and what did happen. Include error messages (the text of the message, not just the number). If I contact you I may ask for a screenshot of the error, and a copy of your data including the problematic output.

Include your email when posting your comment. I’m the only one who can see it, so it will help me follow up but will not let anyone else spam you.

The **Peltier Tech Charts for Excel 3.0** beta program has concluded.

You can read about it and get your license at the following link:

Peltier Tech Charts for Excel 3.0

Existing users of Peltier Tech software will get a healthy discount for upgrading. Send an email to Peltier Tech to request a coupon code for your discount.

The post Peltier Tech Utility 3.0 for Office 2016 appeared first on Peltier Tech Blog.

]]>I have written a tutorial showing how to create an Excel Chart With Colored Quadrant Background, which was more complicated, as it used stacked areas and secondary axes to get the colored background. This is much simpler to create and maintain, and serves much the same purpose.

Here is the sample X and Y data, with calculated averages, and the initial XY scatter chart. We will position the axes at the respective averages of the X and Y data, though you can position them wherever it makes sense in your analysis.

We need to reposition the axes of this chart. Double click the horizontal axis, or select the horizontal axis and press Ctrl+1 (numeral one), to open the Format Axis task pane (shown here, Excel 2013) or Format Axis dialog (works much the same in earlier Excel versions). Under Axis Options >Vertical Axis Crosses, select the Axis Value option, and enter the X average into the box, as shown.

The result is shown below left. Repeat for the vertical axis, below right.

Those axis labels are totally in the way, but it’s easy to move them. Format each axis (open the task pane or dialog as above) and under Labels > Label Position, select Low from the dropdown.

Now those labels are along the edges of the chart, where they do more good than harm (below left). You can do a small amount of formatting to make the quadrants stick out a bit more clearly. In the chart below right, I’ve used a lighter shade of gray for the gridlines, and I’ve used a darker color, in fact, the same color as the markers, for the axis line color.

It is easy to use VBA to position the axes and axis labels, using a simple routine like that shown below. This routine positions the labels, then uses the averages calculated in the worksheet to position the axis lines.

```
Sub AxesAsQuadBoundaries1()
With ActiveChart
With .Axes(xlCategory)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = ActiveSheet.Range("A17").Value2
End With
With .Axes(xlValue)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = ActiveSheet.Range("B17").Value2
End With
End With
End Sub
```

The next routine skips the worksheet calculations, instead taking the averages of the X and Y values plotted in the chart to position the axis lines.

```
Sub AxesAsQuadBoundaries2()
Dim vData As Variant
With ActiveChart
vData = .SeriesCollection(1).XValues
With .Axes(xlCategory)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
vData = .SeriesCollection(1).Values
With .Axes(xlValue)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
End With
End Sub
```

You can even use worksheet events to reposition the chart axes. My X and Y values were calculated using `=RANDBETWEEN(2,14)`

, so whenever the worksheet calculates (e.g., by pressing the F9 function key), the values change. I can tap into this calculation event as follows.

- Right click on the worksheet tab, and select View Code from the popup menu. The VB Editor opens with a code module corresponding to the worksheet.
- Select Worksheet from the left hand dropdown at the top of the new code module.
- Select Calculate from the right hand dropdown.
- Enter the code as shown.

Here is the code so you don’t need to type it all yourself. Simply copy and paste into the worksheet’s code module.

```
Private Sub Worksheet_Calculate()
Dim vData As Variant
With Me.ChartObjects(1).Chart
vData = .SeriesCollection(1).XValues
With .Axes(xlCategory)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
vData = .SeriesCollection(1).Values
With .Axes(xlValue)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
End With
End Sub
```

Depending on the details of your worksheet model, you could use the `Worksheet_Calculate`

, `Worksheet_Change`

, `Worksheet_PivotTableUpdate`

, or other event procedures to update the chart.

The post Simple XY Quad Chart Using Axes as Quadrant Boundaries appeared first on Peltier Tech Blog.

]]>