How do I set a cell equivalent to a chart trendline equation?

[doncameron]

Hi,

I have an excel chart that plots a logarithmic trendline based on a table with data that changes according to the month the user has selected. Therefore, every time the user chooses a new month of data to plot, the trendline equation changes. 

The chart plots quantities on the x-axis and unit prices on the y-axis. I want to put the trendline equation into a cell such that I can calculate the unit price based on a user-inputted quantity.

For example, if the trendline equation is y=-0.407ln(x)+5.7609, I want the user to be able to enter an x value (quantity) in one cell and another cell outputs the corresponding y value (unit price). 

I cannot just manually write out this equation in a cell because the chart trendline will change as the chart input data changes by month. I need the equation cell to change as the logarithmic equation changes. Please let me know if you have any suggestions. 

[#2]

https://www.excelforum.com/excel-charting-and-pivots/376923-function-for-logarithmic-trendline.html

I was able to use this to calculate the logarithmic trendline based on the same data creating the plot. However, I hope there is a more straight forward solution.

[#3]

@doncameron  wrote:

I have an excel chart that plots a logarithmic trendline based on a table with data [....] For example, if the trendline equation is y=-0.407ln(x)+5.7609, I want the user to be able to enter an x value (quantity) in one cell and another cell outputs the corresponding y value (unit price). [.... citing another discussion] I hope there is a more straight forward solution.

Quicker and better answers come when you include a concrete example.  Ideally, attach an example Excel file by clicking "browse" at the bottom of a edit window.  If the forum does not permit that (yet), upload the file to a file-sharing website and post the download URL here.  I like box.net/files; others like dropbox.com.  You might like onedrive.live.com because it uses the same login as this forum.  If the forum does permit you to post a URL (yet), spell it out manually.  For example, this thread is at techcommunity dot microsoft dot com /t5/excel/how-do-i-set-a-cell-equivalent-to-a-chart-trendline-equation/m-p/3589034 .

It is unclear what is not "straight-forward" about the solution(s) in the discussion that you cite.  Perhaps it is just the meandering and varied opinions, which I can image might be daunting.

Hopefully, the example in the attached Excel file and image below will be "straight-forward" enough.

Formula:

B3: =ROUND(SLOPE($B$7:$B$11, LN($A$7:$A$11)) * LN(A3) +
        INTERCEPT($B$7:$B$11, LN($A$7:$A$11)), 2)

The user enters the quantity in A3.  The formula in B3 calculates the price, based on the data in A6:A11.

For clarity, I calculate the slope and intercept separated in D7:E7.  The difference in the intercept (5.7594 instead of 5.7609) might be due to the different values of X and Y that I used and/or to insufficient precision in your presentation of the trendline formula.

-----
PS.... Alternatively, we can use LINEST to calculate the slope and intercept.

Select D7:E7 and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=LINEST(B7:B11, LN(A7:A11))

Then the formula in B3 becomes:

=ROUND($D$7*LN(A3) + $E$7,2)