Forum Discussion

doncameron's avatar
doncameron
Copper Contributor
Aug 02, 2022

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

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. 

Charting
excel
Formulas and Functions
Macros and VBA
    • JoeUser2004's avatar
      JoeUser2004
      Bronze Contributor
      Aug 02, 2022

      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)

       

Resources