Getting the Value of "a" and "b" from a Trendline Formula , with a formula

[qwertyyy]

I Have a Sheet with a chart showing a trendline and a trendline label. I use the values for "a" and "b" shown in the trendline formula label to calculate further data.

Is there anyway to calculate the values of "a" and "b" in the cells on the spreadsheet from the data automatically. so i dont have to enter them manually in the sheet. See attached example.

Thanks

[MrShorty]

The LINEST() function is probably the most straightforward way to get the trendline constants: https://support.office.com/en-us/art...a-fa7abf772b6d In your case, where you are using ln(x) as the independent variable (rather than straight x), you will need to include the LN() function in the known_x argument =LINEST(known_ys,LN(known_xs))

[qwertyyy]

Thanks iv e got the variable "a" by using =LINEST(D5:D7,LN(C5:C7)) what do I use to get "b"

[MrShorty]

As explained in the "notes" (buried somewhere in the middle) section of the help file, LINEST() is an array function (a function that returns an array of values), and must therefore be entered as an array function. Select the required cells (a horizontal array of 2 cells if you are not returning the additional statistics) and confirm entry of the function using ctrl-shift-enter.

[qwertyyy]

Thanks , but {=LINEST(L54:L56,LN(K54:K56))} gives me "a" just need the formula for "b"

[MrShorty]

Can you explain exactly how you are using the LINEST() function? It should give you both a and b. If it is only giving you a, then I suspect that you selected only one cell when entering the function. The LINEST() function returns an array, so you need to select two adjacent cells (horizontal, not vertical) and array enter the function.

If it helps, this post has an example of LINEST(). It's for a 6th order polynomial rather than a straight line, but you can see how he enters the LINEST() function across the range A2:G2: https://www.excelforum.com/excel-for...ml#post4769062

[qwertyyy]

Got it thanks for that much appreciated