How to calculate average percentage growth in Microsoft Excel

Ever been stuck trying to work out how to calculate average percentage growth in Microsoft Excel? Paolo Lenotti of Excel with Business is on hand to show you how

Average percentage growth is not an official accounting or bookkeeping term, but it is still a very useful measure of gain (or loss) over a period of time (of an investment, revenue, profit, etc.). This can help to assess a client’s past performance and, ideally, predict likely future performance.

Calculating average growth in Microsoft Excel

There are different ways of calculating average growth in Excel (e.g. LOGEST, LINEST, lines of best fit, etc.) and some of these will give different results. Here’s the simplest: an annual level of growth that would take you from first year’s level to the last.

Suppose you have profit figures year-on-year as follows:

Microsoft Excel graph example

The line on the graph shows average growth in line with our definition.

We use this simple formula:

=100*(<final value>/<initial value>)^(1/<number of years’ growth>)-100

For the data in the example above, which spanned seven years’ of growth, this formula was used:

=100*(C10/C7)^(1/7)-100 (where cells C7 & C10 contain the first & last data values)

This gives a result of 2% annual average growth.

Technically this is called Compound Annual Growth Rate (CAGR).

Excel with Business online training

AAT members have free access to this and many other Excel tips through their MyAAT account. Excel with Business‘s online course is designed to teach the features of Microsoft Excel that are useful to AAT members, saving time and offering a tailored learning experience.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.