Researchers often transform data to convert original data into another form to meet certain assumptions. Researchers can do several forms of data transformation. Natural logarithm transformation is a form of transformation frequently used by researchers in data analysis.
For example, when a researcher analyzes the Cobb-Douglas production function, one assumption when using multiple linear regression is that the researcher needs to transform the original data into natural logarithms.
This is done because the original form of the Cobb-Douglas production function, which was previously exponential, is converted into a linear form. Therefore, the original data can be transformed into natural logarithms so that one assumption of the Cobb-Douglas production function can be fulfilled.
Furthermore, in the assumption test of multiple linear regression using the ordinary least square method, researchers often use data transformation to help the transformed data meet the required assumptions.
Given the importance of knowledge and understanding of researchers in performing natural logarithm transformation, in this tutorial, Kanda Data will write about how to transform the original data into the form of natural logarithms (Ln) and how to revert the transformed data into its original form (anti-Ln) using Excel.
Example Data for Natural Logarithm Transformation Exercises
To further facilitate researchers in understanding how to transform the original data into natural logarithms, I will provide an example of data that will be used for exercise material.
The data consists of cross-sectional data from 15 countries measured in two variables, namely rice consumption and per capita income. Rice consumption is measured in million tons, and the per capita income variable is measured in millions per capita. In more detail, the data collected by the researcher can be seen in the table below:
Based on the table above, it can be seen that the research objective is to find out the effect of per capita income on rice consumption. Therefore, it can be determined in the specification of the linear regression equation that rice consumption is the dependent variable, and income is the independent variable.
For the exercise material, the data collected by the researcher will be transformed into the form of natural logarithms. Furthermore, based on the natural logarithm transformation results data, it will be reverted to its original form.
Transforming original data into natural logarithm (Ln) form in Excel
Converting original data into natural logarithm form can be done using various data processing applications. Excel can also be used to convert data into natural logarithm form quickly and easily.
The first step to perform data transformation in Excel is to input the data to be transformed into an Excel sheet. Based on the data collected by the researcher according to the table above, the researcher needs to ensure that the data is in number format.
Next, the researcher can create two additional columns labeled LnY and LNX1. Then, the researcher can move the cursor below the label of the LnY variable in Excel cell D4.
The next step is to type the formula =LN( and then move the cursor to the first original data of the Y variable (cell B4), type a closing parenthesis, and press Enter. Based on the calculation results, the natural logarithmic transformation of 48 is 3.87. The detailed calculation results of transforming the original data into natural logarithmic form can be seen in the table below:
Based on the table above, to speed up the calculation, the researcher only needs to copy and paste the formula from cell D4 to cell E4 and then copy and paste the formula to row 18, as shown in the picture above.
How to Convert Natural Logarithm Form into Original Data (Anti-Ln) in Excel
Furthermore, researchers also need to understand how to convert natural logarithm data back into the original form. This is useful when researchers lose the original data but still have the data in the form of natural logarithmic transformation.
The steps to convert natural logarithmic data back into the original form are almost the same as the previous sub-chapter I wrote in the above paragraph. The difference lies in the formula used in Excel.
The steps that need to be taken by researchers when converting natural logarithmic data back into the original form are to create two additional columns labeled AntiLnY and AntiLnX1.
Next, move the cursor below the label of the AntiLnY (cell F4). Type the formula =EXP(, then move the cursor to the natural logarithm data of LnY (cell D4), type a closing parenthesis, and press Enter.
The original data of the natural logarithmic data 3.87 is 48. In detail, the result of converting natural logarithmic data into the original form can be seen in the table below:
Based on the table above, it can be seen that the value of cell F4 is equal to cell B4. This indicates that the data obtained is exactly the same as the initial data on the Y variable of 48. Thus, the conversion of natural logarithmic data back into the original form has been successfully done.
For the next calculation results, the formula in cell F4 can be copied and then pasted into the other related cells. The results can be seen in the table shown above.
It is the tutorial that I can write on this occasion. Hopefully, it will be useful and provide new insights for all of you. Please stay tuned for the upcoming articles.
