Mursalin Ibne Salehin
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)+aHere, 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.
- 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.
- You will see a graph on the sheet.
- Click on the plus (+) sign to open the options.
- 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.
- In the next step, select Logarithmic and check the ‘Display Equation on chart’ section.
- 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.
- In the following step, change the chart and axis titles to make the graph more understandable.
- 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.
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.
- In the end, select Cell C10 and type the formula:
=(C8*C5)+C7- And press Enter to see the result.
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)
- Secondly, press Enter to see the result.
- In the third step, use the Fill Handle to copy the formula in the cells below.
- 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.
