Loading...

Follow Peltier Tech Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid
Gantt Chart with Labels and Gridlines on First of Month
Simple Gantt Charts

A year ago I wrote an article about Gantt Charts in Microsoft Excel. It was pretty comprehensive, showing how to make simple Gantt charts using worksheet conditional formatting and using regular Excel stacked bar charts, and it covered some advanced techniques for adding embellishments, such as a reference line for a given date, multiple bars showing percent complete of a task, and milestone markers at the end of the bars.

Simple Excel Gantt Chart using Conditional Formatting in a Worksheet
Simple Excel Gantt Chart using Stacked Bar Chart
Simple Excel Gantt Chart starting with a Stacked Bar Chart

These Gantt charts all looked great, but a clever reader asked about a nicer axis scale across the bottom of the chart. In my example above, I had a tick label and vertical gridline every week, which is great for the time scale of my data. But what if the chart spans a longer time, for example, a year, and I want a label and gridline at the first of every month.

Gantt Chart with Wrong Monthly Date Scale

Below is a similar data set, but spanning a year rather than a few months.

Following the protocol in the earlier tutorial, we can build a Gantt chart like this one. Never mind the steps, because we’ll have to do it differently. Read on.

The chart spans the year nicely, but the axis scale is not what we’d like. When setting the bar chart’s axis scale, I can’t just say, put a label every month on the first. I have to pick a set number of days. Above I tried 31 days, which worked for January and February, but quickly went awry.

Below you can see the 31-day axis spacing, and a 30 day spacing, which is also wrong. If we recall that 365 days divided by 12 is 30.41666…, we might try 30.4 days, in the bottom scale, and it almost works.

Excel Line charts can give us a nice axis scale. Let’s have a look.

Line Chart Date Axes

In the first example, we had a bar chart with a horizontal axis date scale that had a label every 7 days. The horizontal axis in a bar chart is a value axis.

We can use that same data to create a line chart, with the same 7-day axis spacing. The horizontal axis in a line chart is a category axis, in this case, a date-scale axis. The axis can have the same min and max as the bar chart’s value axis above, but there are more formatting options.

Below are the Format Axis task panes for the bar chart’s horizontal value axis (left) and the line chart’s horizontal date axis (right).

In the bar chart, the minimum and maximum are the numbers 43184 and 43289, which are actually the serial numbers (days since 1 January 1900) of the min and max dates in the line chart, 3/25/2018 and 7/8/2018. The
major unit (axis tick spacing) in the bar chart is 7, and in the line chart is 7 days. So these two axes are the same.

In the current example, we have a bar chart with a horizontal axis date scale that had a label every 31 days. Not exactly every month.

Again, we can use that same data to create a line chart, with monthly axis spacing. But here, the spacing is exactly monthly.

Now we can see the real difference in the axis scales. Here are the Format Axis task panes as before.

In the bar chart, the minimum and maximum are the numbers 43466 and 43831, which are the serial numbers of the min and max dates in the line chart, 1/1/2019 and 1/1/2020. The major unit (axis tick spacing) in the bar chart is 31, which is only appropriate for some months, while in the line chart it is 1 month. Excel uses this setting and adds the appropriate number of days, 31, 30, 29, or 28, so the tick labels are one month apart.

Gantt Chart with Nice Monthly Date Scale

Sometimes different chart types can share incompatible axis types, but here we are not so lucky. We need to create a chart with bar chart series to display the Gantt chart bars, and we need to combine this with a line chart to produce the date axis with the desired tick spacing. It’s a lot of steps, but not too complicated, and for the most part, the order you apply them is immaterial.

The Data

Let’s start with the data, below. On the left is the data needed to create the Gantt chart itself, and on the right is the data for the axis scale. The Gantt data has Task names, Start dates, Duration, and End dates; only the first three columns are needed. The Axis Scale data has the first and last dates in the first column, dummy values of zero in the second column, and serial numbers of the dates in the third column. The formula in the third column links to the first, but the cells are formatted not as a Date but as General.

Create and Format the Chart

Select the first three Gantt columns, and insert a bar chart.

Let’s format the bars and get it over with. Start has no fill, so it’s hidden, and Duration is given the desired color. I have applied a 50% transparency to Duration, so the gridlines show through the bars.

Format the vertical category axis (showing task names), choosing the Categories in reverse order. In the simple Gantt chart from the earlier tutorial, we also moved the time axis to the bottom of the chart, but we’ll keep them at the top for this chart.

Now add the data for the dummy axis. Copy the first two columns of the right hand data block shown above, with dates and dummy zero values. Select the chart, then go to the Home tab of the ribbon, click the Paste dorpdown, and choose Paste Special from the bottom of the menu. Make sure these options are selected: Add Cells as New Series, By Column, Series Names in First Row, Categories in First Column.

The series doesn’t appear as much: it is a new set of stacked bars, but the values of zero means the bars have zero length and don’t appear. There is a new entry in the legend.

Right click on any series in the chart, and choose Change Series Chart Type. All three series are listed as Stacked Bar on the Primary Axis.

Click the Chart Type dropdown for the series named Axis, and select a Line type (I used the line with markers subtype so the series shows more clearly in the following steps).

Excel automatically changes the Axis series from Primary to Secondary Axis.

Here’s our combination chart.

We don’t need the legend any more, so delete it.

Format the Date Axes

Now add the secondary horizontal axis, the whole reason we added the dummy Axis series. Easiest way: select the chart, click on the “+” icon that appears next to the chart, click the right-pointing triangle next to Axes, and check the Secondary Horizontal box.

Now move the new axis to the bottom. Format the secondary vertical axis, on the right edge of the chart. Under Horizontal Axis Crosses, choose Automatic.

Now double-click on the bottom horizontal date axis to format it. Make sure the minimum and maximum are correct, change Base Units to Days, and change Major Units to 1 Month. Under Axis Position, choose On Tick Marks. Also, click under the paint can, and choose No Line for the axis.

Change the Number Format of the axis labels. I like to use a two-line format, with the day above the month. You can get a line feed in the middle of a number format in the worksheet, by typing Ctrl+J in the Number Format box, and then use this data for the axis and check Linked to Source in the Format Axis task pane. Unfortunately there is no way to get the line feed in the Format Axis task pane, which is a severely annoying shortcoming.

I was so annoyed that I wrote a little VBA procedure to do this, and I’ve added it to the Advanced Edition of Peltier Tech Charts for Excel 3.0, pardon the shameless plug. I can right click on the axis, and select Axis Number Format from the context menu.

A little dialog pops up, showing the current number format for the axis.

Then I enter the number format I want, inserting ‘<newline>’ where I want a line feed.

I click OK and I get the desired number format.

The axis may have been fine with just the short month name, that is, a number format of “mmm”, which you can apply using the Format Axis task pane.

Now set the axis scale limits on the primary axis, showing dates at the top of the chart. The axis had used 43200 and 43900, the serial numbers of 10-April-2018 and 10-March-2020, which were autoscaled by Excel. We need to use the serial numbers of 1-January-2019 and 1-January-2020, which are 43466 and 43831. If you use a major unit of 365, you will get tick labels at only the min and max of the axis, as shown below.

Finish with a Little Clean-Up

Turn off the primary major vertical gridlines and turn on the secondary major vertical gridlines. Easy: click the “+” icon next to the chart, click the right-pointing triangle next to Gridlines, and use the checkboxes.

There are two axes we don’t want to show. Format the top horizontal axis so it uses no line (in this case it already had no line) and no tick labels. You could format the right-hand vertical axis the same way; since we really don’t need it any more you could also delete it, but don’t delete the horizontal axes!

Finally, hide the dummy axis series. Double click on the series, and under the paint can, choose No Line and No Markers for its formatting.

There’s our Gantt chart with gridlines and axis labels at the first of each month. You don’t need to squint too hard to see that the month between 1 February and 1 March is shorter than the months between 1 January and 1 February and between 1 March and 1 April.

The post Gantt Chart with Nice Date Axis appeared first on Peltier Tech Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This Surplus-Deficit chart shows the history of surpluses and deficits (positive and negative values) over the past few years. The two colors help show when the deviation was positive and when it was negative. It takes a little bit of Excel magic to get that effect to work properly in an Excel area chart.

I showed how to make this kind of chart in an old tutorial, Area Chart – Invert if Negative. That approach required repeating the data in a new range, inserting a row after every row of data, and doing some interpolation to get the chart data right.

But I was looking over a “visual vocabulary” by my colleague Jon Schwabish of Policy Viz, a rough draft summary of many chart and visualization types and guidelines for building them in Excel. When it’s ready for public consumption, I’ll be sure to post a link.

Jon showed a surplus-deficit area chart, and on inspection, it wasn’t exactly right. He went to my earlier post and fixed it, but in the meantime, I thought of a way to do the calculations in a more compact way, supplementing rather than supplanting the original data.

Surplus-Deficit Column Charts

For this tutorial I will use a simple data set, with monthly surplus-deficit data. I’ve shaded this original data blue, so you can follow it in the long protocol. Note that the data is in an Excel Table, which has lots of magic of its own.

You can make a surplus-deficit chart with any number of chart types. Below is a simple column chart of this data showing surpluses and deficits in a simple data set.

Easy to make, easy to read: surpluses grow upwards from the horizontal axis, deficits downwards. Excel makes it easy to show positive and negative values in different colors in bar and column charts. Format the bars with the main (positive) color, then in the Format Fill part of the Format Series task pane, check the Invert If Negative box, and select the color for the negative values.

Surplus-Deficit Area Charts The Two-Color Area Chart

In general, I prefer columns for this kind of data rather than areas: it always seems to me that an area chart minimizes the points on either side of an axis crossing, and overemphasizes points in the middle of a positive or negative run.

But area charts have their place, so let’s make an area chart of the data:

Let’s format the colors like we did for the column chart. Main color, blue, that’s fine. But there’s no Invert If Negative setting. Let’s stop and scratch out chins for a while. What if we have separate Pos and Neg series in the chart? We can always format them with the desired different colors.

Here’s the data with Pos and Neg data columns added. I’ve used these formulas for Pos and Neg respectively:

Pos: =MAX(0,B2)
Neg: =MIN(0,B2)

Here’s the (unstacked) area chart showing Pos and Neg:

It’s not exactly right. The sloped edges of the different colored should meet on the horizontal axis, along the lines shown in the following chart:

Our data isn’t complete enough. We only have points at the vertices of the surplus-deficit data, indicated by the circular markers in this line chart:

That’s fine for a one-series area chart. But in our Pos and Neg data, here are the points we have:

Adjusting the Data

For the areas to line up correctly, we would need data points at the axis crossings between vertices, like this for the one-series line chart:

…or like this for the Pos Neg chart:

This is where we need to interpolate our area chart data. In my old tutorial, I inserted rows to make room for interpolation, like this. The blue is our original data, and the gold shaded rows are the interpolated data. The green shaded row at the top is a placeholder to make sure I have as many interpolated rows as original rows, for when I adjust the data in a minute.

In the Value column, the formula in the gold cell B4 compares the values in the blue cells above and below it (B3 and B5). If their product (B3*B5) is positive, it means that both have the same sign, so there is no axis crossing, and the cell will show #N/A, to suppress plotting of that point. If their product is negative, it means the blue cells have different signs; the line must cross in between, so the cell contains zero for the Y value at the crossing. Don’t worry about the formulas yet, they need to be redone when the table is adjusted.

In the Date column, if there is no axis crossing (product of blue Value cells B3*B5 is positive or gold Value cell contains #N/A), then the gold Date cell contains #N/A, to suppress plotting. If there is a crossing (product of blue Value cells is negative or gold Value cell contains zero), the gold Date cell A4 contains an interpolated value between the two blue Dates A3 and A5.

Anyway, I realized that I can add columns to the data table for these calculations. To illustrate, I’ll adjust my data in two steps. First I’ll move the gold and green cells to the right by four columns:

Now I’ll remove the blank cells between rows.

And we no longer need the gold and green to distinguish interpolated rows.

The table contains the original data in the blue columns, and these formulas in the other columns

Pos:     =MAX(0,B2)
Neg:     =MIN(0,B2)
Date 2:  =IF(ROW()=ROW(INDEX([Date],1)),NA(),IF(B1*B2>0,NA(),
             B1/(B1-B2)*(A2-A1)+A1))
Value 2: =IF(ROW()=ROW(INDEX([Date],1)),NA(),IF(B1*B2>0,NA(),0))
Pos 2:   =IF(ROW()=ROW(INDEX([Date],1)),NA(),IF(B1*B2>0,NA(),0))
Neg 2:   =IF(ROW()=ROW(INDEX([Date],1)),NA(),IF(B1*B2>0,NA(),0))

There are a few tricks. The first IF looks at the condition

ROW()=ROW(INDEX([Date],1))

which is true if the row is the first data row of the table. And if it’s true, the formula uses NA() to put #N/A into the cell.

The second IF looks at B1*B2. If values in both B1 and B2 are positive or both are negative, the result of multiplication is positive, so the formula returns #N/A. If they are mixed, we get a negative result, so the formula puts zero into the cell.

We’re never actually multiplying B1 and B2, which would produce a #VALUE! error, because cell B1 contains a text header label. In that row of the table, the first IF takes precedence, because it already has determined that the cell is in the first data row and the value should be #N/A.

Adjusting the Chart

Now we just need to get this additional data into the chart. The original Pos-Neg area chart looks like this.

The two series formulas look like this:

Pos:  =SERIES(Sheet1!$C$1,Sheet1!$A$2:$A$13,Sheet1!$C$2:$C$13,1)
Neg:  =SERIES(Sheet1!$D$1,Sheet1!$A$2:$A$13,Sheet1!$D$2:$D$13,2)

We need to change the X values from
Sheet1!$A$2:$A$13
to
(Sheet1!$A$2:$A$13,Sheet1!$E$2:$E$13)

And the Y values from
Sheet1!$C$2:$C$13
to
(Sheet1!$C$2:$C$13,Sheet1!$G$2:$G$13)
and
Sheet1!$D$2:$D$13
to
(Sheet1!$D$2:$D$13,Sheet1!$H$2:$H$13)

You can make the changes by typing in the series formula directly, or by using the Select Data dialog. After changing the series formulas, the areas in the resulting chart align across the axis as desired.

It’s a nice little trick, and it works for area charts and other charts that have category axes that can display a Date scale. These charts internally sort by date before plotting the data. So we can put the data anywhere in the list, even in a separate column that makes up a different part of the list.

The post Surplus-Deficit Area Chart appeared first on Peltier Tech Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The Problem

I’ve recently helped out a client with a project that updated some data and exported a related chart. The client asked if I had a way for him to trigger the VBA procedure (macro) on his PC from his Android phone. He’s not using Office 365 or OneDrive, so he needed a low-tech remote VBA solution.

I decided to base my solution on a synchronized folder in Google Drive or Dropbox, where the presence of a particular file triggers execution of the VBA procedure. It’s easy enough to create a dummy file in one of these shared folders from an Android phone, and even easier if a dummy file exists and all you need to do is rename it.

To run successfully, the remote computer must be running Excel, with the applicable workbook open; Excel’s Application.OnTime function must have started the monitoring process; and a reliable file-sharing service must be synchronizing the target folder automatically.

The Untriggered Project

An oversimplification of the client’s project is shown below. There is some data and a chart that displays the data. The data in C3:C8 is dynamic, in this example because the cells contain the formula =RANDBETWEEN(1,5). The
“Activate Monitoring” button is used to start the monitoring process; I’ll talk about it in a bit.

Why a pie chart? It’s simple enough for a demo; pie charts aren’t quite as evil as we’ve asserted for all these years; and they make easily-distinguishable file icons when viewing a Windows directory, as shown in a screenshot at the end of this article.

Here is the simple VBA routine that updates the data and exports a time-stamped image of the chart:

Sub RecalcAndExportChart()
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("Sheet1")
  Dim cht As Chart
  Set cht = ws.ChartObjects(1).Chart
  ws.Calculate
  Dim FileName As String
  FileName = FullPath & "Chart_" & Format(Now, "yyyy-mm-dd_hhmmss") & ".png"
  cht.Export FileName
  Debug.Print FileName
End Sub

FullPath calls a function to return the target directory for the exported image of the chart. I use the Debug.Print to tell me if a chart was exported.

The Trigger Mechanism The Shared Folder

I designed the first version of this remote VBA trigger mechanism to run using Google Drive, and then modified it to run in DropBox. To operate in Google Drive, I first created a top-level folder in my Drive account named _ Remote. I put this folder name into a VBA constant at the top of my code module:

Const REMOTE_DIRECTORY As String = "\Google Drive\_ Remote"

and I referenced this constant in the FullPath function:

Public Function FullPath() As String
  FullPath = Environ("UserProfile") & REMOTE_DIRECTORY & Application.PathSeparator
End Function

Environ is a handy function that returns useful information about the user’s environment. On my computer, Environ("UserProfile") returns the directory of my Windows account:

C:\Users\Jon Peltier

so FullPath returns:

C:\Users\Jon Peltier\Google Drive\_ Remote\

This is where the newly created Google Drive folder is located on my computer. It is set to synchronize automatically, so any changes made on my laptop are reflected (nearly) instantly on my phone, and vice versa.

Trigger Files

I defined three file names (as VBA constants) for various triggers:

Const REMOTE_TRIGGER_FILE_TRUE As String = "true.trigger"
Const REMOTE_TRIGGER_FILE_FALSE As String = "false.trigger"
Const REMOTE_TRIGGER_FILE_STOP As String = "stop.trigger"

Under normal operation, when the PC is waiting to be triggered but I don’t want to trigger it yet, a file named false.trigger will be present in the _ Remote folder. When I want the VBA update and export procedure to run, I change the name of the file to true.trigger. And if I want the PC to stop monitoring for a trigger, I will rename the file stop.trigger.

There is nothing special about these trigger files except their file names. I created false.trigger as a simple text file. More sophisticated versions of this mechanism may use parameters encoded in the contents of the file.

To determine whether a file exists, I use a very old Visual Basic routine I long ago cribbed from a former Visual Basic MVP named Karl Peterson. Pass in the full path and file name, and it returns True or False:

Public Function FileExists(ByVal FileSpec As String) As Boolean
  ' thanks Karl Peterson MS VB MVP
  ' Guard against bad FileSpec by ignoring errors retrieving its attributes
  On Error Resume Next
  Dim Attr As Long
  Attr = GetAttr(FileSpec)
  If Err.Number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    FileExists = Not ((Attr And vbDirectory) = vbDirectory)
  End If
End Function

I have three functions that tell me whether a trigger file is present:

Public Function IsTriggerSet() As Boolean
  IsTriggerSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_TRUE)
End Function

Public Function IsTriggerNotSet() As Boolean
  IsTriggerNotSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_FALSE)
End Function

Public Function StopTrigger() As Boolean
  StopTrigger = FileExists(FullPath & REMOTE_TRIGGER_FILE_STOP)
End Function
Monitoring and Updating

Finally, I need a VBA procedure to be running on my PC which will detect one of the trigger files and act accordingly:

Sub MonitorAndUpdate()
  If StopTrigger Then
    Debug.Print Now, "Stop"
    If Not IsTriggerNotSet Then
      FileCopy FullPath & REMOTE_TRIGGER_FILE_STOP, FullPath & REMOTE_TRIGGER_FILE_FALSE
    End If
    Kill FullPath & REMOTE_TRIGGER_FILE_STOP
    DeactivateMonitoring
  Else
    If IsTriggerSet Then
      If Not IsTriggerNotSet Then
        FileCopy FullPath & REMOTE_TRIGGER_FILE_TRUE, FullPath & REMOTE_TRIGGER_FILE_FALSE
      End If
      Kill FullPath & REMOTE_TRIGGER_FILE_TRUE
      Debug.Print Now, True
      RecalcAndExportChart
    Else
      Debug.Print Now, False
      If Not IsTriggerNotSet Then
        Dim sFullName As String
        sFullName = FullPath & REMOTE_TRIGGER_FILE_FALSE
        Debug.Print "Creating " & sFullName
        Dim iFile As Long
        iFile = FreeFile
        Open sFullName For Output As iFile
        Close #iFile
      End If
    End If
    ActivateMonitoring
  End If
End Sub

How it works in English is:

  • If a file named stop.trigger is present
    • Monitoring stops
    • stop.trigger is renamed to false.trigger
  • If a file named true.trigger is present
    • The file is recalculated and the chart is exported
    • true.trigger is renamed to false.trigger
    • Monitoring continues
  • If none of the trigger files are present
    • A new false.trigger file is created
    • Monitoring continues
  • If a file named false.trigger is present
    • Nothing happens, the program doesn’t even check
Activation and Deactivation of Monitoring

To activate or continue monitoring we need one more VBA constant for MONITOR_DURATION the time between checking for a trigger (here it is set for five seconds):

Const MONITOR_DURATION As String = "0:00:05"

and we need to use Application.OnTime to tell VBA when to run our monitoring procedure MonitorAndUpdate:

Sub ActivateMonitoring()
  If Not IsActive Then
    Debug.Print "Activating"
  End If
  IsActive = True
  NextTime = Now + TimeValue(MONITOR_DURATION)
  Application.OnTime NextTime, "MonitorAndUpdate"
End Sub

IsActive is a variable that keeps track of whether we are just starting our monitoring or whether we have been monitoring all along, and NextTime is another variable that keeps track of the next scheduled monitoring time:

Dim IsActive As Boolean
Dim NextTime As Date

Finally, to abort the monitoring, we need to tell Application.OnTime to cancel the last event we’ve scheduled. If we don’t keep track of NextTime, you can’t cancel the scheduled event.

Sub DeactivateMonitoring()
  On Error Resume Next
  Application.OnTime NextTime, "MonitorAndUpdate", , False
  On Error GoTo 0
  NextTime = 0
  IsActive = False
  Debug.Print "Deactivating"
End Sub

The “Activate Monitoring” button next to the chart in the very first screenshot runs ActivateMonitoring the very first time.

Running the Monitoring Routine

When I started, the _ Remote directory contained only the “Remote.xlsm” workbook that contains the data and chart and the code. I didn’t need to keep the workbook in the _ Remote directory, but it was convenient for this exercise. I hadn’t even bothered to create the empty false.trigger text file.

I started the routine by clicking the “Activate Monitoring” button. This is indicated by “Activating” in the Immediate Window log below.

The first time the program checked for a trigger file was at 10:22:08 AM; “False” indicates that it should not update and export. Since it found no false.trigger file, it created one.

The program checked again five seconds later, at 10:22:13, and took no action, but by 10:22:18, I had renamed the false.trigger file to true.trigger, and we see that a file named Chart_2019-03-07_102218.png was exported.

The monitoring proceeded for several more cycles, exporting three more charts while I tested renaming the trigger file on my phone, in Windows Explorer, and in the web interface for Google Drive.

Finally I renamed the false.trigger file to stop.trigger, and monitoring ceased.

Debug.Print log from Immediate Window
-------------------------------------
Activating
3/7/2019 10:22:08 AM        False
Creating C:\Users\Jon Peltier\Google Drive\_ Remote\false.trigger
3/7/2019 10:22:13 AM        False
3/7/2019 10:22:18 AM        True
C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102218.png
3/7/2019 10:22:23 AM        False
3/7/2019 10:22:28 AM        False
3/7/2019 10:22:33 AM        False
3/7/2019 10:22:38 AM        True
C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102238.png
3/7/2019 10:22:43 AM        False
3/7/2019 10:22:48 AM        False
3/7/2019 10:22:53 AM        False
3/7/2019 10:22:58 AM        False
3/7/2019 10:23:03 AM        True
C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102303.png
3/7/2019 10:23:08 AM        False
3/7/2019 10:23:13 AM        False
3/7/2019 10:23:18 AM        True
C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102318.png
3/7/2019 10:23:23 AM        False
3/7/2019 10:23:28 AM        False
3/7/2019 10:23:33 AM        False
3/7/2019 10:23:38 AM        Stop
Deactivating

When this all finished, I took a screenshot of the Google Drive\_ Remote folder. You can see the four exported chart images, the false.trigger file (which stop.trigger was renamed), and the workbook that runs it all.

Modifying to Work in Dropbox

I had to make only one simple change in the whole routine, to the REMOTE_DIRECTORY constant, for it to work just as well in Dropbox:

Const REMOTE_DIRECTORY As String = "\Dropbox\_ Remote"

Here’s a screenshot of the _ Remote folder on dropbox, taken from my phone.

The synchronization is handled by third-party file sharing applications, and all Excel has to do is wake up periodically to check for a filename, so this routine ought to work in other services besides Google Drive and Dropbox.

The Complete VBA Code

Here is the complete code for the remote VBA triggering project. You can also download my macro-containing workbook in this zip file: Remote.zip.

Option Explicit

Const REMOTE_DIRECTORY As String = "\Google Drive\_ Remote"
'Const REMOTE_DIRECTORY As String = "\Dropbox\_ Remote"
Const REMOTE_TRIGGER_FILE_TRUE As String = "true.trigger"
Const REMOTE_TRIGGER_FILE_FALSE As String = "false.trigger"
Const REMOTE_TRIGGER_FILE_STOP As String = "stop.trigger"
Const MONITOR_DURATION As String = "0:00:10"

Dim IsActive As Boolean
Dim NextTime As Date

Public Function FullPath() As String
  FullPath = Environ("UserProfile") & REMOTE_DIRECTORY & Application.PathSeparator
End Function

Public Function IsTriggerSet() As Boolean
  IsTriggerSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_TRUE)
End Function

Public Function IsTriggerNotSet() As Boolean
  IsTriggerNotSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_FALSE)
End Function

Public Function StopTrigger() As Boolean
  StopTrigger = FileExists(FullPath & REMOTE_TRIGGER_FILE_STOP)
End Function

Public Function FileExists(ByVal FileSpec As String) As Boolean
  ' thanks Karl Peterson MS VB MVP
  ' Guard against bad FileSpec by ignoring errors retrieving its attributes
  On Error Resume Next
  Dim Attr As Long
  Attr = GetAttr(FileSpec)
  If Err.Number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    FileExists = Not ((Attr And vbDirectory) = vbDirectory)
  End If
End Function

Sub ActivateMonitoring()
  If Not IsActive Then
    Debug.Print "Activating"
  End If
  IsActive = True
  NextTime = Now + TimeValue(MONITOR_DURATION)
  Application.OnTime NextTime, "MonitorAndUpdate"
End Sub

Sub DeactivateMonitoring()
  On Error Resume Next
  Application.OnTime NextTime, "MonitorAndUpdate", , False
  On Error GoTo 0
  NextTime = 0
  IsActive = False
  Debug.Print "Deactivating"
End Sub

Sub MonitorAndUpdate()
  If StopTrigger Then
    Debug.Print Now, "Stop"
    If Not IsTriggerNotSet Then
      FileCopy FullPath & REMOTE_TRIGGER_FILE_STOP, FullPath & REMOTE_TRIGGER_FILE_FALSE
    End If
    Kill FullPath & REMOTE_TRIGGER_FILE_STOP
    DeactivateMonitoring
  Else
    If IsTriggerSet Then
      If Not IsTriggerNotSet Then
        FileCopy FullPath & REMOTE_TRIGGER_FILE_TRUE, FullPath & REMOTE_TRIGGER_FILE_FALSE
      End If
      Kill FullPath & REMOTE_TRIGGER_FILE_TRUE
      Debug.Print Now, True
      RecalcAndExportChart
    Else
      Debug.Print Now, False
      If Not IsTriggerNotSet Then
        Dim iFile As Long
        iFile = FreeFile
        Dim sFullName As String
        sFullName = FullPath & REMOTE_TRIGGER_FILE_FALSE
        Debug.Print "Creating " & sFullName
        Open sFullName For Output As iFile
        Close #iFile
      End If
    End If
    ActivateMonitoring
  End If
End Sub

Sub RecalcAndExportChart()
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("Sheet1")
  Dim cht As Chart
  Set cht = ws.ChartObjects(1).Chart
  ws.Calculate
  Dim FileName As String
  FileName = FullPath & "Chart_" & Format(Now, "yyyy-mm-dd_hhmmss") & ".png"
  cht.Export FileName
  Debug.Print FileName
End Sub

This code can be pasted into a regular code module, and modified to suit your file sharing situation and the updating and exporting requirements of your project.

The post Remotely Trigger an Excel VBA Macro appeared first on Peltier Tech Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Stacked Charts with Labeled Totals

Several years ago on this blog, in Label Totals on Stacked Column Charts, I answered the common question, “How can I label the totals in a stacked column chart?”

You can take your data…

…create a stacked chart…

…and add labels…

…but the labels only appear on the actual points in the chart. There’s no way to make Excel apply labels with the totals.

The Manual Procedure

In that tutorial, I showed how to compute the totals in the worksheet…

…add the Totals data to the chart…

…convert the Totals data to a line chart series…

…add labels to the Totals series…

…reposition the labels above the line…

…hide the series (format with no line or markers)…

…and hide the legend entry for the Totals series.

This protocol is pretty easy, and only takes a minute, and it works for other chart types as well, like this stacked area chart:

But I guess I understand why this might be considered tedious. Which is why I’ve developed…

Automated Labeling of Totals for Stacked Charts

I started with a simple routine which:

  • Checks that it’s an appropriate chart type for labeling stacked totals
  • Counts the number of series, and points per series
  • Creates and populates an array for the totals (see note below)
  • Adds a series of the appropriate type (e.g., line for stacked columns)
  • Hides the series
  • Adds labels showing the values of the added series

Note: The routine does not add a column in the worksheet for the totals. Instead, it adds the data in VBA, creating an array of totals, then uses this array rather than a worksheet range to populate series it adds to the chart. This makes it a bit cleaner, and it’s easier than figuring out where in the sheet to put the totals. However, it’s also not dynamic, so if the values change, the labeled totals will no longer be correct.

Here’s the routine:

Sub AddTotalsToStackedColumnChart()
  If ActiveSheet Is Nothing Then GoTo ExitProc
  If ActiveChart Is Nothing Then GoTo ExitProc
  
  Dim cht As Chart
  Set cht = ActiveChart
  
  If cht.ChartType = xlColumnStacked Then
    Dim SeriesCount As Long
    SeriesCount = cht.SeriesCollection.Count
    Dim PointCount As Long
    PointCount = cht.SeriesCollection(1).Points.Count
    Dim Totals() As Double
    ReDim Totals(1 To PointCount) As Double
    Dim SeriesIndex As Long
    For SeriesIndex = 1 To SeriesCount
      Dim YVals As Variant
      YVals = cht.SeriesCollection(SeriesIndex).Values
      Dim PointIndex As Long
      For PointIndex = 1 To PointCount
        If IsNumeric(YVals(PointIndex)) Then
          Totals(PointIndex) = Totals(PointIndex) + YVals(PointIndex)
        End If
      Next
    Next
    
    Dim IsAxisBetweenCategories As Boolean
    IsAxisBetweenCategories = cht.Axes(xlCategory).AxisBetweenCategories
    
    Dim NewSeries As Series
    Set NewSeries = cht.SeriesCollection.NewSeries
    With NewSeries
      .ChartType = xlLine
      .Values = Totals
      .Format.Line.Visible = False
      .HasDataLabels = True
      With .DataLabels
        .ShowValue = True
        .ShowCategoryName = False
        .ShowSeriesName = False
        .ShowBubbleSize = False
        .ShowPercentage = False
        .ShowLegendKey = False
        .Position = xlLabelPositionAbove
      End With
    End With
    
    cht.Axes(xlCategory).AxisBetweenCategories = IsAxisBetweenCategories
    cht.Legend.LegendEntries(cht.Legend.LegendEntries.Count).Delete
  End If
  
ExitProc:
End Sub

With minor adjustments, this code will work for stacked column, area, and line charts. With major adjustments, it will also work for stacked bar charts.

Note: you should probably avoid stacked line charts. It is visually obvious when column or area charts are stacked, but not so for line charts. Often, people used stacked line charts when they didn’t mean to, when they didn’t even realize such an option was possible.

The Stacked Chart Totaler Add-In

I’ve modified the above bit of code so it will work on any of the stacked chart types, including stacked bars. I’ve added a custom ribbon button.

I’ve added a button on the chart’s context (right-click) menu.

I’ve added the ability to select which set of stacked data to label, if the chart contains more than one set.


You can download the add-in from this link: StackedChartTotalsLabeler.zip. The add-in is packaged in a zip file. Save the file on your computer, then unzip and install the file following instructions in Install an Excel Add-In.

The post Add Totals to Stacked Column Chart appeared first on Peltier Tech Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Peltier Tech Blog by Jon Peltier - 3M ago

A lot of Excel procedures are packaged in Add-Ins. An Add-In is basically a workbook containing some code, that has some other unique properties. An add-in usually has some user interface elements, such as buttons on the ribbon or on context (right-click) menus, so you can access its features.

The add-in workbook itself is invisible, though it has one and often more hidden worksheets, where information related to the add-in may be stored. The add-in can be installed, which means it opens every time Excel opens, so that its functionality is always available. An Excel add-in can also be opened like a regular file when you need it, without installing it, though it is still hidden.

Preparing to Install the Add-In

Very often, an Excel add-in is packaged in a zip file. Unzip the file, and store the add-in in the User Add-in Library, which is

C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns\

where USERNAME is your Windows login. You can get there quickly by pressing Win+R (Win = Windows key), typing %appdata% in the Run box, and clicking OK (which opens the Roaming directory), and drilling down to Microsoft and then AddIns.

You can actually store the add-in in almost any convenient folder, but when you use the Add-In Library, it’s easy to find the add-in from within Excel when you install it.

Windows protects your computer from malicious software that came from a different computer than yours, but it also protects your computer from useful software that comes from my computer, so you need to unblock the add-in. Right click on the add-in file in Windows Explorer, and choose Properties. At the bottom of the General tab of the Properties dialog, there may be a notice that the file may be blocked, and there is a checkbox to unblock the file. (Below is the dialog for Peltier Tech Charts for Excel 3.0B, my commercial Excel charting add-in.)

Check the Unblock box, and click OK.

Installing the Add-In

If you have the Developer tab showing on Excel’s ribbon, go there and click on Excel Add-Ins (or if it’s an older version of Excel that has no Excel Add-Ins button, click on Add-Ins) to open the Add-Ins dialog.

Otherwise, click on the File tab > Options > Add-Ins. Click the Go button near the bottom of the list to open the Add-Ins dialog.

Or you can use the old Excel 2003 shortcut, Alt+T+I, to open the Add-Ins dialog.

If you stored the add-in in the User Library, it will appear by name in the Add-Ins dialog. Otherwise you will have to click Browse, then navigate to find the add-in.

Check the box in front of this entry, then click OK, and the add-in is installed, available whenever you run Excel.

Uninstalling an Excel Add-In

Follow the steps to get to the Add-Ins dialog (Developer tab > Excel Add-Ins, File tab > Options > Add-Ins > Go, or Alt+T+I). When the Add-Ins dialog opens, locate the add-in in the list, uncheck the box in front of it, and click OK. The add-in should remain in the list, to make it easy to reinstall at some future time.

Using an Excel Add-In without Installing

If you don’t want an Excel add-in installed all the time, you can simply start it when you need it, using File > Open in Excel, double clicking on the add-in file in Windows Explorer, or dragging it from Windows Explorer and dropping it on Excel.

The post Install an Excel Add-In appeared first on Peltier Tech Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Peltier Tech Blog by Jon Peltier - 3M ago

In days of old, when you tried to send feedback to Microsoft on any of their products, your message went into the great bit bucket in the sky. And that’s even if you figured out where to send it.

But now, if you want to complain, Microsoft has built a mechanism right into their software. And let’s face it, you do want to complain, since most feedback that anyone ever bothers to give is negative, right?

There are a couple ways to access the feedback feature in Microsoft Office. These have evolved over the past couple years, and I think it’s easier than ever.

You can click on the File tab (#1 below), then choose Feedback in the list along the left (#2), and finally click the frownie icon next to “I Don’t Like Something” (#3). If you have an older version of Office, it still might say “Send a Frown”.

Or you can click the frownie icon at the right edge of the ribbon (#1 below), then when the Feedback task pane appears, click the icon next to
“I Don’t Like Something” in the task pane (#2).

In addition to sending a frown, you could also send a smile (“I like something”) or write up a suggestion on UserVoice, Microsoft’s suggestion box. I’d say frowns are 97 percent of my feedback, with frowns and suggestions making up about 2% and 1% respectively.

However you got this far, now you see the Feedback task pane which has a detailed form for you to fill out. Older versions of Office have a dialog instead of the task pane.

Give a detailed description of what has just annoyed you enough to click on a frownie icon. Details are good. I always check the screenshot box, and if the screenshot is not enough, I will add to the Excel window. Sometimes I paste an actual screenshot I’ve taken of an error message or dialog box right in the worksheet, or I add a textbox with a few lines of VBA code that is broken, or I may put before and after (or good and bad) charts side by side.

I always check the “attach my logs” box, because the logs may include some information that will help fix the problem. They don’t collect any personal information.

I always check the “you can contact me” box with my email address. Usually I also put my name and “Excel MVP” in the description field so they might be more inclined to contact me. And in fact, I have heard back from Microsoft because of frowns I’ve sent.

Usually if something doesn’t work, I can figure out a workaround, or loop a few extra times in VBA until something that’s supposed to happen finally happens. But it’s useful for Microsoft to know what things aren’t working as expected. I have started asking my clients to submit frowns when something goes wrong in their projects.

And if something changes that you like, it wouldn’t hurt you to send a smile their way.

The post Send a Frown appeared first on Peltier Tech Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
SeriesCollection(Item)

Working on a project last week, I encountered a somewhat amusing source of potential confusion. In VBA, you can reference a SeriesCollection item by name or by index.

In the screenshot above, I can reference the first series in the chart using

ActiveChart.SeriesCollection(1)

or using

ActiveChart.SeriesCollection("alpha")

Series 1 is Series “alpha”, pretty clear cut.

Well, in this project, several charts used numbers as series names.

So in the screenshot above, I could reference the first series in the chart with

ActiveChart.SeriesCollection(1)

or with

ActiveChart.SeriesCollection("1")

Series 1 is Series “1”. What’s the difference? Well, in one case, 1 is a number, and in the other, “1” is text. It didn’t seem to matter until the series were not plotted in numerical order.

In the screenshot above, Series 1 is Series “3”, and Series “1” is Series 3.

In Practice

The idea was to have an easy way to let a user select which series to perform some follow-up analysis on.

The first approach was to use a simple InputBox.

An input box accepts all input as text. So if you enter 1, the InputBox returns “1” to your code. If there is a series named “1”, then it’s the series being evaluated, but if there isn’t, hilarity ensues. You don’t get an error that series “1” isn’t found; instead, Excel picks a series using some internal logic all its own.

Okay, let’s use Application.InputBox, which allows you to accept one or more variable types.

You can set this InputBox to accept text or numbers. If you enter a number, you will select a series by index. If you try to select a series by name, by entering a number in quotes, the quotes become part of the series name sought by the code, and it fails (this time without picking any series).

The safest approach was to construct a dialog (aka a UserForm) with a two-column listbox, and listing each series by number and by name. This allows the user to specifically select a series, and if there is any potential confusion, it is obvious in the list.

The post Reference SeriesCollection Item by Name or Index appeared first on Peltier Tech Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

A couple months back I wrote Add One Trendline for Multiple Series which shows how to add a trendline to a chart, and have the trendline calculated for multiple series in the chart. In fact, that tutorial was based on my answer to a question on Quora, How can I have multiple scatter plots and one trendline for all of them combined in Excel? Some Quora questions can be kind of lame, but this was a good one, especially if I’m getting a second blog post out of it.

Feedback on that tutorial was positive, but it seems that people would like the process to be faster and simpler. Fair enough.

So I decided to write a small add-in that automates the process.

The Manual Process

If you recall, the original problem was that we had three series of data in the chart, and we can easily get a trendline for any or all individual series, but we want a trendline that covers all points in all three series. You can download a workbook with my dummy data and charts here: MultiScatterTrendlineData.xlsx.

Here is the original chart from the earlier tutorial:

And here is the chart with a trendline for each individual series:

We created a new series in the chart that included all points from the first three series (the yellow markers cover the blue, orange, and green ones):

This was the tedious step, adding all the data to a new series, and this is the part that my add-in will speed through.

Then we hid the new series by formatting it without markers, and added a trendline:

The VBA Code

The three original series in the chart have these formulas:

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$11,Sheet1!$C$3:$C$11,1)
=SERIES(Sheet1!$E$2,Sheet1!$D$3:$D$11,Sheet1!$E$3:$E$11,2)
=SERIES(Sheet1!$G$2,Sheet1!$F$3:$F$11,Sheet1!$G$3:$G$11,3)

Remember, a series formula has four arguments (a bubble chart series has a fifth argument, but we’ll ignore bubble charts here):

=SERIES(Series Name, X Values, Y Values, Plot Order)

We’ll give our added series a new name, “Combined”, and it will automatically be 4th in the plot order. In between we will combine the X values and Y values of the original three series. Our constructed series formula looks like:

=SERIES("Combined",
    (Sheet1!$B$3:$B$11,Sheet1!$D$3:$D$11,Sheet1!$F$3:$F$11),
    (Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11),
    4)

The multiple X value ranges are enclosed in parentheses, as are the multiple Y value ranges.

What our code will do is count the series in the chart, read each series formula in turn, split out its arguments, and concatenate the separate X and Y values into combined X and Y values. The code will then add the new series, apply the arguments of the series formula, hide the markers, and add a trendline.

Here is the simple procedure:

Sub ComputeMultipleTrendline()
  If Not ActiveChart Is Nothing Then
    With ActiveChart
      Dim ixSeries As Long
      For ixSeries = 1 To .SeriesCollection.Count
        Dim SeriesFormula As String
        SeriesFormula = ActiveChart.SeriesCollection(ixSeries).Formula
        SeriesFormula = Mid$(SeriesFormula, InStr(SeriesFormula, "(") + 1)
        SeriesFormula = Left$(SeriesFormula, Len(SeriesFormula) - 1)

        Dim SeriesArgs As Variant
        SeriesArgs = Split(SeriesFormula, ",")

        Dim XAddress As String, YAddress As String
        XAddress = XAddress & SeriesArgs(LBound(SeriesArgs) + 1) & ","
        YAddress = YAddress & SeriesArgs(LBound(SeriesArgs) + 2) & ","
      Next

      XAddress = "=(" & Left$(XAddress, Len(XAddress) - 1) & ")"
      YAddress = "=(" & Left$(YAddress, Len(YAddress) - 1) & ")"

      With ActiveChart.SeriesCollection.NewSeries
        .Name = "Combined"
        .XValues = XAddress
        .Values = YAddress
        .Format.Line.Visible = False
        .MarkerStyle = xlMarkerStyleNone
        With .Trendlines.Add.Format.Line
          .DashStyle = msoLineSolid
          .ForeColor.ObjectThemeColor = msoThemeColorText1
          .ForeColor.Brightness = 0
        End With
      End With
    End With
  End If
End Sub

If you want to run this code, open the VB Editor (easiest way: use the Alt+F11 shortcut), find your workbook in the Project Explorer, and insert a fresh module (Insert menu > Module, or simply Alt+N+M).

If the new module doesn’t say Option Explicit at the top, type it yourself, then go to the Tools menu > Options, and on the Editor tab of the dialog, check the box labeled Require Variable Declaration, and you may as well uncheck the box for Auto Syntax Check. I discuss why in a decade-old tutorial, VB Editor Settings.

Skip a line after Option Explicit in your brand new code module, then copy the code from above, and paste it into the module.

Before you run the code, select a chart. Then press Alt+F8 to open the Macros dialog. Select ComputeMultipleTrendline and click Run. In the blink of an eye, the new series is added, though it’s not visible, and the trendline appears. I used a solid black line, rather than the default dotted line Excel uses, because I think a solid line makes it easier to see.

It doesn’t matter if all series use the same or different X values; the code doesn’t even compare the X values of the different series, it just puts them all into the series formula.

The Multi Scatter Trendline Calculator

I used the code above as the basis for my add-in. I added a custom ribbon tab named Multi Trendline with a custom button labeled Multi Scatter Trendline to invoke the code. I also designed a dialog so that you can select which series in the chart to include in your analysis (and which to exclude).

Preparing to Install the Add-In

You can download the add-in from this link: MultiScatterTrendlineCalculator.xlam. The add-in is packaged in a zip file. Unzip the file, and store the add-in in the User Add-in Library, which is

C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns\

where USERNAME is your Windows login. You can get there quickly by pressing Win+R (Win = Windows key), typing %appdata% in the Run box, and clicking OK, which opens the Roaming directory, and drilling down to Microsoft and then AddIns.

You can actually store the add-in in almost any convenient folder, but when you use the Add-In Library, it’s easy to find the add-in from within Excel when you install it.

Windows protects your computer from malicious software that came from a different computer than yours, but it also protects your computer from useful software that came from my computer, so you need to unblock the add-in. Right click on the add-in file in Windows Explorer, and choose Properties. At the bottom of the General tab of the Properties dialog, there may be a notice that the file may be blocked, and there is a checkbox to unblock the file.

Check the box, and click OK.

Installing the Add-In

If you have the Developer tab showing on Excel’s ribbon, go there and click on Excel Add-Ins (or if it’s an older version of Excel that has no Excel Add-Ins button, click on Add-Ins) to open the Add-Ins dialog.

Otherwise, click on the File tab > Options > Add-Ins. Click the Go button near the bottom of the list to open the Add-Ins dialog.

Or you can use the old Excel 2003 shortcut, Alt+T+I to open the Add-Ins dialog.

If you stored the add-in in the User Library, it will appear in the Add-Ins dialog as MultiScatter Trendline Calculator. Otherwise you will have to click Browse, then navigate to find the add-in.

Check the box in front of this entry, then click OK, and the add-in is installed, available whenever you run Excel.

If you don’t want the add-in installed all the time, you can simply start it when you need it, using File > Open in Excel, double clicking in Windows Explorer, or dragging it from Windows Explorer and dropping it on Excel.

Using the Add-In

Select a chart, then click the button on the custom ribbon tab.

The dialog pops up. Select which series you want to include, and click OK.

The series is added invisibly, and a trendline is added using Excel’s default settings. You can format this just like any other trendline, to change the fitting model used, to show the trendline formula on the chart, or to change the trendline’s formatting.

About the Add-In

I have left the add-in unprotected in case you want to see how it all works. There is XML code that handles the custom ribbon tab and button. There is code in a second module to handle clicks from the ribbon button. A UserForm (i.e., a dialog) has been added to get input from the user. The main procedure is more detailed than shown in this article, to accommodate this dialog, and to compile data selectively.

I enjoy doing this kind of project. Even with the ribbon components and the dialog, it only takes a few hours. If you need something like this done, send me your requirements and I’ll generate a quote.

The post Trendline Calculator for Multiple Series appeared first on Peltier Tech Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

You can easily make a repeated Gantt chart in Excel to track such things as an ice hockey player’s time on and off the ice. This chart compares the ice times of two rival players:

Gantt Charts

I recently rewrote my tutorial on Gantt Charts in Microsoft Excel, and I’ve written several times about repeated Gantt charts: Gantt Chart for Repeated Tasks, Easier Gantt Chart for Repeated Tasks, and Gantt Chart with Repeated Tasks via Excel XY Chart.

Blog visitor Jesse read the Gantt chart tutorial, and asked,

I have a question … I want to graph two hockey players … from two opposing teams that faced each other in a game. The goal is to chart their start times and duration, and they go on the ice in shifts.
So, for example, player 1 during his first shift, enters the ice at :55 (which means 55 seconds have elapsed in the game) and stays on for 1:26 (a minute and 26 seconds) …

I thought, sure, I can just post a link to those ancient repeated Gantt articles (this tutorial is very closely related to Easier Gantt Chart for Repeated Tasks), or I could get some data and create a real-world example. So here we are.

Hockey Shift Data

Apparently you can get this kind of data from the web site of the National Hockey League. This means you can use Power Query to get the data from the NHL, then clean it up. Jesse supplied me with a file containing data arranged like this:

I might have tried to clean up the data more in Power Query, and maybe I’ll do just that and write another tutorial at a future time. But it’s not too hard to clean it up in Excel. First, all of the data is in text format, not numerical. Second, for the Start of Shift and End of Shift columns, there are two times in each cell: time elapsed in the period, and time remaining in the period, separated by a slash. Third, the times are for each period, and I wanted total time in the whole game, so period 1 goes from 0:00 to 20:00, period 2 from 20:00 to 40:00, and period 3 from 40:00 to 60:00. Finally, I want to make sure the times are in minutes and seconds, not the hours and minutes Excel would assume if it saw, for example, 12:34.

Here is the data with a couple columns added.

The formula in cell H2 is

=TIMEVALUE("0:"&LEFT(D2,FIND(" ",D2)-1))+((C2-1)*20/60/24)

LEFT extracts 0:50 from 0:50 / 19:10 in cell D2, "0:"& converts 0:50 to 0:0:50, so Excel will produce the output in minutes and seconds, and (C2-1)*20/60/24 adds 20 and 40 minutes for the second and third periods.

The formula in cell I2 is

=TIMEVALUE("0:"&F2)

which inserts the blank hour so our elapsed time is in minutes and seconds.

We’re almost ready to go.

Gantt Chart of Ice Time

To give myself a category variable, I inserted the shift numbers from column B into the blank column G, as numbers.

Then following the procedure for creating a Gantt chart, I selected the blue shaded range and inserted a stacked bar chart. Note: don’t do this yet; I’m explaining why it won’t quite work like this.

I formatted both axes, reversing the vertical axis and using friendlier parameters for the times on the horizontal axis.

Finally, I hid the “Start” series by formatting those bars to have no fill.

This is a Gantt chart of player time, but it shows each shift on its own level of the chart. It would be a better chart if we could plot a repeated Gantt chart, with all of a player’s shifts on one horizontal level.

Category Axis Tricks We Can Exploit

The vertical axis in a bar chart is a category axis, and it behaves like the horizontal category axis in a line or column chart. I’ll show the interesting behavior in line and column charts, because it’s easier, then extend it to bar charts. Then we’ll make out repeating Gantt chart, I promise.

If I just use simple labels for my X values, my chart will have a Text Axis. Excel uses the X values as separate labels in a line chart. Excel doesn’t sort the labels (alphabetically, Gamme should come after Eta, at least in English). Excel doesn’t care about gaps (Delta, Epsilon, and Zeta come between Gamma and Eta in the Greek alphabet and in a custom list I’ve built for my use in Excel: see Built In and Custom Lists in Excel). And Excel doesn’t care about duplicate labels (Eta Eta).

If Excel recognizes the X values as dates, the chart will have a Date Axis. Excel uses the X values as dates, and it treats these dates semi-numerically. The dates are internally sorted prior to plotting (not illustrated here, but see Plot Two Time Series With Different Dates). Points with larger spaces between dates are plotted further apart (one day between 1/2/2019 and 1/3/2019 vs. four days between 1/3/2019 and 1/7/2019). And duplicate times are plotted at the same date (two points for 1/7/2019). Excel ignores times on a Date Axis: all points from 12:01 am to 11:59 pm on a given date are plotted at the integral value of that date.

If your X values contain regular numbers, Excel just treats them as non-numeric labels in a line chart, just like the text examples two charts ago. Different gaps between numbers are plotted with the same spacing, and duplicates are treated as separate entries.

However, we can format the axis, and tell Excel to use a Date Axis. Now the chart shows that 3 and 7 are further apart than 2 and 3, and it shows multiple data points for X=7.

We’re going to make use of that behavior of plotting multiple data points for the same X value at the same axis position.

Here is the data for the ice time Gantt chart I made earlier. I’ve plotted it as a stacked column chart. My X values are the shift numbers from 1 to 10.

I’ve turned the chart into a floating bar chart by hiding the “Start” series (no fill).

Here I’ve replaced the shift numbers with player number, which is 1 if I’m plotting one player’s shifts. Now instead of 1 to 10, I see ten 1’s along the X axis.

When I format the axis as a Date Axis, all the floating bars line up horizontally.

That’s the trick, we’ll use player numbers and a Date Axis to align the floating bars vertically.

Repeated Gantt Chart of Ice Time Final Data Setup

Here is my updated data range, with shift data for two players. I have replaced shift number with player number in column G, my shift start times are in column H, shift durations for player 1 are in column I, and shift durations for player 2 are in column J. I could have kept both players’ durations in column I and the chart would be essentially the same, except the floating bars for both players would be in one series and thus formatted the same. For columns H:J, use a number format of [m]:ss; the square brackets tell Excel to keep incrementing minutes and ignore hours, so that 60 minutes (the end of the game) is displayed as 60:00 instead of 1:00:00.

Create the Chart

Start by selecting the blue shaded range, and inserting a stacked bar chart. I’ve already changed the colors in this chart, with “Start” in gray, “Niskanen” in red, the color of the Washington Capitals, and “Bogosian” in Blue, for the Buffalo Sabres.

Next, hide the “Start” series by changing its fill color to none, to make a floating bar chart.

Format the vertical axis so categories are plotted in reverse order, and so the (horizontal) axis crosses at the maximum category.

Change the vertical axis style from Automatic (Text Axis by default) to Date Axis.

Shrink the height of the chart (this one changed from 3″ to 1.5″), and format one of the series of bars to reduce the gap width to zero.

Hide the vertical axis labels (don’t delete the axis, in case you need to reformat it later).

Format the horizontal axis scale. Unfortunately it’s not as easy to do as it should be. It would be nice to be able to enter 60:00 for the axis maximum and 5:00 for the major unit, but Excel makes you enter their decimal equivalents. The 60-minute axis maximum should be set to 0.041666667 (60 minutes is 1/24 of a day), and the 5-minute major unit should be set to 0.003472222 (5 minutes is 5/60 of an hour or 5/60 of 1/24 of a day).

Move the legend to the right of the chart.

Click on the legend, then click on the “Start” legend entry, then press delete to remove it. Then align the center of the legend with the center of the plot area.

You can align the legend manually, or if you have Peltier Tech Charts for Excel, you can right click on the legend, and use the buttons on the context menu that pops up.

Finally, expand the plot area.

Repeated Gantt Chart

The result is our repeated Gantt chart for tracking player ice times and shift changes.

This approach can be used to chart numerous on/off situations. Hockey player ice times, ambulance crew waiting vs. on-call, factory or plant operating vs. standing by, even your child’s sleeping/waking cycles.

The post Repeated Gantt Chart to Track Players’ Ice Time appeared first on Peltier Tech Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In November I traveled to Ljubljana, Slovenia, and presented the “Advanced Excel Charting Masterclass”. I’ve done a fair amount of training before, from one-hour conference sessions to multiple-day custom sessions designed for corporate clients, on topics related to charting, VBA programming, and other Excel topics. But this was a full-day Excel charting workshop, which I guess covers my major obsession. 

The Ljubljana class was very well received, and I want to do it again. So I’ve decided to offer a series of these sessions in 2019. While it’s fun to travel to foreign exotic lands, I’ll start my own series right here at home, and travel across the country.

I will hold my first Charting Workshop on Wednesday, January 23, 2019, in Waltham, MA. This is 15 miles from downtown Boston, on the Route 128 (Route 95) corridor.

The MasterClass is a live, full-day, hands-on workshop. I provide training materials (workbooks and presentations) and a facility with wifi and refreshments, while you bring your laptop, your questions, and even some of your own examples and questions. Topics include:

  • Working with Data
  • Formatting and Conditional Formatting of Charts
  • Making Charts Dynamic
  • Enabling User Interaction With Charts
  • Dashboarding Techniques
  • Enhancing Charts and the Charting Process with VBA

The price of the MasterClass is $500. This includes the training and a buffet luncheon. However, a Super Saver price of $350 (a 30% discount) is available through Friday, January 4, and an Early Bird price of $425 (a 15% discount) is available until January 15. In addition, full-time students can email Peltier Tech for a coupon code for a 50% discount.

Click here to register for the Advanced Excel Charting MasterClass.

Additional dates and cities are being planned: February in Chicago, March in Seattle, April in Charlotte NC.

For more information, contact Jon Peltier.

The post Advanced Excel Charting Workshop appeared first on Peltier Tech Blog.

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview