Part
01
of one
Part
01
Hello, I have a dataseries in excel in cells C7: which produces an exponential regression formula that is y=C1e^C2x where C1 and C2 are constants. We can calculate C1 by taking =INDEX(LOGEST(C7:C22,B7:B22,TRUE,TRUE),2). Can somebody please explain to me how I can build a formula that calculates C2, the R-square and the regression line that will allow me to project 2019 in the attached example? Thank you very much, Oscar
Hello, and thank you for your question about projecting your formula to 2019. The short version is that I have provided a formula which projects through 2032, as well as some information which may prove useful in the future. Below you will find an overview of my methodology and findings, with the full calculations available in the spreadsheet.
OVERVIEW
I have included my calculations below the bolded line in the spreadsheet. The most useful information will likely be the "new y" column—cells I34-I50—which includes the value of .8 (.7789...) for year 2019 in cell I37. I used the GROWTH function to calculate the values in the "new y" column, as can be seen in cell I35. I then provided an updated graph including years 2001 to 2032 based on the output of the GROWTH function.
Unfortunately I was unable to calculate C1 and C2 based on the information provided. There seems to have been some difficulty with the date formatting, with the results visible in cells J34-J50. Based on that, for most of my calculations I have used an adjusted x-value: starting with 2001 as "1," I assigned each number a simple numeric value. Based on the information in cells M35 and N35 and the test I conducted to see if the numerical x-values gave the same results as the initial data—cells T34:V50 show the results, with values pulled out as needed to show that the only differences are due to rounding—this seems to be a valid adjustment. Another solution, seen in cells B89:B121, is to format a year value as a date, which will inform how Excel uses that data in a formula.
Even with the adjustments, the results given by the LOGEST formula did not yield numbers that would generate viable results for calculation of the underlying function. An example can be seen in cells M37:O44 of the formulas used and numbers generated, including the output for a negative value for C2.
The R-squared statistic is available in conjunction with the graphs, and is a measure of how well the regression line fits the available data—in this case it has a value of 1 representing a perfect fit.
I have provided notes within the spreadsheet for ease of reference as to where different numbers came from or how they were found. These notes supplement the information available within the formulas used about the cells they reference to pull data for calculations. To avoid rounding errors, I used cell references rather than typing in numbers for any number extending farther than four decimal places.
CONCLUSION
To wrap it up, I have extended the calculation in the spreadsheet through 2032, finding a value for 2019 of approximately .8. I was unable to fully reverse and reconstruct the given formula, but I have provided resources and context for future reference.
Thanks for using Wonder! Please let us know if we can help with anything else!