# Calculating data on chart

1. ## 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

screenshot 1
Screenshot_2.jpg

screenshot 2
Screenshot_3.jpg

2. ## 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.

3. ## 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. ## 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. ## 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

##### Users Browsing this Thread

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

#### 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