Discussion Re: How do I set a cell equivalent to a chart trendline equation? in Excel
https://techcommunity.microsoft.com/t5/excel/how-do-i-set-a-cell-equivalent-to-a-chart-trendline-equation/m-p/3589058#M157222
<A href="https://www.excelforum.com/excel-charting-and-pivots/376923-function-for-logarithmic-trendline.html" target="_blank">https://www.excelforum.com/excel-charting-and-pivots/376923-function-for-logarithmic-trendline.html</A><BR /><BR />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.Tue, 02 Aug 2022 15:46:07 GMTdoncameron2022-08-02T15:46:07ZHow do I set a cell equivalent to a chart trendline equation?
https://techcommunity.microsoft.com/t5/excel/how-do-i-set-a-cell-equivalent-to-a-chart-trendline-equation/m-p/3589034#M157216
<P>Hi,</P><P> </P><P>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. </P><P> </P><P>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.</P><P> </P><P>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). </P><P> </P><P>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. </P>Tue, 02 Aug 2022 15:26:03 GMThttps://techcommunity.microsoft.com/t5/excel/how-do-i-set-a-cell-equivalent-to-a-chart-trendline-equation/m-p/3589034#M157216doncameron2022-08-02T15:26:03ZRe: How do I set a cell equivalent to a chart trendline equation?
https://techcommunity.microsoft.com/t5/excel/how-do-i-set-a-cell-equivalent-to-a-chart-trendline-equation/m-p/3589058#M157222
<A href="https://www.excelforum.com/excel-charting-and-pivots/376923-function-for-logarithmic-trendline.html" target="_blank">https://www.excelforum.com/excel-charting-and-pivots/376923-function-for-logarithmic-trendline.html</A><BR /><BR />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.Tue, 02 Aug 2022 15:46:07 GMThttps://techcommunity.microsoft.com/t5/excel/how-do-i-set-a-cell-equivalent-to-a-chart-trendline-equation/m-p/3589058#M157222doncameron2022-08-02T15:46:07ZRe: How do I set a cell equivalent to a chart trendline equation?
https://techcommunity.microsoft.com/t5/excel/how-do-i-set-a-cell-equivalent-to-a-chart-trendline-equation/m-p/3589213#M157246
<P><LI-USER uid="1420365"></LI-USER> wrote:</P><P><FONT color="#0000FF">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.</FONT></P><P> </P><P>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 <FONT color="#DF0000">techcommunity dot microsoft dot com</FONT> /t5/excel/how-do-i-set-a-cell-equivalent-to-a-chart-trendline-equation/m-p/3589034 .</P><P> </P><P>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.</P><P> </P><P>Hopefully, the example in the attached Excel file and image below will be "straight-forward" enough.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="JoeUser_0-1659460619138.png" style="width: 999px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/392858i57557481EE23401D/image-size/large?v=v2&px=999" role="button" title="JoeUser_0-1659460619138.png" alt="JoeUser_0-1659460619138.png" /></span></P><P>Formula:</P><P>B3: =ROUND(<FONT color="#DF0000">SLOPE($B$7:$B$11, LN($A$7:$A$11))</FONT> * LN(A3) +<BR /> <FONT color="#DF0000">INTERCEPT($B$7:$B$11, LN($A$7:$A$11))</FONT>, 2)</P><P> </P><P> </P><P>The user enters the quantity in A3. The formula in B3 calculates the price, based on the data in A6:A11.</P><P> </P><P>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.</P><P> </P><P>-----<BR />PS.... Alternatively, we can use LINEST to calculate the slope and intercept.</P><P> </P><P>Select D7:E7 and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:</P><P>=LINEST(B7:B11, LN(A7:A11))</P><P> </P><P>Then the formula in B3 becomes:</P><P>=ROUND($D$7*LN(A3) + $E$7,2)</P><P> </P>Sun, 07 Aug 2022 18:31:16 GMThttps://techcommunity.microsoft.com/t5/excel/how-do-i-set-a-cell-equivalent-to-a-chart-trendline-equation/m-p/3589213#M157246Joe User2022-08-07T18:31:16Z