+ Reply to Thread
Results 1 to 5 of 5

Calculating data on chart

  1. #1
    Registered User
    Join Date
    05-12-2021
    Location
    Valjevo
    MS-Off Ver
    2019
    Posts
    5

    Calculating data on chart

    Hello everyone,

    This question is more about math then a chart one but it uses data from chart

    In the given chart (Screenshot 1) i have x values in log (screenshot 2 black numbers) and y values (screenshot 2 red numbers). Given numbers form a curve on the chart.

    I'm required to find the value where the green lines intersects the blue dotted lines on the chart (top row x values, 0.002, 0.0063, 0.02, 0.2 etc)

    example, the value at blue dotted line 0,0063 is 87 (reading from the left) or 13 reading from the right)

    I would like to create a formula on that will calculate the value at intersections of the green data line and blue chart line

    Example, using values from screenshot 2: 0.09 - 96.17416 and 0.25-98.59551 to calculate the value at 0.2 point (blue doted line)
    Function forecast linear and even forecast won't work on this one

    Thanks in advance.

    screenshot 1
    Screenshot_2.jpg

    screenshot 2
    Screenshot_3.jpg

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

    Re: Calculating data on chart

    There are usually two generic approaches to this kind of problem:

    1) A regression approach where you choose an equation that can represent the data, then use one of the many regression techniques to obtain the adjustable parameters for your chosen equation, then use that equation to solve the problem.
    2) An interpolation approach where you assume that the tabulated points are exact, and use one of the many interpolation algorithms out there to find values in between the tabulated points.

    Looking at the shape of the curve, it seems that interpolation would be the preferred approach, but I cannot choose that for you. If interpolation is the best approach, are you limited to Excel only? Unfortunately, Excel does not have a built in interpolation function, but Gnumeric (Linux only) does, and it makes short work of these kind of problems using either a linear interpolation or a spline interpolation. If you are limited to Excel, I prefer an approach like this: https://www.excelforum.com/excel-cha...ml#post3904113 Note that this particular example uses linear interpolation in a log-log space. Your chart appears to be linear on the Y axis and logarithmic along the x axis, so I would probably choose to interpolate in semilog space.

    You have some choices to make, then see how you get a along, then let us know where you get stuck.
    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
    05-12-2021
    Location
    Valjevo
    MS-Off Ver
    2019
    Posts
    5

    Re: Calculating data on chart

    I'm limited to excel for this one and yes, x axis is log and y is linear

    Gotta be honest, i didn't quite get your suggestion, but i did look at the thread you linked, didn't quite get the job done.

    I tried to make makeshift triangles between my data sets so i can calculate c and angle to a known value at 0.002 x axis , that value should read 20, or 80, depending if you're reading from right or left

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

    Re: Calculating data on chart

    I'm not sure what trouble you had with the interpolation algorithm I suggested. Here's what I would have expected:

    1) This is going to work a lot better with the data sorted by x from high to low (mostly because I usually use the VLOOKUP() function here. If you use INDEX(...MATCH()) or XLOOKUP(), you can leave the data sorted in descending order with the proper options. I find VLOOKUP() is easier when explaining the overall algorithm.
    2) Add a helper column that calculates LOG(x). =LOG(A1) or LN(A1) [depending on your preference of common logarithm or natural logarithm]. copy/paste/fill down.
    3) As explained in the link, use LINEST() or SLOPE() or INTERCEPT() to get slope and intercept for each pair of points. =LINEST(B1:B2,C1:C2,TRUE) [assuming the logarithm of x is in column C] in D1:E1.
    4) Where you are trying to calculate your intercept points, it should now be as simple a couple of lookups to get slope and intercept for your desired interval inside of a basic y=mx+b formula. y=10^(LOG(x)*VLOOKUP(x,$A$1:$E$12,4,TRUE)+VLOOKUP(x,$A$1:$E$12,5,TRUE)). Note that I'm using TRUE for the 4th argument in VLOOKUP(), specifying an approximate match binary search that requires the left column's x values to be sorted in ascending order. This help file explains the behavior of TRUE and FALSE in the 4th argument: https://www.ablebits.com/office-addi...ximate-vlookup

    That should give you a good semi-log space linear interpolation. Does that work, or do you need a different algorithm?

  5. #5
    Registered User
    Join Date
    05-12-2021
    Location
    Valjevo
    MS-Off Ver
    2019
    Posts
    5

    Re: Calculating data on chart

    I'm so happy to tell you that this solution worked but with 1 fix

    i've erased 10^ from the y formula, i don't know why but that was the thing that was giving me trouble.

    now when i enter the desired intersection value for x axis in F column, G column (y formula is in g column) gives me the exact number

    I've tested this on 10 charts and its working perfectly

+ 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. [SOLVED] calculating specific data from a pivot table to create a pivot chart in Excel 2016
    By cinstanl in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-15-2016, 12:14 PM
  2. Calculating a total price based on variables in a separate chart
    By Pvals in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-22-2013, 01:58 PM
  3. Calculating Data for a Chart
    By bremen22 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 07-13-2013, 05:54 PM
  4. Replies: 1
    Last Post: 12-20-2010, 04:46 PM
  5. Replies: 4
    Last Post: 11-04-2008, 01:28 PM
  6. Help with calculating a chart
    By WebJunkie in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 05-28-2008, 03:40 PM
  7. How To Refresh Chart Data Without Calculating The Worksheet
    By Carl Bowman in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 01-19-2005, 06:06 PM

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