In Exponential Regression and Power Regression we reviewed four types of log transformation for regression models with one independent variable. We now briefly examine the multiple regression counterparts to these four types of log transformations:

Level-level regression is the normal multiple regression we have studied in Least Squares for Multiple Regression and Multiple Regression Analysis. Log-level regression is the multivariate counterpart to exponential regression examined in Exponential Regression. Namely, by taking the exponential of each side of the equation shown above we get the equivalent form

Similarly, the log-log regression model is the multivariate counterpart to the power regression model examined in Power Regression. We see this by taking the exponential of both sides of the equation shown above and simplifying to get

Since any positive constant *c* can be expressed as *e*^{ln c}, we can re-express this equation by

(where clearly the coefficients are not the same, and where we included negative values for as well).

We now give an example of where the log-level regression model is a good fit for some data.

**Example 1**: Repeat Example 1 of Least Squares for Multiple Regression using the data on the left side of Figure 1.

**Figure 1 – Log-level transformation**

The right side of the figure shows the log transformation of the price: e.g. cell G6 contains the formula =LN(C6). We next run regression data analysis on the log transformed data. We could use the Excel **Regression** tool, although here we use the Real Statistics **Linear Regression** data analysis tool (as described in Multiple Regression Analysis) on the X input in range E5:F16 and Y input in range G5:G16. The output is shown in Figure 2.

**Figure 2 – Regression on log-level transformed data**

The high value for R-Square shows that the log-level transformed data is a good fit for the linear regression model. Since zero is not in the 95% confidence intervals for Color or Quality, the corresponding coefficients are significantly different from zero.

We could also use the array formula =LOGEST(C6:C16,A6:B16,TRUE,TRUE) to obtain the following output (the labels have been manually added):

**Figure 3 – Use of LOGEST function**

Note that the slope/intercept values in row 7 of Figure 3 are the exponential of the linear coefficients calculated in Figure 2: e.g. the value of cell R7 is equal to EXP(J23) and the value of cell T7 is equal to EXP(J21).

We can also use the regression model to predict the price of a given diamond. For example, suppose a diamond has Color = 4 and Quality = 5 or Color = 7 and Quality = 7, then the following three approaches show how to predict the Price based on the regression model:

**Figure 4 – Forecasting using the log-level model**

**Example 2**: Repeat Example 1 using the data on the left side of Figure 5.

**Figure 5– Log-log transformation**

The right side of the figure shows the log transformation of the color, quality and price. We next run the regression data analysis tool on the log transformed data, i.e. with range E5:F16 as Input X and range G5:G16 as Input Y. The output is shown in Figure 6.

**Figure 6 – Regression on log-log transformed data**

As in the previous example, we see from Figure 6 that the model is a good fit for the data. We can also use the regression model for forecasting. Note that there are LOGEST or GROWTH functions for the log-log transformed models, but we still have the following two approaches for forecasting:

**Figure 7 – Forecasting using the log-log model**

Hi,

My data contains one dependent variable and 10 independent variables (n=720) from an experimentally designed plot. My data are all positive. The value of dependent variable ranges between 0.17-o.89. Values of independent variables varies:

1) Continuous X1 (20-70%), X2 (0.11-1.4), X3 (3-30%), X4 (9-18), X5 (6-60%), X6 (1-5), X7(5-46), 2) Categorical X8 (4 levels), X9 (3 levels), X10 (5 levels)

I tried model with some variables and found deviation from normality assumption of multiple regression.

Question 1:

What type of transformation is good for my dataset as my indepdent variable contains both continuous and categorical data. It means, for what variables do I need to use log transformation as there are different range and scale of data values?

I tried to fit the model with all independent variables and found 6 of them are not significant. Through model selection, eliminated non significcant variables and finally, got model with all 4 significant variables (two continuous and two categorical). I diagnosed that final model, and found it was not satisfied with linearity, normality, homogeneity of variances. Now I would like to make an appropriate transformation for my model.

Question 2:

When shall I perform transformation, at the begining (before elemination of non significant variables) for the model that contains all the variables or at the end (after elimination of non significant variables) for the model that contains only significant variables?

I would appreciate any suggestions on these two questions. Thank you !

Regards’

Mkc

Mkc,

1. Which transformation to use depends on a number of factors. This is more art than science. See the following webpages for some suggestions:

http://www.real-statistics.com/descriptive-statistics/data-transformations/

http://www.real-statistics.com/correlation/box-cox-transformation/

Also depending on what you are going to do with your data, you may or may not need to satisfy some of the stated assumptions: linearity, normality, homogeneity of variances.

2. You can do this before or after the transformation, but what you shouldn’t do is mix the results. You should one or the other. Based on your comments, you probably want to eliminate variables after making the transformations.

Charles

Good evening

I have a question for my multiple regression analysis. Does someone can help me with that?

I’m predicting the GDP of a country using different factors. I’m planning to use the following model (hightes r square).

log(y) = β0 + β1 * log(Xi1) + β2 * Xi2 + … + βn * Xn + ε; in other words: is it possible to do a log – log transformation without transforming all the independent variables?

Jeroen

Jeroen,

Yes, you can perform such a transformation on some variables, but not others.

Charles

Hello,

I am working on a level-log model. But I wonder how to obtain the related prediction equation from the slope and intersection as AA38 or AA39 in Fig. 7? After tests, the equation y=a+b·ln(x)+c·ln(y) proposed by a reader in the comment was not working. Could you show me the prediction equation from the slope and intersection for a level-log model?

Thank you very much,

James

James,

Since your regression takes the form y = b * ln x + a, you can view this as the simple linear regression y = b * z + a where z = ln x. You can use Excel’s TREND to predict the value of y based on any given value of z. Suppose you want to predict y for the x value x0. All you need to do, is use TREND to predict the value of y when z = ln x0; i.e. first take the log of your x0 value and then use TREND to forecast y.

Charles

Charles,

Finally, I calculated y by y=b0 + b1*ln x1 + b2*ln x2 + b3*ln x3 +b4*ln x4 + b5*ln x5. I got a better fitting from the level-log model than the log-log model. Then I applied the prediction equations of these two models to another data for prediction. Somehow I got many negative numbers in prediction in the level-log model that is very different from the log-log model. The maximum in the level-log model is much smaller than the log-log model. The prediction of the level-log model in new data is worse than the log-log model. I wonder why this result is possible?

Thanks much,

James

James, sorry, but I don’t have enough information to be able to speculate as to why this has happened.

Charles

Charles,

I expected a better model obtained from data A would have a better prediction in data B with the same equation. Do you think this is a correct concept? I got a completely different result so I raised the question.

Thanks,

James

James,

I don’t have enough information to determine this.

Charles

hello,

how to determine the coefficients ( c; b1; b2 and b3) for this regression model: log(Y) = c + b1*log(x1) + b2*log(x2) + b3*log(x3).

thanks

Kossi,

See the following webpage:

Power Regression

Charles

hello,

do you know how to interpret the figures?

my equation using log is:

log(Y) = c + log(x1) +log(x2) +log(x3)

i ran the regression on Eviews but i do not know how to interprete my coefficients. please help. thank you.

Shahanah,

I presume the regression model is log(Y) = c + b1*log(x1) + b2*log(x2) + b3*log(x3)

Taking the exponential of both sides of the equation yields y = e^c * x1^b1 * x2^b2 * x3^b3.

E.g. if you double x1, then the new y will be 2^b1 time previous value of y.

Charles

for the log transformation of time series data ,in excel which function we have to press

ln or log

LN(x) is the natural log of x and LOG(x,b) is the the log of x base b. Note that LN(x) = LOG(x,EXP(1))

Generally the natural log is used, although you could really use log to any base.

Charles

is it possible to apply logs to the regresand and not on all the regressors. because other regressors are negative and a log cannot be negative. for example log(exchange_rate)=B+log(oilprices)+interest_rates.

is the model above correct or not.

Chanda,

Yes, you can do this.

Charles

Hello, Charles.

adding to the previous question. In a Log-log regression if you are applying only to 2 independent variables the logarithm, then how you can read the results.

I mean, the coefficient of the variables with logarithm are in percentages and the coefficient of the variable without the Log are in monetary units?

Martha,

Sorry, but what is the previous question?

Charles

Charles, thank you so much for your knowledge sharing!

Got a question, I have gone over this article and tried to come up with an level-log equation like you did with $T$7*$S$7^W14*$R$7^X14 (Fig 4) for log-level and with EXP($J$51)*EXP($J$52)^LN(W38)*EXP($J$53)^LN(X38) (Fig. 7) for log – log.

Any suggestions would be highly appreciated.

Further info: I am using a linest ((price);ln(color;quality)) type of regression.

Found it!

it is y=a+b·ln(x)+c·ln(y)

Once again thanks for your knowledge sharing Charles

Thanks for this information.

Wondering about your cell references in Figure 7. Is it possible that the references to cells J56, J57, J58 in Figure 7 should actually refer to the coefficients in cells J51, J52, J53 in Figure 6?

Yes, you are correct. Actually, these formulas refer to the exponential of the values in cells J51, J52, J53 of Figure 6.

Thanks very much for catching this mistake. I have now updated the referenced figure to reflect the change.

Charles

Ok, all makes sense now. Thanks!

Sir, what if even after taking log data is not normal…..then how to make data normal?….I m having hard time please let me know. Can i take log of already log series …is it ok for making data normal.

Ruchi,

See the Box-Cox approach on the following webpage

Box-Cox Transformation

Charles

I am using 2 stage least square ans seemingly unrelated regression, where I have 12 independent variables. I am planning to use log value for dependent variable and only two independent variables among the 12 independent variables. It is not fully likes log-log regression. Would you please tell me can I do it and if I can, how I can refer the name of this type of model?

I would appreciate any help on this.

Shampa,

I don’t yet support 2 stage least squares, and so I don’t have any advice about this topic at this time.

Charles

Hello Charles

If there is a zero value in the independent variable, how can we go ahead with the log transformation in the log-log model?

Thanks

Use log(x+a) instead of log(x) where a is a constant big enough so that x+a is always positive (for the values of x that you are considering).

Charles

Can I know the log-log regressions have more statistical sense or business sense?

Angela,

I don’t really know how to answer this question. They have practical application and are an interesting subject in statistics.

Charles

Hello Charles,

I am running two OLS models. Model 1 is a liner model and in model 2 i log my outcome variable y.

When I ran the regression using model 1 it shows that my explanatory variable x has a positive and significant effect on y. Then when I ran the model using the log form (i.e., ln(y)) my explanatory variable becomes negative and insignificant.

I am having a hard time understanding why in model 2, my x variable becomes negative and insignificant? Not sure how what is the correct interpretation here. Any suggestions?

Thank you!

It is difficult for me to answer your question without seeing the data. Perhaps the assumptions for an OLS model are not being met with one of these scenarios. If you send me an Excel file with the data, I will try to see why this is occurring.

Charles

Hi Sir,

Could I ask for a bit of help? What kind of fitting should I use if I have a log-log plot of two independent variables (x and y have been measured with error)?

Thanks so much

Sorry, but I don’t understand your question.

Charles

This post helped me work through issues I was having with a log regression. Love your site, your posts and examples are detailed and easy to implement. Thank you!

Hello SIr, i am implementing a log transfromation on OLS regressioni.e Log transformation on multiple regression. But among the 3 types of log transformations namely log-level,level-log and log-log, which transformation should i go with? Is log-level similar to box cox transformation?

There are many types of transformations in addition to the ones you have referenced. The specific transformation depends on your data. Usually you are picking the transformation that achieves some objective (e.g. making the data more linear or making the data better fit the normal distribution).

The log-linear (i.e. log-level) transformation is one of the transformations in the Box-Cox family of transformations.

Charles

Daniel,

If I only had one independent variable I could do a scatter plot against the dependent variable to visually determine whether the relationship is linear, and if not, whether a transformation (log, ln, 1/x, etc.) is appropriate. but when I have multiple independent variables (say 3 o 4) in a multiple regression, what’s the best way to test for linearity, and what if some are liner and others curves (e.g. exponential)? Thanks

Adeeb,

I tend to simply perform the multiple regression analysis and see if I have a good fit (based on the value of R-square and the significance of the correlation coefficients). You can compare difference transformations in this way as well.

Charles

If I am doing a multivariate regression, but on my left hand side of the equation some of my independent variables have values in thousands which are much higher compared to the others having absolute values in range of, say, 0 to 100, should I use log for all of them or just for the ones with the high values so they can be put on the same level? Also, if some of my variables are in percentages, is it ok if I still apply the log on to them?

Thank you very much!

Daniel,

You can use log for these, but you also might be better off not doing so. The important thing is not that absolute values be on the same scale, but that the assumptions for multiple regression be satisfied (linearity, normality, homogeneity of variances). If using the log contributes to this then using the log can be a good idea, otherwise it is better not to use the log. You can use log for some variables but not others.

Charles

I am trying to build a forecasting model using multiple regression, can you have a look at it and tell me if I am doing it right?.

I would appreciate any help on this.

Regards,

Anil

Anil,

I don’t generally do this sort of thing since it can be very timeconsuming. If you send me your model I will take a quick look, but I won’t be able to try to decipher things.

Charles