Recently I had a project that required creating a formula that extracted a workday value from the intersection of a vertical (month) and hortizonal (year) cell reference. In other words, the formula needed to get a value from the following table based on the input of a month and year. The table resides in the range B3:E16 below:

The purpose of the formula is to allow the output to be placed across a horizontal range. In this example, the horizonal range inputs (month and year) are in the range C19:H20. The output range in which the workdays need to be returned resides in C22:H22. After searching the internet for variations of Index and Match, I found that two match functions could work.

The formula in cell C22 is:

=INDEX($B$3:$E$15,MATCH(C20,$B$3:$B$15,0),MATCH(C19,$B$3:$E$3,0),1)

The formula is copied from C22 to H22.

Jon Peltier's PTS Blog is holding a contest to "highlight scientific and engineering applications of Microsoft Excel, by sharing models that users have built."

"The intent is to highlight scientific and engineering applications of Microsoft Excel, by sharing models that users have built."

"The contest winner will receive a copy of Excel MVP Bernard Liengme’s Guide to Microsoft Excel 2002 for Scientists and Engineers. This is the Excel 2002 edition of the book. The Excel 2007 edition will not be out until this fall, but any lessons learned in the Excel 2002 version will be applicable to Excel 2007, though some of the specific commands may have changed."

As a sidenote, when I entered college I wanted to be an engineer. Everything was going great until I hit the second Chemistry and specifically calculations for molarity and molality. For some reason I just couldn't get it. Eventually I went to the professor and asked to drop the class (at that time you had to have the professor sign the drop slip before you could get out.) After an hour of stern lecturing by the professor because, in his view, I wasn't studying enough, he finally signed the slip. Sadly, that was the end of my engineering career. Now I do financial analysis all day long but I still wonder after 20 years how things might have been different. Oh well.

Jon's contest is an excellent opportunity to share best practices. Please participate if you can.

I've been away for a month and during that time the skyrocketing price of gas has received a lot of press. Having worked in the oil industry summers while in school, I've always found it interesting how people react to the price of gas. Until recently my thought had been that the price of gas had not kept up with inflation and the we in the U.S. should be probably be paying four to five dollars per gallon. However, I've never thought to prove my theory out until now.

In an effort to track what the price of gas has done over time, I first visited the U.S. Department of Labor's Consumer Price Index page. The first area of information I wanted to find was the Consumer Price Index for All Urban Consumers. Using January 1984 as the base, I set the base CPI for my analysis at 101.90. I then copied each monthly value from January 1984 to April 2008 into an Excel spreadsheet. Next, I retreived the average price per gallon values for regular unleaded gasoline for the same time period. To get an inflation adjusted price for gasoline, I multiplied the base price of gas (January 1984) by the CPI for the month. The results appear in the chart below. The year is on the X axis and the price per gallon of regular unleaded is on the Y axis.

The chart shows that the price per gallon of regular unleaded remained essentially flat from 1984 to 2000. After a small bubble that occurred from 2000 to 2002, the price starts to increase at a pace of over 20% per year. The 20% figure is calculated using the Compound Annual Growth Rate formula. The formula is:

( Future Value / Present Value ) ^ ( 1 / Number of Periods ) - 1

Excel's XIRR function can also be used to perform this calculation. The Analysis Toolpak must be installed for this function to work.

In January of 2002, the price of regular unleaded averaged $1.14. Five years later the price averaged $3.05. Using the formula above to calculate the compounded annual growth rate . . .

( $3.05 / $1.14 ) ^ ( 1 / 5 ) - 1 = 21.75%

Using 1984 as the base, the chart shows that the average price of unleaded gas exceeds the inflation adjusted price somewhere around 2006 to 2007. Unfortunately all of the growth has taken place in the last five years and it really doesn't start to stand out until 2004 - 2005. Based on inflation, it looks like my $4 to $5 price target may have been too high. Or, maybe $4 to $5 is the reality and the CPI is not reflecting that growth yet because the overall economy hasn't fully priced in current gas costs.

On another note, in the summer of 2004 I parked my truck and started to ride my motorcycle everywhere. I like to tell people that I anticipated the rising price of gas but the truth is that I just wanted to start riding a motorcycle instead of driving a car. Co-workers like to tell me "oh, you must be saving a lot of money by riding a motorcycle to work every day instead of driving a car". Although the bike does get between 40 to 50 mpg depending on driving conditions, I'm not sure that I'm really saving any money. What they don't realize is that I purchased a new motorcycle as opposed to driving a truck that was paid for. I recently figured I would have to ride the motorcycle for about ten years if I thought I was going to break even based on gas prices. I may be saving gas but I'm not saving any money, at this point anyway.

Recently I've had to create a series of Bar charts that incorporate points that allow additional data labels. I've created these points by building in an XY scatter chart into the Bar chart. The additional data labels are tied to the XY points using a tool such as John Walkenbach's J-Walk Chart Tools or Rob Bovey's XY Chart Labeler.

An example of one of these charts, less data labels, looks like this:

The person who requested the chart wanted each XY point to be visible as well as vertically centered within each bar. To do so, I started by setting the gap width to 100%. Next, a calculation creates the Y values of the XY points as illustrated below.

The calculation starts with the source data for the bar chart which is contained in the range B4:E8. There are four categories and three series within each category. I've entered those numbers into the yellow highlighted cells D19 and D20. The calculation of the lowest Y point on the XY chart vertical axis is contained in cell D23. The calculation is:

=D20/(D20+1)

Once this calculation is done, the results can be applied to all of the Y values of the XY chart. The XY chart source is contained in the range C11:H15. The results of the calculation above are linked to cell D12. Cell D13 adds the series per category value to the results in C12. The formula in D13 is:

=D12+$D$20

Cell F12 adds the series per category value to the results in C12. The formula in F12 is:

=D12+$D$23

Finally, the maximum value of the secondary axis is simply the number of categories multiplied by the series per category. The formula in cell D24 is:

=D19*D20

The VBA procedure below attempts to quickly build this chart based on the template above. Please note that it references the color palette that I currently use - your colors may be different. At this point the output produces the following:

Given time the procedure could be written to complete the formatting. It could also be modified to automatically accept a reasonable number of changing categories and series, use a custom color palette, and allow more efficient range references.

Option Explicit

Sub BuildChart()

    Application.ScreenUpdating = False

    Dim XVals As Range
    Dim Srs1 As String
    Dim Srs2 As String
    Dim Srs3 As String

    Set XVals = ActiveSheet.Range("B5:B8")
    Srs1 = ActiveSheet.Range("C4").Value
    Srs2 = ActiveSheet.Range("D4").Value
    Srs3 = ActiveSheet.Range("E4").Value

    Charts.Add
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs1
        .ChartType = xlBarClustered
        .XValues = XVals
        .Values = Sheets("Sheet1").Range("C5:C8")
        .Interior.ColorIndex = 22
        .Border.LineStyle = xlNone
    End With

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs2
        .ChartType = xlBarClustered
        .XValues = XVals
        .Values = Sheets("Sheet1").Range("D5:D8")
        .Interior.ColorIndex = 35
        .Border.LineStyle = xlNone
    End With

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs3
        .ChartType = xlBarClustered
        .XValues = XVals
        .Values = Sheets("Sheet1").Range("E5:E8")
        .Interior.ColorIndex = 24
        .Border.LineStyle = xlNone
    End With

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs1 & "_XY"
        .ChartType = xlXYScatter
        .XValues = Sheets("Sheet1").Range("C12:C15")
        .Values = Sheets("Sheet1").Range("D12:D15")
        .MarkerSize = 3
        .MarkerStyle = xlDiamond
        .MarkerBackgroundColorIndex = 3
        .MarkerForegroundColorIndex = 3
    End With

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs2 & "_XY"
        .ChartType = xlXYScatter
        .XValues = Sheets("Sheet1").Range("E12:E15")
        .Values = Sheets("Sheet1").Range("F12:F15")
        .MarkerSize = 3
        .MarkerStyle = xlDiamond
        .MarkerBackgroundColorIndex = 10
        .MarkerForegroundColorIndex = 10
    End With

    With ActiveChart.SeriesCollection.NewSeries
        .Name = Srs3 & "_XY"
        .ChartType = xlXYScatter
        .XValues = Sheets("Sheet1").Range("G12:G15")
        .Values = Sheets("Sheet1").Range("H12:H15")
        .MarkerSize = 3
        .MarkerStyle = xlDiamond
        .MarkerBackgroundColorIndex = 5
        .MarkerForegroundColorIndex = 5
    End With

    ActiveChart.ChartGroups(1).GapWidth = 100

    ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale = ActiveSheet.Range("D25").Value
    ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale = 0
    ActiveChart.Axes(xlCategory, xlSecondary).MajorUnit = 1

    ActiveChart.Axes(xlValue).MaximumScale = ActiveSheet.Range("D25").Value
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MajorUnit = 1

    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = ActiveSheet.Range("D24").Value
    ActiveChart.Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "#,##0"

    ActiveChart.Parent.Height = 189.75
    ActiveChart.Parent.Width = 319.5

End Sub

Below are interesting items from around the web:

It's that time to update the annual and five-year financial forecasting model that I use at work. The model relies heavily on linear regression techniques to perform the forecasts. For reference, Kelly O'Day has an excellent writeup on his Process Trends site titled Trend Analysis with Excel.

In A Belated Review of Excel 2007, Jon Peltier describes his experiences with the latest release. Although I have Excel 2003 and 2007, I tend to do almost all of my work in 2003 more than likely because that's also what we use at work. However, that will probably change soon as the healthcare system that I work for recently purchased licenses for Office 2007 to support the local staff.

In September of 2005, David Gainer wrote in the The Excel Team Blog that Excel 2007 "made huge improvements to our charting engine for great-looking charts in Excel and across Office" and that Microsoft had "reworked the chart UI to make creating and working with charts much simpler and easier". Fast-forward to 2008 . . . I wonder what the general opinion of end-users and developers is now regarding functionality and features?

Microsoft's Office Fluent User Interface Developer Portal contains a series of resources for customizing the Office 2007 ribbon.

This site uses a combination of CSS and HTML tables. One of my favorite references for formatting tables in CSS is Robert Denton's CSS Tables Tutorial. Although the last update to this site was in 2002, adapting the CSS examples to XHTML standards was very easy with the help of the WC3 Validator. TechRepublic also has a nice writeup titled Why CSS Styling is for Tables Too.

Mandarin Design is one of my favorite resources for general website and blog design. The site features a variety of formatting techniques for setting up multiple columns, floating images, etc. Unfortunately the author passed away sometime back so the site is no longer actively updated. However, it is still online which is great for novice users like myself.

When I most need it, I always seem to forget how to compute the difference between two pivot table columns. So, it's about time to put it in writing. Assume the following data:

Create a simple pivot table with the years as columns and the areas as rows.

Next, eliminate the row totals. To do so, right-click any cell within the pivot table and go to Table Options. Deselect "Grand total for rows" from the format options area of the Table Options dialog box.

At this point it's time to create a calculated item that subtracts the 2008 data from the 2007 data. Right-click on the year (cell B2) and select Show Pivot Table Toolbar. In the Pivot Table toolbar, choose Formulas -> Calculated Item. The following dialog box will appear:

Add the formula for the calculated item as shown below:

To make all of the accountants happy, we need to make the most recent year appear as the first column of the table. To do so, right-click within the column titles (2008, 2007, or Difference) -> Order. You can then move the any of the columns to the right or left to achieve the desired look.

The end-result looks like this: