Essential to Expert Skills progression

Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2019, 2016 and Mac versions more…

Excel linear and exponential series

This article explains the difference between linear and exponential series and shows how to model them using Excel.

Rabbit population growth is a good example of an exponential progression and in this lesson you’ll forecast the growth in a rabbit population for each month in a four-year period.  You may be surprised how quickly they can multiply.

This article is an unedited lesson from one of the 25 short focused lessons Session 2 of my “Essential Skills” book.

Leave me a comment if you find the lesson useful.

note

Linear and exponential series can also be defined using the Ribbon

Here’s how you could have used the Ribbon to complete this lesson:

  • Enter the value 200 into cell B4.
  • Select cells B4:B51.
  • Click: Home->Editing->
    Fill->Series…

The Series dialog appears.

  • Enter the following values into the Series dialog:

Note that the value: 1.2 is used because an exponential (growth) series multiplies each start value by the step value.

important

A synonym is a word you use when you can’t spell the other one.

Baltasar Gracián
(Philsopher 1601-1658)

Excel uses different terminology in various menus, dialog boxes and task panes that mean the same thing (in the context of this lesson):

Series, Forecast and Trend mean the same thing.

Growth and Exponential also mean the same thing.

In this lesson I’ll consistently use the terminology Series and Exponential.

note

Excel can calculate a linear series when the step values of cells are not equal

Excel calculates a linear series (AutoFill also calls this a fill series) using this formula:

Start Value + Step Value

Sometimes you may need to produce linear series values from a series of numbers that have different step values.  For example, consider this range:

In this case Excel will use a more complex mathematical operation (called the least-squares algorithm) to determine the correct step value to use. In the above example, Excel would calculate a step value of 1.01.

trivia

According to an old legend, the inventor of the game of Chess presented his new game to a powerful king.  The king was so pleased with the game that he offered the inventor any reasonable reward.

The inventor asked for one grain of rice for the first square on the chessboard, two for the second, four for the third… and so on for each of the 64 squares on the chessboard. 

The king thought the inventor was foolish to ask for such a simple gift.  Much later he found that there was not enough rice in the entire world to fulfil the inventor’s request.

With the Excel skills you have learned in this lesson, you should easily be able to model the King’s dilemma and discover exactly how many grains of rice would have been needed (about 18.5 Quintillion).

Lesson 2-18: Understand linear and exponential series

Linear series

In: Lesson 2‑14: Use AutoFill for text and numeric series, you used AutoFill’s Fill Series method to automatically create these numeric series.

Linear and exponential series - illustration of linear series

These are both examples of Linear series.

To create the linear series in column C you selected cells C18:C19 before AutoFilling down the column:

To calculate a linear series Excel first identifies the step value.  The linear series in column C has a step value of nine (18-9=9).

When you use AutoFill’s Fill Series method, Excel adds the step value to the start value (the number shown in the previous cell). 

In the above example, if you AutoFill down to cell C20, the value shown in cell C20 will be 27.  This is calculated by adding the step value of 9 to the start value of 18.

This is an example of a very simple linear series.  Excel is also able to calculate a linear series even when the step values of the selected cells are not equal (see sidebar).

Exponential (or Growth) series

An exponential series is calculated by multiplying (rather than adding) the start value by the step value.  This type of series is usually referred to as an exponential (rather than linear) series.  Excel also uses the term growth series as a synonym for exponential series (see sidebar).

Here’s an example:

  • A truly excellent restaurant opens in town. On the first day they only have one customer, but the customer is so delighted by the food, service and value that the customer tells two friends.
  • The next day the two friends eat there, and the restaurant has two diners.
  • The two friends are also so pleased with their experience that they each tell two friends. On the third day the restaurant has four diners.

You can see that the restaurant owner might expect that this trend will continue and that each day the number of diners will double (in Excel’s terminology, the step value is 2).  Eventually, of course, the restaurant will become full and the trend will have to end (in Excel’s terminology the number of seats in the restaurant is the stop value).

In this lesson you will model this type of exponential series.

1. Open the sample file Rabbit Population in Excel.

2. If the rabbit population of an island increases at the rate of 20% per month, use an exponential progression to calculate how a newly introduced community of 200 rabbits will grow in four years.

  • Type the value: 200 into cell B4 to set the rabbit population at the beginning of the period. 
  • Type the value: 240 into cell B5 to set the rabbit population after one month.
  • Select the range B4:B5.
  • Hold down the right mouse button on the AutoFill handle at the bottom-right of the selected range and drag down to AutoFill to cell B51.
  • Click Growth Trend from the shortcut menu. Note that this is the shortcut menu’s synonym for: Exponential Series.
Linear and exponential series - defining an exponential series.

After four years (48 months) the 200 rabbits will have grown to a population of a little over a million.

3. Save your work as Rabbit Population-1.

Here’s the sample file at the end of the session.  If you ran into any problems you can study the finished workbook to identify the problem.

I hope that you enjoyed the lesson and now have a complete understanding of Linear and Exponential series.

This lesson is excerpted from the above book.  Smart Method books are the only up-to-date Excel books published with a new edition every six months.  

Available as both a paper printed book and as an e-book you can instantly download today.

 

Lessons in Session 2

Session Two: Doing Useful Work with Excel
Session Objectives
Lesson 2‑1: Enter text and numbers into a worksheet
Lesson 2‑2: Create a new workbook and view two workbooks at the same time
Lesson 2‑3: Use AutoSum to quickly calculate totals
Lesson 2‑4: Select a range of cells and understand Smart Tags
Lesson 2‑5: Enter data into a range and copy data across a range
Lesson 2‑6: Select adjacent and non-adjacent rows and columns
Lesson 2‑7: Select non-contiguous cell ranges and view summary information
Lesson 2‑8: AutoSelect a range of cells
Lesson 2‑9: Re-size rows and columns
Lesson 2‑10: Use AutoSum to sum a non-contiguous range
Lesson 2‑11: Use AutoSum to calculate average and maximum values
Lesson 2‑12: Create your own formulas
Lesson 2‑13: Create functions using Formula AutoComplete
Lesson 2‑14: Use AutoFill for text and numeric series
Lesson 2‑15: Use AutoFill to adjust formulas
Lesson 2‑16: Use AutoFill options
Lesson 2‑17: Speed up your AutoFills and create a custom fill series
Lesson 2‑18: Understand linear and exponential series
Lesson 2‑19: Use automatic Flash Fill to split delimited text
Lesson 2‑20: Use manual Flash Fill to split text
Lesson 2‑21: Use multiple example Flash Fill to concatenate text
Lesson 2‑22: Use Flash Fill to solve common problems
Lesson 2‑23: Analyze Data with the Ideas feature
Lesson 2‑24: Use the zoom control
Lesson 2‑25: Print out a worksheet
Session 2: Exercise
Session 2: Exercise answers

Related Articles

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Leave a Reply

Your email address will not be published.

twelve − 5 =

Cross-lesson hyperlinks

In order to use all cross-lesson hyperlinks you will need to download the full e-book. The Expert Skills e-book contains 253 short lessons that will teach you everything there is to know about Excel.

When you download the e-book you’ll be able to access every lesson,  online or offline, on all of your devices, using Amazon’s free advanced e-book reader