How to Calculate Logarithmic Growth in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to calculate logarithmic growth in Excel. Mainly, there are two types of growth. They are logarithmic growth and exponential growth. The difference between these two growths is the speed of the growth. In an exponential case, the growth starts slowly at the beginning and later it speeds up. In a logarithmic case, the growth starts fast, and then it gets slower. Today, we will demonstrate 2 easy methods. Using the methods you can easily determine the logarithmic growth. So, without further ado, let’s start the discussion.


What Is Logarithmic Growth?

Logarithmic growth is a type of growth where the growth is usually high at the beginning and then, gradually it slows down. It is the inverse of exponential growth. Generally, it is used to model sound levels, earthquakes, and many more practical phenomena. Using this growth, we can predict a value at a certain data point.

The equation of the logarithmic growth is given below.

y=b*ln(x)+a

Here, y is the response variable, x is the predictor variable, and a & b are the growth coefficients.

Read More: How to Do Inverse Log in Excel 


How to Calculate Logarithmic Growth in Excel: 2 Easy Methods

To explain the methods, we will use a dataset that contains information about the Height (in cm) and Weight (in kg) of a person. It shows the weight of a person at different heights. From the dataset, we will try to calculate the weight of the person when his height is 150 cm. You can see that we don’t have the weight at a height of 150 cm inside the dataset. We will use logarithmic growth and predict the weight. Here, the weight will be the response variable (y) and height will be the predictor variable (x).


1. Apply Scatter Chart to Calculate Logarithmic Growth in Excel

In Excel, we can apply the scatter chart to calculate logarithmic growth. The Scatter Chart is used to plot two groups of data in the XY coordinates. Let’s follow the steps below to see how we can calculate logarithmic growth using a scatter chart.

STEPS:

  • First of all, select the data points of your dataset.

Apply Scatter Chart to Calculate Logarithmic Growth in Excel

  • Secondly, go to the Insert tab and click on the Insert Scatter icon. A drop-down menu will occur.
  • Click on the Scatter icon from the drop-down menu.

Apply Scatter Chart to Calculate Logarithmic Growth in Excel

  • You will see a graph on the sheet.
  • Click on the plus (+) sign to open the options.

Apply Scatter Chart to Calculate Logarithmic Growth in Excel

  • Now, put the mouse pointer on the right side of Trendline. A drop-down menu will appear.
  • Select More Options from there. This will open a Format Trendline section on the right side of the screen.

Apply Scatter Chart to Calculate Logarithmic Growth in Excel

  • In the next step, select Logarithmic and check the ‘Display Equation on chart’ section.

Apply Scatter Chart to Calculate Logarithmic Growth in Excel

  • As a result, you will see the logarithmic growth equation with values on the chart.
  • From the chart, we can see b = 191.52 and a = -916.74.

Apply Scatter Chart to Calculate Logarithmic Growth in Excel

  • In the following step, change the chart and axis titles to make the graph more understandable.

Apply Scatter Chart to Calculate Logarithmic Growth in Excel

  • After extracting the values of a & b, we can calculate the logarithmic growth at a height of 150 cm easily.
  • To do so, we need to determine the natural log of height (x). We will do these procedures in a new sheet.
  • For that, select Cell C5 and type the formula:
=LN(C4)
  • Hit Enter to see the result.

Apply Scatter Chart to Calculate Logarithmic Growth in Excel

Here, we have used the LN function to determine the natural log value of the height.

  • After that, type the value of the coefficients a & b in Cells C7 & C8 respectively.

Apply Scatter Chart to Calculate Logarithmic Growth in Excel

  • In the end, select Cell C10 and type the formula:
=(C8*C5)+C7
  • And press Enter to see the result.

Apply Scatter Chart to Calculate Logarithmic Growth in Excel

Here, C8 indicates b, C5 denotes ln(x), and C7 refers to a.

Read More: How to Plot Log Scale in Excel


2. Determine Logarithmic Growth with Data Analysis Tool in Excel

Another way to determine logarithmic growth is to use the Data Analysis tool in Excel. This is also a simple method. But, we will not get any plot in this method. Here, we will use the same dataset again and try to calculate the weight at a height of 150 cm. Then, we will compare the result with the previous method. So, without any delay, let’s observe the steps below.

STEPS:

  • In the first place, determine the natural log values of the heights.
  • To do that, select Cell C5 and type the formula:
=LN(B5)

Determine Logarithmic Growth with Data Analysis Tool in Excel

  • Secondly, press Enter to see the result.

Determine Logarithmic Growth with Data Analysis Tool in Excel

  • In the third step, use the Fill Handle to copy the formula in the cells below.

Determine Logarithmic Growth with Data Analysis Tool in Excel

  • Now, go to the Data tab and select Data Analysis.

  • A Data Analysis dialog box will appear.
  • Select Regression from there and click OK to proceed.

  • At this moment, type the Input Y Range and Input X Range in the Regression pop-up box.
  • Remember, the weight is the Y Range and the natural log of height is the X Range.
  • After that, select New Worksheet Ply and click OK to proceed.

  • Instantly, you will see the Summary Output in a new sheet.
  • From the Summary Output, you can get the values of the coefficients.
  • In the highlighted region of the picture below, you can see two coefficients.
  • Here, Intercept denotes the value of a, and X Variable 1 indicates the value of b.

  • Finally, copy the values of a & b and paste them into the calculation section.
  • In this case, we get a weight of 42.9166 kg at a height of 150 cm. This value is almost the same as the previous one. This little error occurs because we took more places after the decimal point.

Read More: How to Log Transform Data in Excel 


Download Practice Book

Download the practice book here.


Conclusion

In this article, we have demonstrated 2 easy methods to Calculate Logarithmic Growth in Excel. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo