I have plotted data and applied a trend line to it. I have printed the equation (y=25.64x^0.7526) however when I use this formula to calculate y I am getting the wrong answer.
When I use the formula to calculate, I get values that vastly exceed the expected value. Am I missing something?
I have wanted to get the value of y correct so that I can reverse the calculation to determine x for any given value of y.
MacBook Pro 13″, macOS 12.6
What you did makes perfect logical sense but it doesn't do what it seems like it should do. There are two kinds of charts. There is the scatter chart, which is the only true X-Y chart where X and Y are both numeric, and there are all the others which are "category" charts where the X axis values are text labels. The line chart you created is one of the category charts. Your X axis "numbers" are text. They might as well be A B C D. To make a trend line on one of these charts, Numbers uses 1 2 3 4 and so on as the X axis values.
Delete the chart. Convert the first column to be a regular column (set the number of header columns to 0) because the scatter chart requires the X data to be in a regular column. Create a scatter chart (the chart icon that looks like a chart of a bunch of + signs). Now your trendline will be the true trend line of your numeric data.
What you did makes perfect logical sense but it doesn't do what it seems like it should do. There are two kinds of charts. There is the scatter chart, which is the only true X-Y chart where X and Y are both numeric, and there are all the others which are "category" charts where the X axis values are text labels. The line chart you created is one of the category charts. Your X axis "numbers" are text. They might as well be A B C D. To make a trend line on one of these charts, Numbers uses 1 2 3 4 and so on as the X axis values.
Delete the chart. Convert the first column to be a regular column (set the number of header columns to 0) because the scatter chart requires the X data to be in a regular column. Create a scatter chart (the chart icon that looks like a chart of a bunch of + signs). Now your trendline will be the true trend line of your numeric data.
If you are asking how to calculate the coefficients in a table, it is easy and straight forward to calculate the intercept and slope of a linear trend line using the INTERCEPT and SLOPE functions. For other trendlines, like the power trendline, it requires some additional manipulation. In version 14.4 and higher (where we have array formulas) a power trendline is this:
The multiplier is =EXP(INTERCEPT(LN(B),LN(A)))
The exponent is =SLOPE(LN(B),LN(A))
You can use those directly in your formula(s) or calculate them in separate cells and refer to those cells.
Do not have blank rows. If you have blank rows, you will have to use ranges like A2:A5 and B2:B5 that leave those blank rows out. Your X and Y data all has to be >0.
Polynominal:
Slope =SLOPE(B,LN(A))
Intercept =INTERCEPT(B,LN(A))
Thank you. Changing from a line chart to a scatter plot has certainly made a difference. I will also heed your advice on dynamic calculations, to avoid having to re-enter them each time. Thank you once again.
Calculating x from trend line equation
