My previous survey allowed only one version of Excel to be selected for work and for home. I knew that this was limiting, but the survey seemed easier to set up this way. But a few comments corrected my thinking, and a few responses helpfully had multiple versions entered as “Other”, so I’m going to set that one aside, and offer the following survey. As it turns out, it was not really any harder to set it up. Live and learn. You can select any and all spreadsheet versions that you use.

WordPress sometimes does funny stuff with embedded content like this survey. If the survey does not appear when the page is first loaded, wait a few seconds, then refresh the page.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

This quick and dirty survey has been closed. Thanks to all who participated. Results will be posted in the near future. In addition, a newer and better version of the survey will be/has been posted at New Survey: What Spreadsheet Programs Do You Use?

The original survey (shown below) allowed one response each for version of Excel used at work and home. This ignores those who use multiple versions, for example, developers who need too make sure their spreadsheets will work on any version of Excel.

My main intention in this survey was to get an idea of how many Excel users have upgraded to “New” versions (2007 and 2010), and how many are still using the “Classic” versions (1997 through 2003). The following chart shows my findings.

So about 86% of users have upgraded, while 14% are staying pat. The percentage was almost identical for usage at work and at home. I’m not sure if I’m surprised.

A handful of respondents entered multiple versions into the “Other” boxes in the survey; if these responses included both Classic and New, I didn’t count them. The follow-up survey will try to capture this usage more accurately.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

In most cases the technique in the cited article is the best approach, since it does not use VBA and it updates with the speed of Excel formulas. Sometimes, though, you need a VBA solution.

Prepare for VBA Formatting

In a worksheet named “ColorSheet”, I have set up a range that has row headers corresponding to the category labels I expect to encounter, and column headers corresponding to values I want to use as cut-offs for conditional formatting. The cell at the intersection of the category label row and value column is formatted in the way I want the chart’s data to be formatted. For example, a point with a category label of Alpha will be some shade of blue, while a point labeled Eta will be orange. The shade of the color used is lighter for smaller values and darker for larger values. I have included a label “other” in case an unexpected label is found, and a value of “above” in case the maximum in the table is exceeded. The light shades might be too light, and in any case, shades of color shouldn’t be expected to provide much resolution when encoding information.

Naturally, your labels and values will be different, and in fact you may need more complicated algorithms in the code.

I’ve named this region “ColorRange”. Note that this name appears in the Name Box above column A when the range is selected.

Here is my data and chart. The bars have the default formatting for series 1.

Apply Formatting by Running the VBA Code

Here is the chart after running the FormatPointByCategoryAndValue procedure.

After changing the values and rerunning the procedure, the chart’s bars keep their color, though the shades have become lighter or darker. The light green for Gamma is too light to distinguish from gray, and almost too light to see at all. I should probably go back and darken all of the lighter shades.

Now all of the data has changed, and in fact, more data is plotted in the chart. The code still works as expected. Note the Omega series, which is colored gray because that label was not included in the original color table.

The VBA Procedure

The code is not too complicated. After the declarations (Dim and Consts) the first thing that the code does is abort if the user has not selected a chart (a little extra effort is always well-spent if it that a run time error). There is a line of code which, if uncommented, will remind the user to select a chart by showing this dialog:

Then the range containing the colors is identified and its values put into an array to make the values easier to work with.

The first series of the active chart is defined as the series we are formatting. The category labels (XValues) and values (Values) are put into arrays, also for ease of processing.

The code then looks at each point’s value and label, to determine which cell has the desired formatting. The rows and columns are looped starting at 2, since the first of each contains an irrelevant label. The looping stops one count before the end. If a match is not found, the loop counter points to the last row or column.

Finally the point is given the same fill color as the matching cell in the color table. Note that there are two sets of syntax, one for Classic Excel (2003 and earlier) and the other for Neo Excel (2007 and later). I’ve commented out the 2003 syntax, because I now use 2010 for most of my outward-facing work.

Sub FormatPointByCategoryAndValue()
  Dim rColor As Range
  Dim vColor As Variant
  Dim srsColor As Series
  Dim iRow As Long
  Dim iCol As Long
  Dim iPoint As Long
  Dim vCategories As Variant
  Dim vValues As Variant

  Const sColorSheetName As String = "ColorSheet"
  Const sColorRangeName As String = "ColorRange"

  If ActiveChart Is Nothing Then
    ' uncomment following line to alert user
    'MsgBox "Select a chart and try again.", vbExclamation, _
        "No Active Chart"
    GoTo ExitHere
  End If

  Set rColor = Worksheets(sColorSheetName).Range(sColorRangeName)
  vColor = rColor.Value

  Set srsColor = ActiveChart.SeriesCollection(1)

  With srsColor
    vCategories = .XValues
    vValues = .Values

    ' cycle through points
    For iPoint = 1 To .Points.Count
      ' find category (row)
      For iRow = LBound(vColor, 1) + 1 To UBound(vColor, 1) - 1
        If vCategories(iPoint) = vColor(iRow, 1) Then Exit For
      Next

      ' find value (column)
      For iCol = LBound(vColor, 2) + 1 To UBound(vColor, 2) - 1
        If vValues(iPoint) <= vColor(1, iCol) Then Exit For
      Next

      ' apply color of identified cell to given point
      ' Excel 2007+ syntax
      .Points(iPoint).Format.Fill.ForeColor.RGB = _
          rColor.Cells(iRow, iCol).Interior.Color

      ' Excel 2003- syntax
      '.Points(iPoint).Interior.ColorIndex = _
          rColor.Cells(iRow, iCol).Interior.ColorIndex
    Next
  End With

ExitHere:

End Sub

To run the procedure, select the chart, then press Alt+F8, select FormatPointByCategoryAndValue, and click Run.

Here are some closely related articles from the archives:

To format by point value, see VBA Conditional Formatting of Charts by Value.
To format by category label, see VBA Conditional Formatting of Charts by Category Label.
To format by series name, see VBA Conditional Formatting of Charts by Series Name.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

The reader wondered how to create this shaded-band effect in a fan chart. This tutorial was developed using Excel 2010, but the procedure is applicable to all Excel versions.

The chart comes from page 34 of A Preliminary Analysis of the President’s Budget and an Update of CBO’s Budget and Economic Outlook, published in March 2009 by the Congressional Budget Office.

I’ve manually digitized the actual data up to 10/1/08 and the projections from 1/1/09 onward into this tall thin tower of numbers.

Here is the data plotted in a line chart.

I’ve set up the following table to capture the data for the fan. Midline contains the actual and projected values from above. I made up Min and Max so that the fan would grow wider for each successive quarter. I then created values in a and b to split the fan below the midline into three shades of gray, and in c and d to split the fan above the midline into the same three shades of gray. You can use more or fewer calculated columns to increase or decrease the number of shaded regions.

My numbers are arbitrarily calculated. If you have some probabilistic means of generating such values, you should use your calculations instead.

When I add these columns of data as additional lines in the chart, I get the following chart. I’ve changed the default kaleidoscope of line colors to shades of gray that lighten with increasing distance from the midline. The labels indicate which column in the table above contains data for each line. This chart is fine, but it doesn’t show the filled area effect.

To achieve filled regions, I will use a stacked area chart. In an adjacent range I’ve added the following table. The dates are the same, as are the minimum and midline predictions. The rest of the data are calculated differences between each line and the previous line, to capture the height of each colored band (distance between lines). For example, the Max value for 1/1/09 in the table below (33) is the difference between the Max value for 1/1/09 in the table above and the d value for 1/1/09 in the table above (11400 minus 11367).

I’ve created a stacked area chart using this table of data. Here are my colored bands.

The first thing I did was format the axes and delete the gridlines.

Then I converted the Midline series to a Line Chart. This is done by right-clicking on the series, choosing Change Chart Type (or simply Chart Type in older Excel versions), and selecting a line chart style.

Next I made the Min series invisible by formatting it to have no fill.

Then I rescaled the vertical axis, because I don’t want all that empty space at the bottom.

Finally I formatted the Midline to use dark gray for its line, and the area series to have lighter and lighter gray fills the further they are from the midline. I’ve also deleted the legend.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

“If all you have is a hammer, everything looks like a nail.”

Hard to believe Bernard Baruch died before computer graphics, because he hit the – um, nail – right on the head. Some days, it seems the world is comprised of people who fit into one of two categories …

1. “Find Excel not sufficiently sophisticated for real statistics, don’t you agree?” (Said in the same tone as Thurston Howell III – look it up, youngsters!)
2. Believe every problem can be solved by Excel.

What’s really interesting is that I fall into both of those categories, not just some of the time but almost every day. Let’s start with last week.  My darling daughter fought for the 135 lb world title on Saturday, and a few weeks ago, she started a free rice group. You can read a bit of the story at RondaMMA Free Rice.  In a nutshell (or should I say grain of rice), this is a wonderful site where you can answer questions and for every one correctly answered, 10 grains of rice are devoted to the world food program. Ronda sent free t-shirts, autographed pictures and other swag to fans who were part of her free rice group. Below is one of three Excel charts I did to track the progress at different points in the competition.

Her fans have donated over 20,000,000 grains of rice so far, and God love them for that, but it is safe to conjecture that the average mixed martial arts fan is not a doctoral student in statistics so my challenge was to come up with easy to follow graphics for tracking the results. Even though I am usually using SAS or SPSS all day, I selected Excel for this chart for a couple of reasons. First, the free rice site allows me to download the group data in a .csv file each day, making it easy for me to open in Excel. Second, it is blissfully easy to insert a picture in an Excel chart.

Should you have your own Excel free rice group you want to chart (or anything similar) here are the steps.

1. Create your data. In my case this meant having one column with the dates and a second column with the number donated as of that day.

A.  Each day, I downloaded the csv file that had three columns, a userid (A), a username (B) and number of grains donated (C).  I computed the number for that day by entering into a cell =SUM(C2:CN) where N was however many group members had donated as of that day. This gives me the data for one day.

B. In my master file, type the date, copy the sum from the day’s file and use Paste Special to paste the value only.

2. Format the cells. I went to Format, then Cells, then Number and formatted the cells to have zero decimal places and a comma to separate 1,000.

3. Make the chart: Select the Date and Rice Grains columns. Click Charts and select the first option, which is an area chart.

4. Double-click on your chart and the FORMAT DATA SERIES window shows up. It has an option I have never used because I am a “Serious Academic” (Thurston Howell III accent again) and I don’t work for USA Today and insert pictures in my chart. Until now, when I click on the PICTURE tab and choose insert picture.  I also click on the option to TILE PICTURE AS TEXTURE.

Because it looks awfully plain with white rice and a white grid, I double-click on it again and choose FILL this time, changing the background color to a pale orange.

I click on the legend and delete it, because it really is superfluous, add a title, and I have the chart to post to track the contest.

One person asked, “It takes 3,500 grains of rice to fill one bowl. If you raise 1,000,000 grains it’s only fed less than 300 people. How much difference does that make?”

Ronda’s answer was, “If you’re one of those people, it makes a lot of difference to you.”

As of now, the group has raised enough rice to give a meal to over 5,500 people who otherwise would have gone hungry.

You can see the fight at Ronda Rousey vs. Miesha Tate – caution: graphic violence.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

What if you want to get your axis scale parameters in the worksheet, but for some reason want to avoid using VBA? In this tutorial I show how to use boring old worksheet formulas to do just that.

Calculate Axis Scales in the Worksheet

The following table shows how to set up worksheet calculations of your axis limits. The minimum and maximum of your data are entered into the blue cells B4:B5 (labeled Min and Max), either as constants or as calculated values. Below these values are some calculations.

B6 and B7 (Min’ and Max’) are adjustments to min and max, adding 1% of the difference between the data max and min to the max, and subtracting this amount from the min. If the values are zero or closer to zero than 1% of the difference, then zero is used. This prevents any values except for zero from being located on the edge of the plot area of the chart. (The formulas shown in column C are used in the adjacent cells in column B.)

B8 and B9 determine what the axis tick spacing (called “major unit” by Excel) should be. If this major unit were written in exponential notation, Factor is “like” the pre-exponential coefficient and Power is “like” the power of ten.

The axis tick spacing (Xmajor) is computed in B3, where Factor is used in the lookup table in A11:B15, and multiplied by a function of Power to determine a nicer tick spacing. The tick spacings you calculate may not be exactly right, do to different chart and font sizes or other factors. You can experiment with the values in the lookup table to try to improve them.

Xmin in B1 is calculated as the largest multiple of Xmajor which is less than Min’, and Xmax in B2 is the smallest multiple of Xmajor which is greater than Max’.

Use the values in the red cells to adjust the chart axis manually.

Calculate Axis Scales Allowing User to Override Values

There are cases where you may want to allow the user to lock in one or more of the axis scale parameters. For example, if your data is for the value axis of a bar chart, you could override the calculated minimum to ensure that the axis scale starts at zero, regardless of the data minimum.

This greatly complicates everything, notably the formulas appearing in column C. The data min and max are in B7:B7 in the modified table below, while the user may enter preferred values into any of the cells B4:B6. Valid entries will be used in B1:B3, invalid entries or blanks will result in values being calculated.

The Min’ and Max’ modifications to the data Min and Max use the override min and max values if they are valid, otherwise use the same algorithm as in the previous case.

Power and Factor are calculated the same way as before.

The logic of the new calculations are as follows:

Xmajor: if there is a valid override entry for tick spacing, use it, otherwise, calculate it as before from Factor and Power.

Xmin: if there is a valid override entry for axis minimum, use it; otherwise, if there is a valid override entry for axis maximum, start counting down from the override maximum in increments of Xmajor, and use the largest value which is less than Min’; otherwise calculate as before.

Xmax: if there is a valid override entry for axis maximum, use it; otherwise, if there is a valid override entry for axis minimum, start counting up from the override minimum in increments of Xmajor, and use the smallest value which is greater than Max’; otherwise calculate as before.

This example with user overrides may be overkill. But I occasionally find it very useful. This post is my way of saving it to the cloud, so I can find it later in a search engine.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.