Recently I've had to use formulas that perform left-sided lookups at work. The combination of Index and Match in a formula will do so, but I always seem to have a hard time remembering how to build the formula. Recently I put together this color-coded example to help:

The formula in cell C8 looks like this:

=INDEX(Range_of_Values_To_Look_Up,MATCH(Look_Up_Value,Range_of_Look_Up_Values,0),1)

or

=INDEX(B2:B6,MATCH(B8,D2:D6,0),1)

If you need to report statistics or financial data in a standard template each month, consider using Excel as a simple database and the SUMPRODUCT function within formulas as the engine that drives the reporting. For example, many financial reports include the following standard data elements:

• Current month and year-to-date results.
• The variance of current month and year-to-date results against a plan.
• Current month and year-to-date results measured against the same month last year and year-to-date.

As soon as the data is compiled, many analysts enter the data into a spreadsheet. They then change the formulas in the report to reference the data that needs to appear for the most current month. Given the manual changes to formulas, the risk of error is increased.

Rather than recreating formula references each month, the spreadsheet can be set to work much more efficiently. By changing the month in one cell and the year in another, the SUMPRODUCT function when used within formulas can automatically update the entire report.

The following example is based on two sheets in a workbook:

• The first sheet, called "dBase", contains the data needed to feed the report.
• The second sheet, called "Rpt", contains the actual report. It also contains two cells that allow the user to update the report by changing the year and the month.

Beginning the example, the screenshot below is a very simple example of the data that might be needed for the typical financial statement. The worksheet in which the data resides is called "dBase":

The report below contains the elements as described in the bullets above. The worksheet in which the report resides is called "Rpt".

SUMPRODUCT formulas are included in the formulas within the report. Those formulas act as month and year-to-date lookups.

To build the SUMPRODUCT formulas, I've first created two named ranges. The first named range, called "Year", covers the range B4:B29 of the dBase sheet. The named range dialog box for the formula named "Year" looks like this:

The second named range, called "Month", covers the range C4:C29 of the dBase sheet. The named range dialog box for the formula named "Month" looks like this:

Now it's time to build the report. The SUMPRODUCT function within formulas looks up the report data based on the entries into cells E4 (the Year) and E5 (the Month). The formulas are as follows:

To conclude, this setup allows allows the user to enter data into the dBase sheet. The user can then automatically update the report for any month and year via inputs to the month (cell E4) and year (cell E5) inputs in the Rpt sheet.

Below are the calculations to perform a simple variance analysis of discounts against plan in a healthcare setting. Selected cells are color coded to make the calculation easier to understand. The input cells reside in the range E3:F4.

In the example, the components of gross revenue are volume, payer mix, and acuity. The discount rate, by definition, includes the payer mix and acuity components. As a result, the "plug" is volume.

Many companies produce reports at a top-level, department, division, and/or cost center level. When Excel is used to build these reports, the normal practice seems to be to create a new sheet for each department, division, and/or cost center. Each sheet contains the data and calculations for that area. A single top-level reporting sheet is then created to which each department, division, and/or cost center sheet links to. When it's all over and done the workbook can become very large, prone to error, and difficult to audit.

An easier and more accurate approach is to use the combination of lookup formulas and a VBA loop to build the report. In this example, only four sheets in the Excel file are illustrated (it could be only one if designed that way). The first sheet, called "dBase", contains the raw data for each department, division, and/or cost center. The second sheet, called "Calcs", contains a single set of calculations needed to build the final report. The third sheet, called "Load", contains a summary of the data from the Calcs sheet needed for the final report. The final sheet, called "Report", is the final report.

The example begins with a screenshot of the sheet called "dBase" (for database). The "dBase" sheet contains ten departments. The Commerical / Contract and Government volumes for each department is provided for the years 2007 and 2008. The final report requires that you show the components of payer mix variance for each department.

The next screenshot shows the sheet called "Calcs". The blue cell (C2) contains the name of the department. The yellow cells contain lookup formulas that reference the "dbase" sheet.

For example, the lookup formula in cell E8 is:

=VLOOKUP($C$2,dBase!$A$3:$E$12,2,FALSE)

The lookup formula in cell F8 is:

=VLOOKUP($C$2,dBase!$A$3:$E$12,4,FALSE)

The next screenshot shows the sheet called "Load". The purpose of this sheet is to reference all of the values that need to appear in the final report into one row of data.

The data that appears in the "Load" sheet is referenced to the green cells in the "Calcs" sheet.

Finally, a VBA procedure is run to create the report. A loop is created that, when combined with the lookup formulas, does the following:

• The VBA procedure loads the name of the department into cell C2 of the "calcs" sheet.
• The lookup formulas in the calcs sheet reference the data from the "dBase" sheet for that particular department.
• The payer mix calculations are performed.
• The payer mix data is referenced from the "calcs" sheet into row of data in the "Load" sheet.
• The data from the "Load" sheet is copied as values into the final report.

In this example, the procedure described above is performed ten times - once for each department as referenced in the range B8:B17 of the "Report" sheet. The VBA code that performs the work is below:

Sub BuildReport()
    Application.ScreenUpdating = False
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim Rng4 As Range
    Set Rng2 = Sheets("Calc").Range("C2")
    Set Rng3 = Sheets("Load").Range("B3:G3")
    Set Rng4 = Sheets("Rpt").Range("C8:H8")
    For Each Rng1 In Sheets("Rpt").Range("B8:B17")
        Rng2.Value = Rng1.Value
        Rng3.Copy
        Rng4.PasteSpecial xlPasteValues
        Set Rng4 = Rng4.Offset(1, 0)
    Next Rng1
    Sheets("Rpt").Range("A1").Select
End Sub

After the macro has been run, the final report looks like this:

The advantages of using lookup formulas and VBA loops to build the report are these:

• Only one set of calculations (the "Calcs" sheet) needs to be audited for accuracy. This is especially advantageous as the number of departments, division, etc. needed on the final report grows.
• The audit risk concerning the workbook is reduced due to less sheets and data ranges needing to be proofed.
• The physical size of the workbook is reduced which make it easier to maintain and transfer to others via email, web, etc.
• The VBA procedure runs fast and the report is produced almost instantaneously.

Potential disadvantages of using lookup formulas and VBA loops to build the report are these:

• If you need to pass the workbook on to someone that has no experience with VBA, the issue of who can maintain the workbook may come up.
• If your manager is clueless when it comes to lookups or VBA, he or she may prefer the "many sheets and calculations" approach so they can understand it (runs hand-in-hand with the first bullet).

Below are interesting items from around the web:

The PTS Blog recently illustrated three VBA techniques for chart formatting:

• VBA Conditional Formatting Charts by Series Name
• VBA Conditional Formatting of Charts by Value
• VBA Conditional Formatting of Charts by Series Label

Process Trends provides an interesting writeup on how to create Cycle Charts. Also included is a downloadable template with VBA code that automatically creates these charts.

Andy Pope recently provided a method for building Conditional Line Charts Using Formulas:

The Excel Team Blog provides a writeup on chart templates for Office 2007:

Have you seen those star raters and wonder how they work? Chris Curtis provides a link to a CSS Based Star Ranking System:

Pie charts, as well as many other charts, can be built to show percentages of a whole. Unfortunately, many times the pie chart percentages do not tie in total to the total of the source document percentages. Microsoft acknowledges the problem via this knowledge base article titled Pie Chart Shows Incorrect Percentage Value in Excel.

As a potential work-around, I attempted to create a source document formula that works similar to the logic described in the knowledge base article. The purpose of the formula is to force the rounded percentages in the source report to always tie to the pie chart percentages.

Refer to the screenshot below:

A source range, named "Data", covers the range C4:C10. Column D reflects the rounded source document percentages. When added together, the percentages equal 101%.

Column E contains the alternative percentages that tie to the pie chart. The formula, entered as a multicell-array into the range E4:E10, is as follows:

{=IF((Data-ROW()/10^10)=LARGE((Data-ROW()/10^10),1),
1-SUMPRODUCT(((Data-ROW()/10^10)<>LARGE((Data-ROW()/10^10),1))
*(ROUND(Data/$C$11,2))),ROUND(Data/$C$11,2))}

The formula works essentially as the knowledge base article describes. It:

• Uses the ROW function to first break any ties that may occur when the same number appears two or more times.
• Using the numbers calculated by the ROW function, it then uses the LARGE function to determain which percentage is the maximum.
• If the percentage is the maximum, it subtracts the total of all of the the percentages excluding the maximum from 100%. All other percentages are calculated and rounded as normal.

In the example, the formula recognizes the rounded value for Gamma as the maximum. Because the data does not add to 100%, the formula subtracts one from the Gamma value to force the percentages to round to 100%. Likewise, the percentages in the pie chart show the same values.

To test the formula, I used the RANDBETWEEN function and a macro to quickly test 10,000 random scenarios. The formula appeared to work well with values that round in total to 99% or 101%. However, when values round to 98% or 102%, I did find occurances where the formula returns values that differ from the pie chart. In those cases Excel does not seem to assign the difference to one single value. Rather, Excel appears to pick two of the same values and distribute the difference evenly. Given the logic as described in the knowledge base article, I'm at a loss to explain why.

Below is an example where the pie chart percentages differ from the formula:

The formula calculates Beta to be the maximum and subtracts two from it to force the total to equal 100%. However, Excel subtracts one from Beta and Delta to get to 100%.