I am showing you my answer to this question that came to me from http://www.quora.com. There are other answers to this question that you might also want to look at.
You can transform your data by logarithms and carry out regression in the normal way. For example, you can use
- INTERCEPT() and SLOPE()
- Data Analysis Regression
In my examples, though, I am going to demonstrate using LINEST() using
- X and Ln(Y)
- Ln(X) and Y
My examples are small and simple, my normal approach, otherwise we get bogged down in worrying about hundreds of rows of data, many columns and unwieldy formulas at the end of it all.
LINEST_LNX
In this example I am transforming the X variable by LN(), natural logarithms, since I think that would improve my model and not transforming the Y variable. Look at the screenshot
The data are in the range A1:C5, including headers
the LINEST() formulas are in the range D8:E8 … please note, they are ARRAY entered. You can see how to create the formulas in D9:E9
I then show how to program the results of the LINEST() output in D1:D5 and show the formulas I used in E2:E5
If you have never done this before, take your time and repeat the technique with your own data.
I have also included two graphs and the results of using the Data Analysis Regression routine on the same data: you will see the answers are the same.
LINEST_LNY
In this example I am transforming the Y variable by LN(), natural logarithms, since I think that would improve my model and not transforming the X variable. Look at the screenshot, which seems identical to the LINESY_LNX screenshot but look carefully as there are some differences:
The data are in the range A1:C4, including headers
the LINEST() formulas are in the range D7:E7 … please note, they are ARRAY entered. You can see how to create the formulas in D8:E8
I then show how to program the results of the LINEST() output in D1:D4 and show the formulas I used in E2:E4
If you have never done this before, take your time and repeat the example with your own data.
I have also included two graphs and the results of using the Data Analysis Regression routine on the same data: you will see the answers are the same.
Data Analysis Regression Analysis
I said already that the Data Analysis Regression output is equivalent to the results of the LINEST() output but it contains a lot more supporting information/results. If you need guidance on that aspect let me know and I will run through it with you. Otherwise, this demonstration shows one way of answering your question as I understand it!
Duncan Williamson
18th September 2017
Excel with Excel Master 