The Thrill of F4

Quick Summary:

  • Use the F4 key on your keyboard to toggle from a relative cell reference through the three variations of absolute cell references.
  • The F4 key works for this application in Microsoft Office Excel and Google Sheets.


Via a few effective examples, Mike “ExcelIsFun” Girvin explains cell references and the time-saving advantages of using F4 in the following video. He specifically covers the F4 keyboard shortcut starting around 3:25.

Mike also teaches a broader, productive lesson of analytical inquiry and the benefits of tinkering, when he observes:

“This is getting annoying.”

And he asks:

“There’s got to be a way in Excel, instead of having to create all these formulas by hand — there’s got to be a way to tell Excel…

Let us know what you think.

If you use a spreadsheet program other than Microsoft Office Excel or Google Sheets , let us know how absolute cell references are manipulated therein.

The CAGR: Handle with Care

Quick Summary:

  • The CAGR formula takes into account only two datapoints.
  • A CAGR is arguably a weak method for expressing historical trends, particularly when more than two datapoints are available.
  • An AAGR calculation can incorporate multiple datapoints and is therefore more useful for expressing accurate historical trends.
  • A simple arithmetic average of growth rates (a calculation distinctly different from the AAGR) is also an erroneous approach.


Take great care when using the CAGR, or the compound annual growth rate, to express or understand average growth or decline over multiple periods.

Consider the first series of data below, which represents annual demand for a hypothetical product from 2001 through 2013. The 2001-2013 CAGR amounts to 3.7%, while the average annual growth rate, or AAGR, languishes at a mere 1.7%. Which is more accurate?

The CAGR calculation takes into account only two datapoints: here, 1244 and 1924, or the two endpoints in the time series. The CAGR represents a a smooth curve from 1244 to 1924. Plotting smoothed, multi-period curves between two quantities can be very useful, particularly when the subject is money. Money often begets money in the form of agreed interest, so the CAGR is a fine method for calculating, say, the future 2013 value of a 2001 deposit of 1244 if interest is compounded annually at a rate of 3.7%. But product demand, even in value terms, and money are different domains. Markets stock and destock, for example. Markets boom and slump. Markets rarely follow smooth curves across the interim between two distant periods.

That is why it is useful to examine all available datapoints over time, rather than to limit the calculation to only the start and end points of a time series. The AAGR does just that. It calculates a smooth trend just like the CAGR, but the AAGR fits that trendline among multiple datapoints instead of spanning it between two.

Using spreadsheet symbols for illustration purposes, the CAGR calculation resulted from ((1924 / 1244) ^ (1/12)) – 1. The =RATE formula can be used as well; it is also limited to only two datapoints. The AAGR was calculated using 13 datapoints in the following formula: =LOGEST(1244, 1604, 1492, 1547, 1338, 1855, 1725, 1290, 1387, 1439, 1664, 1789, 1924) – 1.

Again, the CAGR and AAGR are quite different. The CAGR outpaces the AAGR by more than a factor of two.

To further illustrate the weakness of the CAGR, the original data have been modified to exhibit 5% annual growth over the interim between 2001 and 2012 with a sudden drop in 2013 to an unchanged value of 1924. The CAGR is nevertheless the same, while the AAGR has shifted to reflect the increase in average growth.

CAGR vs AAGR up tween

In a case of 5% annual decline from the 2001 value and a sudden jump to an outlier value in 2013, the CAGR again disregards all values except two (ie, the 1244 in 2001 and the 1924 in 2013. The AAGR, however, reflects all datapoints (NB: the AAGR equals minus 1.6%).

CAGR vs AAGR down tween

Handle the CAGR with care.

All That Means Well May Not End Well

Resist the temptation to take an arithmetic average, or mean, of growth rates in order to calculate average growth over multiple periods. That method yields erroneous results. As illustrated in the chart below, if 2001 demand of 1000 units grew 30% in 2002 to 1300, but then declined 30% in 2003 (1300 x (1-0.3) = 910), an arithmetic average of the rates yields a figure of 0%. In other words, the calculation claims that demand remained flat between 2001 and 2003, but that cannot be right, for demand fell from 1000 to 910 units. An arithemetic average of growth rates is therefore not helpful for analysis of multi-period growth.

Arith Mean of Growth Rates

For further reading, Professor Richard Rumelt of UCLA provides an excellent overview of historical trends analysis, here.

Have you started to notice misuse of the CAGR in annual reports, investor presentations, market research reports, or elsewhere? Consider letting us know the specifics in a comment.