Excel: Graphing Ranges

Took me a while to try figure out how to do this from getting tips from several different sources (I have included the sources at the bottom if you are interested in more detail).  What I was trying to do was produce a graph that would show salary ranges, min to max, for different job positions.  As an example imagine you have the following data in a table.

Job Min Avg Max
Butcher $ 30,000 $ 40,000 $ 50,000
Baker $ 35,000 $ 45,000 $ 60,000
Candlestick-maker $ 50,000 $ 55,000 $ 60,000

Now you want to create a graph that looks like this:

This is the final chart.
This is the final chart.

First thing you need to do is add a new column that is the result of =MAX-MIN for each.  You can do this beside the existing table. Now select the entire table including the new column you just created.  Create a chart, using the type stacked column.   Make sure the the X-axis has each job position listed, if not the you have to tell excel to switch the data around, the data series should be min, average, max and max-min.  You should now have a column chart that looks something like this.

This is what you should get with the first chart.
This is what you should get with the first chart.

Now here are the steps to get things to look right:

  1. Hide the min series.  Select series by click on one of the min blocks.  Format series.  Set fill to none and line to none.  This should make the series hidden but it is still there supporting the other blocks of the column.
  2. Now convert the Average data series into a line.  Select the series.  Right click on it and select change series chat type (different version of excel have this labeled differently).  Select a chart type of XY scatter – markers only.  You should get a dot or some marker that will replace the bar.  Depending on the version of excel this will either be centered with the columns or create a secondary x-axis.  If the marker is off center then click on one of the marker and then right click and select Format Data Series.  Under that menu you should be able to force it to use the primary access.  That is it for now.
  3. Now do the same operation for the Max data series.
  4. You should now have something that shows ranges with a marker for the average and max.
  5. Add a marker for the min.  Right click on the chart and select data source.  In the pop up window add another series.  Call it what you want “Min”.  Then for source data highlight the min data from the table.  Depending on your version of excel it will either add this as an X&Y scatter type or as part of the column.  If you get a column then convert it using the method above.
  6. You can now change the look of chart by adding data labels and changing the marker types to fit your needs.

I hope this will help people out who are trying to do the same sort of thing.

Broadband Speed Tests

If you ware wondering if you are getting the level of service promised by your ISP then the SpeedTest.net tool is a great way to check.  They have a slick interface for selecting the test router closest to your location.  They both maintain a history of your past tests so you can see if tweaks or upgrades have had the desired outcome.  They also show you results when compared to other users of the same ISP and other ISPs in your province, across your country and world.  Also be sure to check out the collection of data about world results.  Canada is not looking so good at 36th position for download speed and 40th for upload. When compared to Korea’s 20+ Mbits/sec download speed we seem to have a lot of catching up to do in North America.

UPDATED: 2018/12/17

Somewhat entertaining to see how the rates I mentioned nearly 10 years ago are now considered low-end speeds. Canada has made a big jump in the rankings, moving up to 16th spot. Singapore takes the top spot with a speed 185.25 Mbps. Can’t imagine what speeds we will see in another 10 years.

I was contacted by another broadband test provider about their speed test. It has a less commercial feel to it, a simple interface and gave similar results to what I was seeing on SpeedTest.net. The nice thing about this test is that every test done raises 1 cent to two foundations doing great work in the tech space, Electronic Frontier Foundation and Computers 4 Africa. Nice way to do something positive while checking your broadband connection, go check out compritech speed test.

There has also been a trend in routers now including daily speed tests. Our Google Wifi Mesh Router monitors both the LAN mesh side of the network along with a daily WAN speed test. All displayed in a nice chart on the Google Wifi App.

Google Flu Trends

I have written about Google Trends in the past and what a wealth of information that one could get by tracking what people are googling.  Well it looks like the gang over at Google.org have compiled the trend data to look for flu outbreaks.  Google Flu Trends now has coverage of most of North America and Europe.   It will be interesting to see how the tool performs when it comes to the H1N1 virus.

Quicken Issue With TSX Index

Just noticed that my version of Quicken had stopped downloading quotes for both the Toronto Stock Exchange and Toronto Venture Exchange, several months ago. I actually thought my investments were starting to look pretty good in comparison. I went digging around the Intuit support site but didn’t get much help there. I did manage to find a solution that seems to have the updates working again.

For the TSX follow these steps:

  1. Open the portfolio screen and scroll to your list of index.
  2. Click on the S&P/TSX Composite Index.
  3. This will open the security detail view window for OSPTX. Now click on Edit Security Details button.
  4. In the new window, click on the drop down arrow for Exchange and select U.S. Stock.  (Don’t know why, but hey it seems to work.)
  5. Click on OK to accept the change. In the new window leave the selection to merge quotes from OSPTX into OSPTX.
  6. Click Ok to accept the changes.

Do the same thing for the Toronto Venture Exchange but use the symbol OSPVX.

If anyone has another solution or an explanation of what happened to break this then please leave a comment.

Update: 01/29/2010

Quicken.ca now has the above solution posted for this problem now. They indicate that this was due to switching providers for security updates. Makes me think this was a choice made by headquarters in the U.S. without consideration for their Canadian users.