Extract Excel Scatter Plot with connected by smooth lines Points

[AmazingTrans]

Hi there,

I have a couple of points, and when i do a scatter plot with connected by smooth line plot, it seems like excel does a bezier splines interpolation. I am wondering am i able to extract the data plots from the data chart?
for example if i have
X - Y
0 - 5
180 - 0
360 - 5

i would like to have all datas of Y from X (0 - 360 degree every degree)

Hope someone know how i can do this.

Thanks!

 

[AmazingTrans]

Yes tusharm, you are right, regarding about the higher orders, I was just exaggerating to show that my plot is totally scattered with thousands of points instead of the 2nd order polynomial and my main objective of this post was to find out how to extract x-y plots from a chart that is not in my points.

Thank you evreybody. Looks like the website tusharm gave me has this chart tool that is able to read out the points in between. I will probably investigate the vba code for it.

Thanks for everybody reply.

 

[tusharm]

You are welcome. Do note that while the add-in is currently free to use, the locked VBA code is copyrighted and protected by U.S. and international law.

Yes tusharm, you are right, regarding about the higher orders, I was just exaggerating to show that my plot is totally scattered with thousands of points instead of the 2nd order polynomial and my main objective of this post was to find out how to extract x-y plots from a chart that is not in my points.

Thank you evreybody. Looks like the website tusharm gave me has this chart tool that is able to read out the points in between. I will probably investigate the vba code for it.

Thanks for everybody reply.

 

[Lori_M]

I posted a (rather intricate!) worksheet formula for this a while back:
http://www.mrexcel.com/forum/showthread.php?543636-interpolation-of-a-curve

The xlrotor code on Brian Murphy's site is only an approximate match to the actual smooth line curve, an accurate algorithm is here:
http://answers.microsoft.com/en-us/...00?msgId=5b59b6de-5099-48d1-b205-1b791ea2df05

 

[AmazingTrans]

I posted a (rather intricate!) worksheet formula for this a while back:
http://www.mrexcel.com/forum/showthread.php?543636-interpolation-of-a-curve

The xlrotor code on Brian Murphy's site is only an approximate match to the actual smooth line curve, an accurate algorithm is here:
http://answers.microsoft.com/en-us/...00?msgId=5b59b6de-5099-48d1-b205-1b791ea2df05

Lori,

I am trying to do a basic plot of bezier curve and my curve does not seem to fit all the 4 points by using a bezier curve. It only fits the first point and end point. I guess that is what wikipedia says. If i would like to fit all points, what do i have to do? :
(9,6)
(11,9)
(13,9)
(14,13)

I have used formulas from website such as
p1 + (p2 - p1) * t = q1
p2 + (p3 - p2) * t = q2
p3 + (p4 - p3) * t = q3

q1 + (q2 - q1) * t = r1
q2 + (q3 - q2) * t = r2

p(x) = r1 + (r2 - r1) * t
p = r1 + (r2 - r1) * t

Where
0 < t < 1 : Step of 0.1
p1 = position of point number 1, in this case 9
p2 = position of point number 2, in this case 11

Here I derived all of q1, q2, q3. (Total # of points for each q is 11. Therefore there is 33 points.)
Then i derived all of r1 & r2 (Total # of points for each r is 11. Therefore there is 22 points.
and then p(x), p (Total # of points for each r is 11. Therefore there is 11 points.
I am not sure what is missing here but apparently I am not doing something right here.

Then i tried this formula, which is the same.
http://paulbourke.net/geometry/bezier/bezier1.gif

and I still couldn't get the curve to go through all the points.
http://i.imgur.com/j821S.jpg

Now I am going to try the cadmul-rom code that you have here.
I saw that you have this code which helps to determine the point of y given x. Therefore I can do a small step of x and generate y. but it seems like it only take x = 1 to 19 into account. How do i get points for 21, 21.5,23.5 etc. I have reference the points from this website:

http://www.mrexcel.com/forum/showthread.php?543636-interpolation-of-a-curve

=SUM((1+1/(IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2;1,-3,3,-1},OFFSET(A$2,MATCH(E2,A$2:A$12)-2,,4)-E2))+1E-20
))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},OFFSET(B$2,MATCH(E2,A$2:A$12)-2,,4)))/2

Hope i can get a more detail explanation on how these curves fit in between 2 points, or 10 points and meet every point that is given.
I'm not sure how you derive the matrix for cadmul-rom formula either.
This is the equation that i have, and if we were to use tau as 1, we would get [{0,1,0,0},{-1,0,1,0}, {2,-2,1,-1},{-1,1,-1,1}] instead of [{0,2,0,0},{-1,0,1,0},{2,-5,4,-1},{-1,3,-3 1}].
Do let me know if i misunderstand.

http://imgur.com/ix9jP

Cheers!!!

 

[Lori_M]

First, regarding fitting a curve through all points. One way is to add two extra points to the data set by extending the data at each end - This method is quite common and matches the Excel curve. So for your example data (9,6) (11,9) (13,9) (14,13) add the points at the two ends (7,3) (15,17).

Second, to clarify the worksheet formula method and chart in the linked interpolation post, here's the layout I used:

      --A-- --B-- --C-- --D-- --E--
1       -3*  -23*
2        1     1           x   22.5
3        5    25           y  510.9*
4       10   100
5       20   400               0.25
6       30   900        22.5* 510.9*
7       40* 1400*

The cells marked with an asterisk contain the following formulas...

A1,B1,A7,B7 contain extension formulas listed in the linked post.

E3 contains the interpolation formula as in your post above. The interpolation formula gives the same results as the matrix formula shown below. It takes an x value input and solves the cubic polynomial x(t) for the position parameter t=1/(1+IRR). In the formula, removing the "1/" and using the same matrix in the second line as the first line gives the same results and is clearer but I ended up tweaking this a bit by doing 1/IRR of the column-reversed matrix to avoid problems of 0^0 when evaluated at one of the data points.

D6:E6 contain this array formula ctrl+shift+entered in both cells:

=MMULT(E5^{0,1,2,3},MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},A4:B7))/2

This is equivalent to the matrix equation in your last link with parameter tau=0.5 and position E5=0.25 see also this link http://www.mvps.org/directx/articles/catmull/.

I have to admit it took me a long time to figure this stuff out and I'm sure I still don't fully understand it. Anyway hope this helps - good luck!

 

[AmazingTrans]

Lori,

I also want to populate about 100 points(x-y coordinate) in between each of these points and the points are on the bezier or catmull romm.

For example:
(9,6)---100 points--- (11,9)
(11,9)---100 points--- (13,9)
(13,9) ---100 points---(14,13)

Thanks!

 

[Lori_M]

Just use a similar layout to above but enter the fromula in F2 and fill down for other values as below:

 

[AmazingTrans]

Lori,

Do you have the equation on how the points on y is derived?
I am looking at this website http://www.mvps.org/directx/articles/catmull/
I see they have the same matrix form as what you have, but I am not sure how the q(t) is derived to your equation you have in excel?

I hope to hear from you!

Thank you for the explanations!

 

[Lori_M]

Here's some steps for deriving the formula starting from the matrix equation in your link:

                                  [ 0  2  0  0]   [P0] 
    q[t] = 0.5 * [1, t, t², t³] * [-1  0  1  0] * [P1]     (0<=t<=1)     [1]
                                  [ 2 -5  4 -1]   [P2] 
                                  [-1  3 -3  1]   [P3]

Note this is a parametric equation q(t) = [x(t), y(t)] and we are looking to find a formula for y(x).

(1) First solve q(t) = x which is equivalent to solving q(t) = 0 and shifting each coordinate by x i.e.:

                q(t) = a + bt + ct² + dt³ = 0,             (0<=t<=1)     [2]

                 [a]         [ 0  2  0  0]   [x0 - x]
                  = 0.5 * [-1  0  1  0] * [x1 - x]                    [3]
                 [c]         [ 2 -5  4 -1]   [x2 - x]
                 [d]         [-1  3 -3  1]   [x3 - x]               

In Excel we can make use of the IRR function to solve [2] by setting:

                   t = 1 / (1 + r)

                t(x) = 1 / (1 + IRR({a;b;c;d}) )                         [4]

where the expression for {a;b;c;d} is given by [3]. This equation however does not allow for the case t=0 as that would require IRR to be infinite.

(2) To find an alternative expression for t valid at t=0, we can reverse the order of the points x0,x1,x2,x3 in [3] and solve:

               q'(s) = a' + b's + c's² + d's³ = 0          (0<=s<=1)                            

                   s = 1 / (1 + r)                                       [5]

                   t = 1 - s = r / (1 + r)                 (0<=t<1)

In Excel this is equivalent to reversing the columns in [3] and solving:

                t(x) = 1/(1 + 1/IRR({a';b';c';d'}) )                     [6]

(3) Now substitute [6] back into [1] to solve for y where:

                                   [P0]    [y0]
                  y(x) = q[t(x)],  [P1] =  [y1]            (0<=t<=1)     [7]
                                   [P2]    [y2]
                                   [P3]    [y3]

It's a small step to figure out the formula logic from here noting that when there are more points the OFFSET and MATCH functions are used to find the four point bracketing interval.

 

[AmazingTrans]

Hi Lori,

You've done a great job explaining detail , but I wish you can put some examples in numbers because I do well in numbers then to the code...

1) In equation 1, i believe we are trying to find a,b,c,d?
2) What values is x0 or x? and why is it xi - x?

When you said shifting each coordinate by x; does that mean I pick x0 = 9, x1 = 11, x2 = 13, x3 = 14; And i have x = 0?
There for from this eqn:

                 [a]            [ 0  2  0  0]   [x0 - x]
                  = 0.5 * [-1  0  1  0] * [x1 - x]                  
                 [c]            [ 2 -5  4 -1]   [x2 - x]
                 [d]            [-1  3 -3  1]   [x3 - x]     

I will get

                 [a]            [ 0  2  0  0]    [9 - 0]
                  = 0.5 * [-1  0  1  0] * [11 - 0]                  
                 [c]            [ 2 -5  4 -1]   [13 - 0]
                 [d]            [-1  3 -3  1]   [14 - 0]     

Hope you can help me out on this. I have seen websites giving the blending functions like the one you have, and I believe I am in the right route to understand how to implement other curve once i know how these equations works. I have been looking at blending matrix and i'm not sure how does equations from : http://www.blackpawn.com/texts/splines/ are derived to y(x)...