+ Reply to Thread
Results 1 to 12 of 12

Can a cell get data from a chart?

  1. #1
    Registered User
    Join Date
    10-23-2017
    Location
    Boston, MA
    MS-Off Ver
    Excel 365
    Posts
    73

    Can a cell get data from a chart?

    I have a chart created from a bunch of data, using the "Scatter with Smooth Lines" option. Since this is from observed data, the lines have quite a bit of noise in them. I've created trend lines for each of them. I would post an image showing the chart but the forum won't allow me to. So, instead I'll try and explain what the image looks like. The y-axis is in terms of % values, ranging from 0.00% to 50.00%. The x-axis is in terms of calendar dates, ranging from 2/12/2020 to 2/29/2020. There are 7 solid lines that are based on observed data. There are 7 dotted lines matching the colors of the solid lines, and these are trend lines calculated by Excel. Most of them are logarithm functions but a few of them are quadratics because those fit the curves better.

    What I want to do after this is have several cells = to the y value of the trend lines at x = 2/29/2020. I have absolutely no idea how to do this. I can show the function in the trend line options, but that doesn't actually get me what I need. I need the cell data that is getting information from the trend lines to update automatically as I update the source data for the solid lines. Also not really sure how I would convert "2/29/2020" into a value of x that would work in the function, but that's besides the point because I need a fully automated process.

    There has to be some way to do this.
    Last edited by Technetium; 02-28-2020 at 06:49 PM. Reason: solved

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Can a cell get data from a chart?

    Rather than use the chart's trendline for this, I prefer to use the LINEST() worksheet function https://support.office.com/en-us/art...a-fa7abf772b6d The LINEST() function can do any of the trendlines the chart can do (except moving average), but performs the calculation in the spreadsheet, where you can readily access the results with another function.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    10-23-2017
    Location
    Boston, MA
    MS-Off Ver
    Excel 365
    Posts
    73

    Re: Can a cell get data from a chart?

    Hm, seems like using the trend line is still good to have some kind of visualization for what the formula in the cell is doing.

    It looks like there is also a LOGEST, which seems more appropriate since the trend lines I was using are logarithms rather than straight lines. There doesn't seem to be a POLYEST or POLYNOMIALEST. What would I do in that circumstance?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Can a cell get data from a chart?

    LOGEST() performs a linear regression for ln(y)=ln(b)+x*ln(m) [equivalent to y=b*m^x]. If that is indeed a more accurate model for your data, then you may be better served to use the LOGEST() function. LINEST() can perform regressions for any "linear" (as one learns it in linear algebra, so it goes way beyond straight lines) function, and polynomials are linear functions (as are all of the chart trendline types). Towards the end of the remarks section and just before the main example section of the help file is an example for a cubic polynomial, which should easily adapt to any other polynomials.

    And, you are correct, it is nice to visualize the trendline on the chart. I just find that I usually prefer to calculate the trendline in the spreadsheet and add it as an additional data series rather than use the chart trendline feature.

  5. #5
    Registered User
    Join Date
    10-23-2017
    Location
    Boston, MA
    MS-Off Ver
    Excel 365
    Posts
    73

    Re: Can a cell get data from a chart?

    I'll be honest, most of that page flies way over my head.

    I can sorta understand Example 2, as it's the closest to doing the type of process I want (getting a best-fit line, and then finding a y value for an x value outside of the range of sample data). In the formula entered for that example, they use: =SUM(LINEST(B1:B6, A1:A6)*{9,1}). So, two things about this formula I am not sure of: Why is it a SUM? For the part in the {} brackets, what's the meaning of the second value, 1?

    For the other stuff on that page, as well as its rather brief explanation of how to use this for things other than straight lines, I need a process that involves a lot more hand-holding through each step of what is going on, especially since my math skills are pretty rusty in this area. I really have no idea what Examples 3 & 4 are doing, or even what the results that they're calculating mean. I don't want to dump all that on you, so I'll see if I can find some tutorials on youtube for using LINEST.

    Thank you!

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Can a cell get data from a chart?

    tl:dr -- Can you select a horizontal array of 3 cells and array enter =LINEST(known_ys,known_xs^{1,2})? Whether you understand how it works or not, that's all there is to getting a quadratic regression in the spreadsheet.

    The polynomial example is in the Remarks section where they say
    In addition to using LOGEST to calculate statistics for other regression types, you can use LINEST to calculate a range of other regression types by entering functions of the x and y variables as the x and y series for LINEST. For example, the following formula:

    =LINEST(yvalues, xvalues^COLUMN($A:$C))

    works when you have a single column of y-values and a single column of x-values to calculate the cubic (polynomial of order 3) approximation of the form:

    y = m1*x + m2*x^2 + m3*x^3 + b
    . They use the COLUMN() function to return an array of the integers {1,2,3}, one could also use xvalues^{1,2,3} -- or, for a quadratic -- xvalues^{1,2}

    How much of the help file going over your head is spreadsheet programming and how much is the underlying statistics? Examples 4 and 5 are fairly heavily rooted in how a good statistician might use all of the statistics output by the LINEST() function, and would likewise only be fully understood by someone who really understands the statistics behind linear regression. Example 3 is an example of multiple regression (y=m1x1+m2x2+...+mn*xn+b) where you have multiple x values. This is very similar to your polynomial example, where you have multiple "x" values.

    The =SUM(LINEST(B1:B6, A1:A6)*{9,1}) example works like this. LINEST() is an array function, so it returns an array {m,b}. Then Excel multiplies each element of that array with the array {9,1} yielding {9m,1b}. The SUM() function then adds up the elements of the final array 9m+1b.

    Hopefully that helps understand more how to use the LINEST() function.

  7. #7
    Registered User
    Join Date
    10-23-2017
    Location
    Boston, MA
    MS-Off Ver
    Excel 365
    Posts
    73

    Re: Can a cell get data from a chart?

    That was very helpful. I did a test, inputting some x,y values that I knew would be roughly an inverted quadratic. Using LINEST(A1:A10,B1:B10^{1,2}), and then remembering to press CTRL-SHIFT-ENTER, I get a result of coefficients that exactly match the formula shown when creating a chart of scatter points and trendline. When data matches as expected, that's a good thing!

    So my final question... my x-axis is by date. I see when I adjust the bounds of the x-axis on the chart, Excel replaces it with an integer value. It looks like I can use DATEVALUE to do this. If I want to find the y-value for x=2/29/2020, and that date is stored in cell J1, and my sample data is in A1:A6 and B1:B6, then using the method shown before it would be =SUM(LINEST(A1:A6,B1:B6^{1,2})*{DATEVALUE(J1),1}), right?

    If so, this would solve the problems for the quadratics. I still have the ones that are logarithmic to do, but I think having this better understanding of LINEST will probably help a lot there. Thanks again!

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Can a cell get data from a chart?

    =SUM(LINEST(A1:A6,B1:B6^{1,2})*{DATEVALUE(J1),1}), right?
    That will give you y=a*(dateserialnumber)+b -- but will not include c. Recognizing that, for a quadratic, you have 3 elements in the array, you would do =SUM(LINEST(A1:A6,B1:B6^{1,2})*{DATEVALUE(J1)^2,DATEVALUE(J1),1}), right? to get y=ax^2+bx+c. That said, I tend to avoid this kind of "nesting". I prefer to output my coefficients a, b, c to an array of cells, then just refer to those cells. Assuming you entered =LINEST(A1:A6,B1:B6^{1,2}), right? in A10:C10, then I would use =$C$10+$B$10*DATEVALUE(J1)+$A$10*DATEVALUE(J1)^2 -- or use the SERIESSUM() function =SERIESSUM(DATEVALUE(J1),2,-1,$A$10:$C$10) [https://support.office.com/en-us/art...4-96c49087f637 ]. I don't like the SUM(LINEST(...)*{array}) variation, because then every single copy of the formula will repeat the regression. And, while the LINEST() function is fairly fast, I know that a regression requires many calculations. I prefer to avoid forcing Excel to unnecessarily duplicate those calculations. I also tend to want access to the actual parameters a, b, c and not have those parameters hidden inside of a nested function.

    Also, I find that this sort of thing with dates requires some extra care. Date serial numbers (at least for dates in this century) tend to be large numbers, and I find that it takes some thought to make sure I understand what the regression is doing. If you are unfamiliar with how Excel stores dates (and times) as serial numbers, you may want to review something like: http://www.cpearson.com/Excel/datetime.htm

  9. #9
    Registered User
    Join Date
    10-23-2017
    Location
    Boston, MA
    MS-Off Ver
    Excel 365
    Posts
    73

    Re: Can a cell get data from a chart?

    Yeah, I also decided not to use the SUM(LINEST(... approach. I have it output the coefficients and then where I want the result to be, I have it do something like =(V3*(K5^2))+(W3*K5)+X3.

    For linear and quadratic, these seem to be working, meaning that the coefficients are the same as what is shown on the chart trendline formula, and the result for the value of X I give it looks like where the trendline is. For some reason, when I go to cubic, it's not working.

    For the array data for quadratic, I use {=LINEST(C14:C21,B14:B21^{1,2},TRUE,TRUE)}
    This is giving me me coefficients .00033, -28.712, and 629908.9, i.e. y = .00033x^2 -28.712x + 629908.9. This matches what is shown in the trendline formula.

    For the array data for cubic, I use {=LINEST(C14:C21,B14:B21^{1,2,3},TRUE,TRUE)}
    This gives me coefficients 4.97E-9, -.00033, 0, 210017.5, i.e. y = 4.97E-9x^3-.00033x^2 + 210017.5. However, the chart trendline gives a formula of y = 6E-5x^3 - 9.3561x^2 + 36604x - 5E9.

    And when I go to do the quartic upgrade, I get ~0 for coefficients except the y-intercept, indicating a horizontal line. Also, I notice that the R^2 value isn't changing significantly between the quadratic, cubic, and quartic.

    Also, the whole DATEVALUE proved unnecessary. When I refer to a cell that contains a date in a formula like these, Excel just does the conversion automatically.

    EDIT: I resolved the problem above. I think the numbers were too large and Excel was having issues with them. I changed the date to be the number of days since the start-point. Works fine now.
    Last edited by Technetium; 02-28-2020 at 02:10 PM.

  10. #10
    Registered User
    Join Date
    10-23-2017
    Location
    Boston, MA
    MS-Off Ver
    Excel 365
    Posts
    73

    Re: Can a cell get data from a chart?

    Actually, still having an issue with LOGEST. The problem is that my own data is producing an impossible result with LOGEST. I'm gonna paste in my own data in case it helps getting to the bottom of this.
    x values (B14:B21): 29, 44, 50, 52, 54, 55, 57, 58
    y values (C14:C21): 37.71%, 32.56%, 24.66%, 24.55%, 25.14%, 30.42%, 31.24%, 32.65%

    I select a 2x5 cell group and enter {=LOGEST(C14:C21,B14:B21,TRUE,TRUE)}
    This gives the following for the first two rows (into cells AH3 and AI3): 0.991549243 0.451057816

    I want to find the value for x=60. So I have another cell that contains =(AH3*LN(K7))+AI3
    This produces a result of 4.5108, which would be over 450%, and doesn't make any sense given the sample data. The problem seems to be the AH3 value of 0.99155. LN(x) is going to be greater than 100% for any value of x over 3, and so if I am expecting a value under 100% then the number I multiply LN(60) by needs to be much smaller than 0.99155.

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Can a cell get data from a chart?

    LOGEST() returns m and b (and other statistics) for the equation ln(y)=ln(b)+x*ln(m) or y=b*m^x. Using those values for m and b:

    y=b*m^x=.451*.992^60=0.271 is correct
    ln(y)=ln(b)+xln(m)=ln(.451)+60*ln(.992)=-.796-60*.00849=-1.305=ln(0.271)

    Either way, I get 0.271 for y at 60. I'm not sure where you got the y=m*ln(x)+b equation, but that is a different regression.

  12. #12
    Registered User
    Join Date
    10-23-2017
    Location
    Boston, MA
    MS-Off Ver
    Excel 365
    Posts
    73

    Re: Can a cell get data from a chart?

    Ah, I assumed it was m*ln(x)+b because that appears to be what it uses when you choose logarithmic in the trend line. Thanks again. Seems much more reasonable now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pie chart to display single cell data
    By andusbeckus in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-29-2016, 02:28 PM
  2. Using cell references to construct data ranges in chart data selection
    By rmalliston in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-13-2016, 08:48 PM
  3. Replies: 2
    Last Post: 10-16-2014, 08:57 PM
  4. Chart changing data from one cell?
    By sonnyzhang in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-07-2014, 01:47 AM
  5. Use cell content for chart source data
    By scarr in forum Excel General
    Replies: 2
    Last Post: 03-15-2012, 07:31 AM
  6. Chart data based on cell reference
    By scottjn in forum Excel General
    Replies: 2
    Last Post: 04-21-2010, 07:34 PM
  7. Using contents of cell as source data for chart
    By cpenticuff in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-17-2009, 10:24 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1