In our accounting shop, we build our budgets in Excel but submit them in another software called Hyperion. Hyperion requires us to submit in whole numbers. To do so, we need to round every formula in the final Hyperion load so that the budget foots and crossfoots.

Before submission, I'll use the following macro to wrap the ROUND function around every formula:

Sub Add_Rounding()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Round")) = 0 Then
            Rng.Formula = "=round(" & cellFormula & ",0)"
        End If
    
    Next Rng

End Sub

The macro works by:

• Looping through each cell in the range.
• Uses the Special Cells method to identify only those cells with formulas.
• Sets up a string called "cellFormula" which is a picture of the formula.
• Uses the InStr function to look at the cellFormula string and identify if it already has the ROUND function included. If so, the macro goes on to the next cell. If not, it adds the ROUND function to the formula.

You can use the same type of logic to apply the ISERROR function to a range of formulas:

Sub Add_ErrorTrap()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Error")) = 0 Then
            Rng.Formula = "=if(Iserror(" & cellFormula & "),0," & cellFormula & ")"
        End If
    
    Next Rng

End Sub

Until several weeks ago I had not used an RSS reader to access the sites that I like to visit. I'm not sure why other than it seems like the authors of those sites have only in the last year or two added RSS feeds or started their own blogs. As a result, until recently I had not even taken the time to view my own site in an RSS aggregator. I just assumed that the content had to look OK because I used the standard RSS template that ExpressionEngine provides.

Recently I viewed the feed from this site and each post looked like one large block of text as opposed to showing the proper line breaks between paragraphs, code examples, etc. Upon investigation I viewed the feed using Bloglines and Google Reader. Both aggregators confirmed that the feed was coming across in this manner.

ExpressionEngine provides very good support and I was able to quickly find a solution in their Knowledge Base. I changed my template, logged back into Bloglines and Google Reader, and . . . everything looked the same.

To make a long and frustrating story short, I found out the following about RSS and aggregators in general:

• As I made changes to my RSS template I thought those changes would be reflected in the aggregators immediately. However, RSS readers update only periodically and when they do they cache their results. Changes made to an RSS template may not show up in an aggregator for several hours. Furthermore, they may never show up depending on what aggregator you are using.
• Some aggregators, as the ExpressionEngine Knowledge Base article alluded to, do not show HTML as expected. Bloglines appears to be one of those aggregators. For that reason and although I don't really care for the interface, I've moved to Google Reader as my aggregator of choice.

In summary, I find it hard to believe that given the popularity of Bloglines it does not show whitespace as expected. And not understanding how aggregators cache their results resulted in several hours over several days of research and frustration on my part.

Depending on the version of Excel that you are using, there are two free addins available that allow you to tie the series labels in a chart to worksheet cells. Those addins are:

• The XY Chart Labeler available from Rob Bovey's Applications Professionals site.
• The J-Walk Chart Tools available from John Walkenbach's Spreadsheet Page.

A project that I worked on recently required that I tie the series data labels to one of many range references based on an input by the user. To do so, it was required that I build the same type of functionality that the addins provide directly into my project.

In a post to the Microsoft Excel Programming Newsgroup dating back to July of 1999, John Green describes how to create labels in a chart from the text in a range using VBA. The code originates from the book titled "Excel 2000 VBA Programmer's Reference" written by John Green, Stephen Bullen, and Felipe Martins.

As taken from the post, the code to tie the series labels to a worksheet range looks like this:

  Sub AddDataLabels() 
  Dim SalesSeries As Series 
  Dim pts As Points 
  Dim pt As Point 
  Dim rng As Range 
  Dim i As Integer 

  Set rng = Range("B4:G4") 
  Set SalesSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1) 
  SalesSeries.HasDataLabels = True 
  Set pts = SalesSeries.Points 
  For Each pt In pts 
    i = i + 1 
    pt.DataLabel.Text = "=" & rng.Cells(i).Address _ 
             (RowAbsolute:=True, _ 
              ColumnAbsolute:=True, _ 
              ReferenceStyle:=xlR1C1, _ 
              External:=True) 
   Next pt 
End Sub

After finding the example above, I wanted to see if the code could be streamlined in any way. I first looked up the "Address" property as applied to the range object in Excel's VBA help and found out that the default values of the row and column references are "true". As a result, the Address property could be shortened and rewritten as:

rng.Cells(i).Address(, , xlR1C1, True) 

I remembered that in a prior post titled Declaring Line Chart Variables, Jon Peltier suggested a best-practice for declaring variables. Following those suggestions, I renamed the series variable "SalesSeries" to "Srs". I also eliminated the point variable and used a reference to Srs.Points.Count in its place. With those changes the loop could be rewritten as follows:

For i = 1 To Srs.Points.Count
    Srs.Points(i).DataLabel.Text = "=" & Rng.Cells(i).Address(, , xlR1C1, True)
Next i

Finally, by making the changes above I was able to simplify the code to look like this:

Sub AddDataLabels()
    Dim Srs As Series
    Dim Rng As Range
    Dim i As Integer
    Set Rng = Range("B4:G4")
    Set Srs = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    Srs.HasDataLabels = True
    For i = 1 To Srs.Points.Count
        Srs.Points(i).DataLabel.Text = "=" & Rng.Cells(i).Address(, , xlR1C1, True)
    Next i
End Sub

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.