To keep the percent change formula from returning errors and/or extreme values, consider a nested IF statement to trap errors or values that fall outside of a certain range. In the example below, "n/a" refers to "not applicable". Errors have been trapped in lines one through three. In line four, the formula returns "n/a" because the absolute value of the change is greater than 200%. This logic is written into the formula to prevent it from returning exteme values.

The formula in G4 is below. It is copied from cell G4 into cells G6, G8, G10, and G12.

Over time I've found that the interpretation of percent change is one of the most simple and misunderstood calculations in finance. As an example, take the following data:

The formula in cell C3 is

This is the formula that I've found most people use. The formula works great when you're comparing positive numbers. However, a problem occurs when you bring in the negatives. Looking at cell C11, is it correct to say that the increase from -45 to -35 is -22.2%? If -35 is a larger number than -45, how can the increase be a negative?

If you look at all numbers, positive and negative, as being on a single scale the calculation is easier to see. Now we'll take the same data above and apply a slightly different formula:

The formula in cell C3 is

When you look at column C, all of the percent increases are positive percentages. Likewise, when you look at column D all of the percent decreases are negative percentages. Positive percentages correspond to moving up the number scale and negative percentages correspond to moving down the number scale.

To conclude, the formula that includes Excel's ABS function is the correct formula. The ABS function insures that the denominator in the formula is always positive which allows the formula to return the correct percentage change. If you use this formula and think of the number scale, you'll never have to worry about the calculation of percent change and / or how to explain it to those that may not understand how to interpret it.

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:

or

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:

Monthly Financial Results - Revenue
Cell	Formula
D16	=SUMPRODUCT((Year=Rpt!$D$2)*(Month=Rpt!$D$3)*(dBase!$E$4:$E$29))
E16	=SUMPRODUCT((Year=Rpt!$D$2)*(Month=Rpt!$D$3)*(dBase!$H$4:$H$29))-D13
F16	=SUMPRODUCT((Year=Rpt!$D$2-1)*(Month=Rpt!$D$3)*(dBase!$E$4:$E$29))

Year-To-Date Financial Results - Revenue
Cell	Formula
I16	=SUMPRODUCT((Year=Rpt!$D$2)*(Month<=Rpt!$D$3)*(dBase!$E$4:$E$29))
J16	=SUMPRODUCT((Year=Rpt!$D$2)*(Month<=Rpt!$D$3)*(dBase!$H$4:$H$29))-I13
K16	=SUMPRODUCT((Year=Rpt!$D$2-1)*(Month<=Rpt!$D$3)*(dBase!$E$4:$E$29))

Monthly Financial Results - Expense
Cell	Formula
D18	=SUMPRODUCT((Year=Rpt!$E$4)*(Month=Rpt!$E$5)*(dBase!$F$4:$F$29))
E18	=SUMPRODUCT((Year=Rpt!$E$4)*(Month=Rpt!$E$5)*(dBase!$I$4:$I$29))-D18
F18	=SUMPRODUCT((Year=Rpt!$E$4-1)*(Month=Rpt!$E$5)*(dBase!$F$4:$F$29))

Year-To-Date Financial Results - Expense
Cell	Formula
I18	=SUMPRODUCT((Year=Rpt!$E$4)*(Month<=Rpt!$E$5)*(dBase!$F$4:$F$29))
J18	=SUMPRODUCT((Year=Rpt!$E$4)*(Month<=Rpt!$E$5)*(dBase!$I$4:$I$29))-I18
K18	=SUMPRODUCT((Year=Rpt!$E$4-1)*(Month<=Rpt!$E$5)*(dBase!$F$4:$F$29))

Monthly Results - FTEs
Cell	Formula
D22	=SUMPRODUCT((Year=Rpt!$E$4)*(Month=Rpt!$E$5)*(dBase!$G$4:$G$29))
E22	=SUMPRODUCT((Year=Rpt!$E$4)*(Month=Rpt!$E$5)*(dBase!$J$4:$J$29))-D22
F22	=SUMPRODUCT((Year=Rpt!$E$4-1)*(Month=Rpt!$E$5)*(dBase!$G$4:$G$29))

Year-To-Date Results - FTEs
Cell	Formula
I22	=SUMPRODUCT((Year=Rpt!$E$4)*(Month<=Rpt!$E$5)*(dBase!$G$4:$G$29))/E5
J22	=SUMPRODUCT((Year=Rpt!$E$4)*(Month<=Rpt!$E$5)*(dBase!$J$4:$J$29))/E5-I22
K22	=SUMPRODUCT((Year=Rpt!$E$4-1)*(Month<=Rpt!$E$5)*(dBase!$G$4:$G$29))/E5

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.

Excel's "Add Trendline" option automatically provides an equation for the Linear Regression option. The model below shows how you can manually create that equation if needed. Also included is a calculation for correlation.

The formula for the slope (cell F18) is:

The formula for the Y intercept (cell F19) is:

Although not a part of the linear regression calculations, you might find it useful to know how to manually calculate the correlation of the X and Y data. The formula to calculation correlation (cell F20) is:

A chart showing the original points and trendline is below:

The Excel-generated trendline formula appears at the top right side of the chart. The manually calculated trendline in the example starts in cell F26 and is copied through the range F26:F33. That formula is below:

I've found that although arrays are not intuitive to most Excel users they can come in very handy at times. For example, a VLOOKUP formula similar to the one below might be a good alternative to beating the limitations of a nested IF statement.

Calculate percent change with ABS function.

In my opinion, the calculation of percent change is one of the most misunderstood calculations in finance.  Most of the time the calculation is correct if both of the numbers compared are positive.  The problem arises when one or both of the numbers is negative.  If the denominator happens to be negative the calculation will be incorrect.  Unfortunately the calculation is so widely used that most people don’t realize there’s an error until it’s too late.

To insure that the sign in the denominator is always positive, apply Excel’s “ABS” function to the denominator.  If you make this a habit, your calculation of percent change will always be correct.

For example, if the price of gas rises from $2.50 / gallon to $2.75 / gallon, the price has risen 10%.

Let’s assume there is a credit on the financial statement and the beginning balance of an account was -$5.  The next month the balance in the account is $10.  The percent change in the account between the two months is calculated as: