Solve xkcd’s Velociraptor Problem with Excel

Velociraptor

The xkcd Velociraptor Problem #1

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

Velociraptor Problem - xkcd Setup

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

Calvin and Hobbes
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).

Approach 1. Using Excel Table with Simple Formulas

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.

Velociraptor Problem - Simple Excel Table - Initial

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.

Velociraptor Problem - Defined Names

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.

Velociraptor Problem - Simple Excel Table - In Progress

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

Velociraptor Problem - Simple Excel Table - End of Chase

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

Velociraptor

Approach 2. Using Excel Table with More Detailed Formulas

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.

Velociraptor Problem - Detailed Excel Table - Initial

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

Velociraptor Problem - Detailed Excel Table - End of Chase

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.

Velociraptor Problem - Detailed Excel Table - Chart

Velociraptor

Approach 3. Using Excel VBA

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.

Velociraptor Problem - Excel VBA - Initial

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.

Velociraptor Problem - Excel VBA - After 2 sec

Here is the chase after four seconds.

Velociraptor Problem - Excel VBA - After 4 sec

And here is the chase at its conclusion.

Velociraptor Problem - Excel VBA - End of Chase

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

Velociraptor

VBA: Accuracy vs. Calculation Load

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.

Numberical Analysis - Accuracy and Calculation Time

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.

Numberical Analysis - Accuracy and Calculation Time

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.

Numberical Analysis - Accuracy and Calculation Time

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.

Numberical Analysis - Accuracy and Calculation Time

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

Numberical Analysis - Accuracy and Calculation Time

 

Velociraptor - Wyoming Dinosaur Center

 

Peltier Tech Charts for Excel

Simple XY Quad Chart Using Axes as Quadrant Boundaries

This article will show how easy it is to create a scatter chart with its plot area divided horizontally and vertically into four regions. The regions are separated by the chart axes, and these axes can be positioned where needed to demarcate the quadrants.

Quad Chart Using Axes as Quadrant Boundaries

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.

Quad Chart by Axes - Data and Chart

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.

Format Axis Task Pane - Axis Crosses At

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

Quad Chart by Axes - Position Axes

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.

Format Axis Task Pane - Label Position Low

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.

Quad Chart by Axes - Position Labels and Reformat

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.

Worksheet_Calculate Event to Keep Axes in Position

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.

 

Peltier Tech Charts for Excel

Structured Referencing to Identify Parts of Excel Tables

Guest post by Zack Barresse & Kevin Jones
Data Automation Professionals, LLC

Zack and Kevin are VBA ninjas who have been helping people around the internet for several years. They’ve combined resources and started a company, Data Automation Professionals, which helps Excel users automate simple to complex tasks, consults on projects, and teaches the world VBA. Zack has been hanging around forums like Mr Excel and VBA Express for several years, and maintains the blog at exceltables.com. Kevin is an engineer who has been spotted around the net using the online moniker ‘zorvek’. Together they’ve written a book on Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables.

With the introduction of Tables in Excel 2007 (Tables are a re-invention of Lists, introduced in Excel 2003), we were also provided a new syntax for referencing Tables and the parts within those Tables. This new syntax is called structured referencing. The reason a new referencing method is required is because Tables are very dynamic, and the traditional cell referencing syntax would not allow robust referencing without clever use of functions as Tables as data is added and removed. As you will see in this article, structured referencing is a very powerful tool that makes your formulas dynamic while maintaining significant simplicity. If you’re not familiar with Tables, a good starting point is this blog post (with video) by Excel MVP Jon Acampora.

Tables play an integral part of modern Excel. They’re very organized, controlled, and most importantly, they have rules. These allow for a lot of built-in functionality previously unavailable. Functionality such as good data structuring, dynamic chart ranges, dynamic PivotTable sources, etc. Additionally there is some default behavior which can be nice such as banded rows and columns, cell formatting that is added to every new row of a table, and new rows auto-populating with formulas.

All examples in this article are for Excel 2010 and later. There is a slight difference between using structured referencing in Excel 2010 and Excel 2007—Excel 2007 is not covered in this article. For the purposes of discussion, the traditional method of referencing cells (i.e. A1, A2, B1:B100, A2:D100, etc.) is referred to as standard referencing.

Structured Referencing

Before we get too in-depth here, let’s make sure we have a good understanding of what is meant by structured referencing. Structured referencing makes it easier and more intuitive to work with cell references in Tables. It allows you to reference a Table’s parts such as the columns, header rows, and total rows without using standard referencing (R1C1 or A1 syntax) but rather by using the Table’s name and other constants such as column header values which makes references easier to read because recognizable names are used. This eliminates the need to use complex formulas or rewrite formulas when the Table structure is changed or data is added or removed. Formula audits are also made much easier.

Let’s look at a quick example of referencing the hours, let’s say this is stored in column A, and the rate, stored in column B. The standard referencing formula to calculate the total billable amount in row 2 is:

=A2*B2

The structured referencing formula in row 2 (and in all other rows) is:

=[@Hours]*[@Rate]

As you can see, with structured referencing it is much clearer what the formula is doing than with standard referencing. With standard referencing, we have to navigate to the source to determine what, exactly, is in cell A2 and B2. With structured referencing, we have greater transparency with makes development, maintenance, and auditing easier. In today’s world of Excel, with groups like EuSpRIG and heightened auditing and maintenance requirements, transparency in spreadsheets is as important as it’s ever been, and structured referencing goes a long ways to assist.

In the examples below, we will look at a simple Table which has three columns and six rows, with both the header and total rows showing. The image below is used for the next examples.

Starting Point for Table Formula Examples

Before we get too in-depth on how the referencing actually takes place, there are a few rules to structured referencing which must be identified. In any single structured reference you may have a

  • Table name,
  • special identifier, or
  • column name.

Generally only the column name is required, but every structured reference will have some combination of these three elements. Below are the basic rules of when to use these elements.

  • The Table name used if
    • more than one column of a Table is being referenced, or
    • the column is being referenced from outside of the Table.
  • A special identifier is used if a specific part of the Table is being referenced, i.e. the total row.

A key part of structured referencing is the use of square brackets. Square brackets are used to identify a reference as a structured reference versus a standard reference. Every structured reference (except the Table name itself) is enclosed in a set of square brackets. There are two occasions where you will have an additional set of square brackets.

Single-column cell reference

Column name of “Column” (no quotes used as the cell value) has a reference of:

[@Column]

Column name of “A Column” (no quotes used as the cell value—note the space) has a reference of:

[@[A Column]]

Multi-column cell reference

TableName[@[Column1]:[Column2]]

In a multiple column reference, Excel will automatically place a separate set of square brackets around each column name regardless of whether or not there is a space or other special character in the name, as well as append the Table name to the reference. This is to identify it as an individual column within a multiple column reference.

Additionally, below are the characters which Excel identifies and automatically puts an additional set of square brackets around.

  • Space ( )
  • Line feed
  • Carriage return
  • Comma ( , )
  • Colon ( : )
  • Period ( . )
  • Left bracket ( [ )
  • Right bracket ( ] )
  • Pound sign ( # )
  • Single quotation mark (apostrophe) ( ‘ )
  • Quotation mark (  )
  • Left curly bracket (brace) ( { )
  • Right curly bracket (brace) ( } )
  • Dollar sign ( $ )
  • Caret ( ^ )
  • Ampersand ( & )
  • Asterisk ( * )
  • Plus sign ( + )
  • Minus sign ( – )
  • Equal sign ( = )
  • Greater than ( > )
  • Less than ( < )
  • Division ( / )

This Row

The ampersand character (@) is used to identify “This Row” in a structured reference. This is also known as the implicit intersection of the row in which the reference resides.

Special Characters

Excel uses special characters to qualify structured references (discussed later in more detail) and, when these characters are included as part of a column name, they need to be “escaped” so that Excel does not interpret it as a special reference qualifier. The apostrophe ( ‘ ) is used for this escaping.

Below is a list of all special characters used to qualify structured references and must be preceded by an apostrophe when part of a column header.

  • Left bracket ( [ )
  • Right bracket ( ] )
  • Pound sign ( # )
  • Single quotation mark (apostrophe) ( ‘ )
  • At sign ( @ )

Special Identifiers

When attempting to reference specific parts of a Table, you will need to use a special identifier. There are only five. Let’s take a look at each of them and a picture for visual reference. In the following examples the referenced area is outlined with a red box.

[#All]

Table Special Identifier [#All]

[#Headers]

Table Special Identifier [#Headers]

[#Data]

Table Special Identifier [#Data]

[#Totals]

Table Special Identifier [#Totals]

@ (or [#This Row] in Excel 2007)

Table Special Identifier @ or [#This Row]

Note that when referencing Table parts which are not visible or enabled such as when a header or total row isn’t showing, the reference will evaluate to a reference error (#REF!). Let’s look at an example formula:

=TableName[[#Headers],[Column 3]]

The above formula references the Table named “TableName”, with the special identifier of the header of “Column 3”. This is a single-cell reference. If the header row is showing the result will be the column name or, in this case, “Column 3”. If the header row is not showing the result will be “#REF!”.

This means we can use formulas to tell if a header or total row is visible or not.

Header row visible formula:

=IF(ISERR(TableName[[#Headers],[Column 3]]),"No","Yes")

Total row visible formula:

=IF(ISERR(TableName[[#Totals],[Column 3]]),"No","Yes")

Another Table part which can possibly not exist is the body. It’s important to note this will not affect formula evaluation, but does have a serious impact in VBA which is covered later in this article.

In Data Validation

Using Tables as a source for an in-cell drop down can simplify your spreadsheets, but Table ranges cannot be referenced directly in the data validation source formula. Instead you must name the range and use that name in the validation formula. To do this, navigate to the FORMULAS tab and click ‘Name Manager’, then click ‘New’.

Since the name of any Table must be unique  for the entire workbook, you cannot name a range the same as a Table name. This is why some people either use Hungarian Notation with naming Tables, or preface all Table names with a “t”. For example, if you have a Table with a list of countries, instead of naming the Table “Countries”, you would name it “tCountries”. This way if you wanted to have those countries as a data validation drop-down list, you could name a range of “Countries” which points to the Table range. Here is an example:

Name: “Countries”

Refers to: “=tCountries[Countries]”

In Charts

Putting your data into Tables and using structured referencing makes it easier to create dynamic charts that change as the data in the Table changes. As to not reinvent the wheel, we’ll refer you to Jon Peltier’ great post about this topic Easy Dynamic Charts Using Lists or Tables. The takeaway from the blog post is Tables make great data sources for charts which grow and shrink as the source data set changes. It’s a nice alternative from having to manually create dynamic named ranges.

In Formulas

Putting all of this information into practice can be confusing. Let’s look at some specific structured referencing syntax examples in formulas. Keep in mind that structured references always evaluate to a range of cells and are treated like any other range reference in Excel, so if you’re referencing more than a single cell it must be used as an aggregate, range array, or lookup array, depending on the formula to which they are being passed.

There are five specific locations we can reference:

  • Body column
  • Entire column
  • Column header
  • Column total
  • Column cell

The examples below assume the Table’s name is “TableName”, using column “Column Name” where applicable.

Specific Column Body or Data Excluding Header and Total Rows

TableName[Column Name]

Specific Column Including Header and Total Rows

TableName[[#All],[Column Name]]

All Data Excluding Header and Total Rows (Data Body)

TableName

All Data Including Header and Total Rows

TableName[#All]

Row Across All Columns

TableName[@]

Row for One Column (Single Cell)

TableName[@[Column Name]]

Header Row Across All Columns

TableName[#Headers]

Header Row for One Column

TableName[[#Headers],[Column Name]]

Total Row Across All Columns

TableName[#Totals]

Total Row for One Column

TableName[[#Totals],[Column Name]]

Let’s assume we have a column in our Table which is titled “Column 3”. Below are examples of the types of structured references we can use to reference specific parts of that column.

Specific Column Body or Column Data Excluding Header and Total Rows

TableName[Column 3]

Referencing Table Column Excluding Header and Total Rows

Specific Column Including Header and Total Rows

TableName[[#All],[Column 3]]

Referencing Table Column Including Header and Total Rows

Header Row for One Column

TableName[[#Headers],[Column 3]]

Referencing Header Row for One Column

Total Row for One Column

TableName[[#Totals],[Column 3]]

Referencing Total Row for One Column

Row for One Column (Single Column Cell)

TableName[@Column 3]

Referencing One Cell

In VBA

There are two basic methods for referencing Tables and parts of Tables from VBA. The easiest is to use the same syntax as described above for formulas where you pass the Table information as a text string to the Range object. The other method is to use the Excel object models ListObject and its child properties and methods. Both are described below.

Using Range and Evaluate

Just as with standard referencing, the Range object evaluates structured references passed as a text string. For example, to reference the body or data portion of “Column 3” in the Table named “TableName” using the Range method:

Range("TableName[Column 3]")

Note that no qualifying worksheet is required because the table name has to be unique across all worksheets. This is the referencing style you will see when using the Macro Recorder.

Using the Object Model’s ListObject

To reference any part of a Table using the Excel object model, we have to first identify the Table object itself. The ListObject object is how Excel exposes a Table in the Excel object model. It is contained in the collection ListObjects which  is a child of the Worksheet object. Use this syntax to reference a specific Table on a worksheet:

ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")

ListObjects, being a collection of list objects or Tables, can also be accessed with an index number:

ThisWorkbook.Worksheets("Sheet1").ListObjects(1)

The index number of the Table is the order in which it was created on the worksheet and is a read-only property.

Once we have the Table’s ListObject object we can access and manipulate any part of that Table. The more commonly used properties and methods are discussed below. Each example starts with this code:

Dim Table1 As ListObject
Set Table1 = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")

Range Property

The Range property returns the entire Table as a range object including the header and total rows.

Dim Table1Range As Range
Set Table1Range = Table1.Range

HeaderRowRange Property

The HeaderRowRange returns the Table’s header row as a Range object. The range is always a single row – the header row – and extends over all Table columns. When the header row is not showing this property returns Nothing.

Dim Table1HeaderRowRange As Range
Set Table1HeaderRowRange = Table1.HeaderRowRange

DataBodyRange Property

The DataBodyRange returns the Table’s body as a Range object. The range is every row between the header and the total row and extends over all Table columns.

Dim Table1DataBodyRange As Range
Set Table1DataBodyRange = Table1.DataBodyRange

When the Table does not contain any rows the DataBodyRange property returns Nothing. This may be confusing when looking at the worksheet as it will appear as if a single row exists. This is the only case when the property InsertRowRange returns a range which can be used to insert a new row. Effectively InsertRowRange and DataBodyRange are equivalent.

TotalRowRange Property

The TotalRowRange returns the Table’s total row as a Range object. The range is always a single row – the total row – and extends over all Table columns. When the total row is disabled this property returns Nothing.

Dim Table1TotalRowRange As Range
Set Table1TotalRowRange = Table1.TotalRowRange

InsertRowRange Property

The InsertRowRange returns the Table’s current insertion row as a Range object only when the Table DataBodyRange object is Nothing (the Table has no data rows); it’s Nothing when the DataBodyRange is a Range (the Table has one or more data rows). While this range was always the last data row in Excel 2003 (the row with the asterisk), it was partially depreciated in Excel 2007 and remains so in 2013. In Excel 2007 and later versions it only returns the first data row and only when the Table does not contain any data. Otherwise it returns Nothing.

Two additional properties or collections provide access to the rows and columns in the Table. Each collection provides access to all of the ListRow and ListColumn objects in the Table. Each ListRow and each ListColumn object has properties and methods.

ListRows Property

The ListRows property returns a collection of the rows in the Table’s DataBodyRange as a ListRows object type. It behaves very much like a Collection object and contains a collection of all the ListRow objects. Rows are referenced only by a one-based index number relative to the first row. An empty Table has no rows. The ListRows.Add method can be used to insert new rows.

Debug.Print "The Table has " & Table1.ListRows.Count & " rows."

ListColumns Property

The ListColumns property returns a collection of the columns in the Table as a ListColumns object. It behaves very much like a Collection object and contains a collection of all the ListColumn objects. Columns are referenced by a one-based index number relative to the first column or the column header. The ListColumns.Add method can be used to insert new columns.

Debug.Print "The Table has " & Table1.ListColumns.Count & " columns."

 

Peltier Tech Charts for Excel

Apply Custom Data Labels to Charted Points

Often you want to add custom data labels to your chart. The chart below uses labels from a column of data next to the plotted values.

Simple Chart with Custom Data Labels

When you first add data labels to a chart, Excel decides what to use for labels—usually the Y values for the plotted points, and in what position to place the points—above or right of markers, centered in bars or columns. Of course you can change these settings, but it isn’t obvious how to use custom text for your labels.

This chart is the starting point for our exercise. It plots simple data from columns B and C, and it displays only the default data labels, showing the Y values of each point.

Simple Chart with Default Data Labels

There are a number of ways to apply custom data labels to your chart:

  • Manually Type Desired Text for Each Label
  • Manually Link Each Label to Cell with Desired Text
  • Use the Chart Labeler Program
  • Use Values from Cells (Excel 2013 and later)
  • Write Your Own VBA Routines

Manually Type Desired Text for Each Label

The least sophisticated way to get your desired text into each label is to manually type it in.

Click once on a label to select the series of labels.

Simple Chart with Data Labels Selected

Click again on a label to select just that specific label.

Simple Chart with Specific Data Label Selected

Double click on the label to highlight the text of the label, or just click once to insert the cursor into the existing text.

Simple Chart with Data Label Text Selected

Type the text you want to display in the label, and press the Enter key.

Simple Chart with Data Label Text Being Typed

Repeat for all of your custom data labels. This could get tedious, and you run the risk of typing the wrong text for the wrong label (I initially typed “alpha” for the label above, and had to redo my screenshot).

One thing that makes this approach unsophisticated is that the typed labels are not dynamic. If th text in one of the cells changes, the corresponding label will not update.

Manually Link Each Label to Cell with Desired Text

Select an individual label (two single clicks as shown above, so the label is selected but the cursor is not in the label text), type an equals sign in the formula bar, click on the cell containing the label you want, and press Enter. The formula bar shows the link (=Sheet1!$D$3).

Simple Chart with Data Label Link Being Entered

Repeat for each of the labels. This could get tedious, but at least the labels are dynamic. If the text in one of the cells changes, the corresponding label updates to show the new text.

Use the Chart Labeler Program

Brilliant Excel jockey and former MVP Rob Bovey has written a Chart Labeler add-in, which allows you to assign labels from a worksheet range to the points in a chart. It is free for anyone to use and can be downloaded from http://appspro.com. Rob colls it the XY Chart Labeler, but it actually works with any chart type that supports data labels.

When installed, the add-in adds a custom ribbon tab with a handful of useful commands. The tab is added at the end of the ribbon, but being pressed for space I moved it digitally to the beginning.

XY Chart Labeler Ribbon Tab

With a chart selected, click the Add Labels ribbon button (if a chart is not selected, a dialog pops up with a list of charts on the active worksheet). A dialog pops up so you can choose which series to label, select a worksheet range with the custom data labels, and pick a position for the labels.

XY Chart Labeler Dialog

If you select a single label, you can see that the label contains a link to the corresponding worksheet cell. This is like the previous method, but less tedious and much faster.

XY Chart Labeler Dialog

Use Values from Cells (Excel 2013 and later)

After years and years of listening to its users begging, Microsoft finally added an improved labeling option to Excel 2013.

First, add labels to your series, then press Ctrl+1 (numeral one) to open the Format Data Labels task pane. I’ve shown the task pane below floating next to the chart, but it’s usually docked off to the right edge of the Excel window.

Format Data Labels Task Pane

Click on the new checkbox for Values From Cells, and a small dialog pops up that allows you to select a range containing your custom data labels.

Format Data Labels Task Pane

Select your data label range.

Format Data Labels Task Pane

Then uncheck the Y Value option. I also uncheck the Show Leader Lines option, which is another enhancement added in Excel 2013. Leader lines are hardly ever useful for the charts I make, but many users are happy with them.

Format Data Labels Task Pane

While these data labels are not explicitly linked to worksheet cells as in the previous approaches, they still reflect any changes to the cells that contain the labels.

Write Your Own VBA Routines

I’ve put together a couple little routines that help with data point labeling. These are quick and dirty, because sometimes that’s all that you need. Also, writing your own code allows you to streamline your workflow according to your specific requirements.

Add Data Labels from Range Selected by User

This routine first makes sure a chart is selected, then it determines which series is to be labeled. It asks the user to select a range using an InputBox, and if the user doesn’t cancel it adds a label to the series point by point, linking the label to the appropriate cell.

Sub AddLabelsFromUserSelectedRange()
  Dim srs As Series, rng As Range, lbl As DataLabel
  Dim iLbl As Long, nLbls As Long

  If Not ActiveChart Is Nothing Then
    If ActiveChart.SeriesCollection.Count = 1 Then
      ' use only series in chart
      Set srs = ActiveChart.SeriesCollection(1)
    Else
      ' use series associated with selected object
      Select Case TypeName(Selection)
        Case "Series"
          Set srs = Selection
        Case "Point"
          Set srs = Selection.Parent
        Case "DataLabels"
          Set srs = Selection.Parent
        Case "DataLabel"
          Set srs = Selection.Parent.Parent
      End Select
    End If

    If Not srs Is Nothing Then
      ' ask user for range, avoid error if canceled
      On Error Resume Next
      Set rng = Application.InputBox( _
          "Select range containing data labels", _
          "Select Range with Labels", , , , , , 8)
      On Error GoTo 0

      If Not rng Is Nothing Then
        ' point by point, assign cell's address to label
        nLbls = srs.Points.Count
        If rng.Cells.Count < nLbls Then nLbls = rng.Cells.Count
        For iLbl = 1 To nLbls
          srs.Points(iLbl).HasDataLabel = True
          Set lbl = srs.Points(iLbl).DataLabel
          With lbl
            .Text = "=" & rng.Cells(iLbl).Address(External:=True)
            .Position = xlLabelPositionRight
          End With
        Next
      End If
    End If
  End If
End Sub

Add Data Labels from Row or Column Next to Y Values

This routine first makes sure a chart is selected, then it determines which series is to be labeled. It doesn’t bother the user, instead the routine parses the series formula to find the range containing the Y values, and if this is a valid range, it finds the next column or row, depending on the orientation of the Y values range. The code then adds a label to the series point by point, linking the label to the appropriate cell.

Sub AddLabelsFromRangeNextToYValues()
  Dim srs As Series, rng As Range, lbl As DataLabel
  Dim iLbl As Long, nLbls As Long
  Dim sFmla As String, sTemp As String, vFmla As Variant

  If Not ActiveChart Is Nothing Then
    If ActiveChart.SeriesCollection.Count = 1 Then
      ' use only series in chart
      Set srs = ActiveChart.SeriesCollection(1)
    Else
      ' use series associated with selected object
      Select Case TypeName(Selection)
        Case "Series"
          Set srs = Selection
        Case "Point"
          Set srs = Selection.Parent
        Case "DataLabels"
          Set srs = Selection.Parent
        Case "DataLabel"
          Set srs = Selection.Parent.Parent
      End Select
    End If

    If Not srs Is Nothing Then
      ' parse series formula to get range containing Y values
      sFmla = srs.Formula
      sTemp = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
      vFmla = Split(sTemp, ",")
      sTemp = vFmla(LBound(vFmla) + 2)
      On Error Resume Next
      Set rng = Range(sTemp)

      If Not rng Is Nothing Then
        ' use next column or row as appropriate
        If rng.Columns.Count = 1 Then
          Set rng = rng.Offset(, 1)
        Else
          Set rng = rng.Offset(1)
        End If

        ' point by point, assign cell's address to label
        nLbls = srs.Points.Count
        If rng.Cells.Count < nLbls Then nLbls = rng.Cells.Count
        For iLbl = 1 To nLbls
          srs.Points(iLbl).HasDataLabel = True
          Set lbl = srs.Points(iLbl).DataLabel
          With lbl
            .Text = "=" & rng.Cells(iLbl).Address(External:=True)
            .Position = xlLabelPositionRight
          End With
        Next
      End If
    End If
  End If
End Sub

 

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0