In a recent post at the PTS Blog titled Trendline Fitting Errors, Jon Peltier describes some of the problems that result from fitting data to trendlines created as a part of an Excel chart. Microsoft seems to acknowledge this problem via their Knowledge Base Article 211967, although the article is somewhat confusing.

Refering to the article and under the header "Symptoms", it states "The equation displayed for a trendline on an xy (scatter) chart is incorrect." Several sentences later, the article states "The trendline formula should only be used when your chart is an XY Scatter chart."

Finally, the article does state the reason why trendlines should only be used on XY Scatter charts: "Line, Column, and Bar charts plot only the Y axis as values. The X axis is plotted only as a linear series in these chart types, regardless of what the labels actually are. Therefore, the trendline will be inaccurate if displayed on these types of charts. This behavior is by design." I wonder why Microsoft continues to offer trendlines as a component of Line, Column, and Bar charts if they are incorrect?

Below are some useful resources for creating and interpretting trendlines:

• Choosing the Right Trendline for Your Data from Microsoft.
• Trendline Analysis with Excel from Process Trends.
• Trendline Charting Formulas from The Spreadsheet Page.

I recently found the following procedure posted as part of a comment at the Excel-Tips Blog. The procedure makes all of the cells in the worksheet grid square. It might come in handy some time.

Sub SquareCells()
    Dim i As Integer
    For i = 1 To 4
        With ActiveSheet
            .Columns.ColumnWidth = _
            .Columns("A").ColumnWidth / .Columns("A").Width * _
            .Rows(1).Height
        End With
    Next
End Sub

As an update to Two-Way Variance Analysis Post 1, the example below shows the generally accepted calculation as well as notes (A and B) describing the components of the calculation.

The right side of the example shows a simple alternative method to perform the calculation. In my opinion this method is easier to remember and understand because it keys in on the percent change only of each component of variance. In other words, you don't need to remember footnotes A and B to perform the calculation. This can be especially useful when performing analysis of more than two components as the calculations get much more complex.

Due to work I've been away for approximately one month. Upon my return, I discovered that John Walkenbach's Spreadsheet Page has been completely redesigned. All I can say is - what a nice format! In addition to the high quality of information provided, the new site is easy to navigate and very professional looking.

John has also started a blog called The Spreadsheet Page Blog, which can also be found at the new site.

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.