A recent question to the Microsoft Excel Charting Newsgroup asks "How can I create a floating column chart that shows a minimum, maximum, and average value"? Although Excel 2003 does offer a floating column chart as a custom-type option, it is not as "clean" as desired because the bars are automatically set to a 3D option. The best alternative is to use a combination Stacked Bar / XY Scatter chart. The bars will show the minimum to maximum range and the XY series will show minimum, maximum, and average values for each item. An example of the finished product is below:

To build the chart, assume the following set of data:

Some additional data and calculations need to be added to prepare the chart source. In the screenshot below, the range A9:C13 will serve as the source of the bar chart. The original range A2:D6 will serve as the X axis points of the XY chart and the Y points are shown in the range F2:F6.

Start by creating the floating bar chart. To do so, refer to a process posted at the Peltier Technical Services site that illustrates how to make a series invisible so that a second series floats in place (titled: Floating Column Charts) as a guide.

In the example, note that the "Dummy1" series is exactly the same as the "Min" values. The "Dummy2" series is calculated by subtracting the "Min" value from the "Max"value. Select the range A9:C13 and create a stacked bar chart. The chart should look like the one below:

Double-click the "Dummy1" series. In the Format Data Series dialog box - Patterns tab, set the Border to None and the Area to None. The chart should now look like this:

Now that the floating bar chart is created, it's time to incorporate an XY Scatter chart which will show the minimum, maximum, and average values. Select the chart and in the Source Data - Series dialog box, add a third series called "Minimum" based on the range F3:F6. After doing so, the Source Data - Series dialog box should look like this:

Note the new third series has appeared as a set of yellow bars. These bars need to be converted to XY Scatter points. Click on the new third series (the yellow bars) and, in the Standard toolbar, select Chart -> Chart Type -> XY (Scatter) -> type = Scatter. After doing so, the chart looks like this:

In the Standard Toolbar, select Chart -> Source Data -> "Minimum" Series. Currently there are no X values selected for the XY points (see the screenshot below):

Select the Minimum values as the X Value range (see the screenshot below):

And the chart should look like this:

With the incorporation of the XY Scatter points, two new secondary axes have been added to the top and right sides of the chart. To properly align the new XY points with the bars, the maximum values for these new axes need to reflect the same maximum values of the primary category and value axes. Double-click on the Secondary Value (X) Axis (the axis values at the top of the chart) and in the Format Axis dialog box -> Scale tab - set the maximum to the same value as the maximum of the Value axis (in this example the maximum is 60).

At this point the minimum values are now aligned with the minimum values of the bars. The same process of adding XY scatter points has to be completed to set up the average and maximum values. After adding the average and maximum values as XY points, the chart looks like this:

All that's left is to do now is some clean-up. Double-click on the Secondary Value (X) Axis (the axis values at the top of the chart) and in the Format Axis dialog box -> Patterns tab set the Tick Mark Labels and Major Tick Mark Type options to None. Likewise, double-click on the Secondary Value (Y) Axis (the axis values on the right side of the chart) and in the Format Axis dialog box -> Patterns tab set the Tick Mark Labels and Major Tick Mark Type options to None. The chart now looks like this:

To eliminate the legend references to the "Dummy1" and "Dummy2" series, click first on the legend and second on the "Dummy1" legend entry. Handles should appear around the "Dummy1" legend entry if it has been selected correctly. An example of how the chart looks with the "Dummy1" legend entry selected is below:

To get rid of the "Dummy1" legend entry, hit the delete key. Follow the same process for the "Dummy2" reference.

Select the plot area. In the Standard Toolbar select Chart -> Chart Options -> Gridlines and deselect all of the gridline options. Select the Minimum, Average, and Maximum series and in the Format Data Series dialog box -> Data Labels tab select the X value.

Continue to format until finished.

Several days ago I received a very large workbook from a co-worker containing thousands of formulas. Some of the formulas were wrapped in the ROUND function while others did not. All of the formulas were originally returning data rounded to the nearest million. However, in order for the workbook to be loaded to our accounting system all of the data needed to be stated in thousands and then rounded to whole numbers.

To do so, I wrote a macro to first remove the ROUND function from any formula that contained it. After some trial and error, the macro to remove the ROUND formula finally looked like this:

Sub RemoveRound()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String
        
    On Error Resume Next
    
    Set cellRange = Range("A1:C5").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Round")) > 0 Then
        
        cellFormula = Right(cellFormula, (Len(cellFormula)) - 6)

        cellFormula = Left(cellFormula, Len(cellFormula) - 3)
        
        Rng.Formula = "=" & cellFormula
        
        End If
    
    Next Rng

End Sub

The macro works by looping through each cell in the range with a formula. As it loops through each cell, the formula is stored in a the string variable called "cellFormula". The line cellFormula = Right(cellFormula, (Len(cellFormula)) - 6) trims the "ROUND(" out of the left side of the formula and saves the result to the same cellFormula variable. The reference to cellFormula = Left(cellFormula, Len(cellFormula) - 3) trims the ",0)" out of the right side of the formula and then saves the result to the cellFormula variable. Finally, the reference to Rng.Formula = "=" & cellFormula saves the formula without the ROUND function back to the range.

Since all formulas were calculating in millions, I used another macro to add a multiplier to each formula to convert the results to thousands. The following macro adds "*1000" to the end of each formula.

Sub AddThousands()

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

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

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

End Sub

Finally, the last macro rounded all of the formulas in the workbook to the nearest whole number.

Sub AddRound()

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

    On Error Resume Next
    
    Set cellRange = Range("A1:C5").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

Recently I needed to create a stop and resume process for an application at work. This is probably pretty basic stuff for most developers but took me some time to figure out. To do so, I first added a "Show Form" button to a worksheet. When the Show Form button is hit, it calls a userform that contains four buttons that control a simple looping procedure. The first button starts the procedure, the second stops it, the third button resumes it, and the fourth exits the procedure altogether. As the procedure loops through its values, those values are recorded in cell A1.

The procedure works by using "DoEvents" to read the value of the iStop variable. When the Go button is hit, the iStop variable is set to False. When the Stop button is hit, iStop is set to True and the procedure is forced to exit the loop.

The userform is below:

Option Explicit
Public iLoop As Integer
Public iStop As Boolean
Public Rng As Range

Sub DoLoop()
    Do Until iLoop = 15000
        Rng.Value = iLoop
        iLoop = iLoop + 1
        DoEvents
        If iStop = True Then Exit Sub
    Loop
    Rng.Value = 0
    Unload Me
End Sub

The code tied to the Go button is:

Private Sub btnGo_Click()
    iStop = False
    Set Rng = ActiveSheet.Range("A1")
    iLoop = 1
    Call DoLoop
End Sub

The code tied to the Stop button is:

Private Sub btnStop_Click()
    iStop = True
End Sub

The code tied to the Resume button is:

Private Sub btnResume_Click()
    iStop = False
    Call DoLoop
End Sub

Aned finally the code tied to the Quit button is:

Private Sub btnQuit_Click()
    iStop = True
    Rng.Value = 0
    Unload Me
End Sub

One of my frustrations with Excel's Goal Seeking feature is that you need to hand enter the "To value" input as opposed to giving you the option to tie the value directly to a value in a cell (see the screenshot below).

Recently I created a simple tool which allows the user to get around this limitation. Screenshots of the tool appear below. Note the two option buttons that appear on the bottom right-side of the tool.

In the first state shown below, the user can tie a cell value to the to the second input box. In this mode a RefEdit control is used to capture the cell value.

In the second state, the tool works very similar to Excel's built-in feature. In this mode an input box is used to capture the cell value.

Several buttons appear at the bottom of the tool. After entering the three required inputs, hit the Apply button to start the goal seeking process. If the tool can't find an answer quickly, you can use the Stop button to halt the procedure. After the tool has returned a result, the Undo button can be used to put the values back to their original state.

The tool is currently designed to work with Excel 2003 and has not been tested by anyone other than myself. It can be used as-is or converted to an add-in. If you would like the file, feel free to download it via the link below.

Many people see the markets moving up or down but have no idea of risk or rate of return of their personal investments. Andreas Steiner's web site provides a wealth of information on investment performance and many Excel templates that you can download for free. Keep in mind that that header on the downloads page states The downloads provided on this page are free and designed for educational purposes. They are meant to be illustrations to be used at your own risk, not productive applications.

There are a number of Excel book excerpts available around the web:

• Links to excerpts from four of John Walkenbach's 2007 books are available at The Spreadsheet Page.
• From the book titled Microsoft Office Excel 2003 Programming Inside Out, the chapter titled Creating Advanced User Forms is available.
• Several chapters are available from the book titled Microsoft Excel Data Analysis and Business Modeling. They are Determine the Optimal Product Mix with Solver, Introduction to optimization with the Excel Solver Tool, and Pick Your Fantasy Football Team with Solver.
• From the book titled Excel 2007 VBA Programmer's Reference, the chapter titled Primer in Excel VBA is available via Wiley.
• Links to excerpts from several chapters of the book titled Professional Excel Development are available at Stephen Bullen's Office Automation site. The chapter on RibbonX might be of special interest given the changes in Excel 2007.

Below are a few blog posts that I found especially useful:

• Jon Peltier recently posted articles titled 9 Steps to Simpler Chart Formatting and Overlapped Bar Chart - Thinner Bars in Front.
• John Walkenbach recently posted an article dealing with Color Contrast Functions with a tip titled Quantifying Color Choices. I found this post to be useful because many of the people I work with tend to use multiple background colors to highlight different types of information. As they use more and more colors, their spreadsheets become more and more distracting and difficult to read. On top of that, color printers don't always replicate the spreadsheet colors as intended so your printed copies look totally different from what you see on the screen.

MSN Money and Yahoo Finance are two good resources for those that like to follow stock trends by creating their own tables or charts in Excel. To retrieve a history of the performance of a selected stock, start at the Home page for either site. Enter a stock symbol into the "Get Quotes" input box. I started with Berkshire Hathoway's Class A shares, which closed yesterday at $115,500 / share (the highest price of any U.S. stock).

After entering the symbol into MSN Money, you get a page that contains the following:

The Yahoo Finance page looks similar to the MSN Money page:

Both sites have sidebars on the left side of their pages that allow you to access historical data. In the MSN Money sidebar, look under Charts - Historical. When you click on that link you get a chart that shows the history for the time period you selected. To retrieve the chart data, scroll down to the bottom of the page and click on the "View price history with dividends / splits" link.

In Yahoo Finance, look under Quotes - Historical Prices. Yahoo Finance offers a greater amount of price detail for download than MSN Money does. For example, you can get historical prices by day, week, or month as well as select your own time periods. The screenshot below shows the daily level of detail that's available.

Don't worry if you can't afford the $115,500 / share Class A stock. You can always pick up a few shares of the Class B stock for approximately 1/30 of the price of the Class A shares. Yesterday the Class B shares closed at $3,850.