How to graph an ellipse on a scatter plot by VBA

[Landcruiser87]

Helloooo to the masses of fellow coders,

After hours of pouring over the world wide web looking for sample code that might be able to accomplish the title of this post, i've come up empty handed.

What i'm attempting is to create a vba routine that can take a subset of data
Ex:

X Y
29.70 264.00
25.50 269.00
27.90 265.00
37.20 269.00
54.90 262.00
41.10 270.00


And have a vba routine find its centroid, draw a std ellipse, then rotate it 360° to find the minimum area and thus find the angle at which the ellipse best represents the weighting of the data.

I'd like to use matrices and arrays in VBA, but am having trouble finding any examples to work off. So i pose the question to you my friends, Have any of your tried this before? Help this struggling scientist figure out something that seems easy but has thrown up more road blocks than the 405N at rush hour. ( i live in cali )

Thanks !

 

[JackBean]

Your title is incomplete.
You want to recreate an ellipse with a small subset of the points.
With 4 points, making a rectangle, there is a whole family of ellipse that will incorporate the points.
So how many points are needed?
It would seem at least 5.
And how would you do this?
By solving a system of equations?

There is no way that an ellipse can fit your sample points.
Did you try plotting them on a scatter plot?

Example in parametric form:
Major axis is 2x5=10
Minor axis is 2x2=4
Rotated counterclockwise 45 degrees
In parametric form; allows rotation (Phi in radians); allows offset from origin (Xc and Yc):

Excel Workbook
	A	B	C
1	Parametric	0.012566
2	Phi	0.785398
3	Xc	1
4	Yc	2
5	a	5
6	b	2
7	X	Y	t
8	4.535534	5.535534	0
9	4.517484	5.553026	0.012566
10	4.498878	5.569957	0.025133
11	4.47972	5.586324	0.037699
12	4.460012	5.602125	0.050265
13	4.439758	5.617357	0.062832
14	4.418961	5.632017	0.075398
15	4.397624	5.646105	0.087965
16	4.37575	5.659616	0.100531
17	4.353343	5.67255	0.113097
18	4.330407	5.684903	0.125664
19	4.306945	5.696675	0.13823
20	4.282961	5.707863	0.150796
21	4.258458	5.718465	0.163363
22	4.233441	5.728481	0.175929
23	4.207913	5.737907	0.188496
24	4.181878	5.746743	0.201062
25	4.155341	5.754988	0.213628
26	4.128306	5.76264	0.226195
27	4.100777	5.769697	0.238761
28	4.072758	5.776159	0.251327
29	4.044254	5.782025	0.263894
30	4.015269	5.787294	0.27646
...

Cell Formulas
Range	Formula
B1	=PI()*2/500
B2	=PI()/4

Copy down through Row 508:

Cell Formulas
Range	Formula
A8	=B$3+B$5*COS(C8)*COS(B$2)-B$6*SIN(C8)*SIN(B$2)
B8	=B$4+B$5*COS(C8)*SIN(B$2)+B$6*SIN(C8)*COS(B$2)

Copy down through Row 508:
Cell C9:
=C8+B$1
Example Ellipse (rotated and not centered on origin):


The first thing you need to do is figure out how you will construct the ellipse from the points.
This can always be converted to VBA Code.

Here is a reference to plotting an ellipse, without rotation of the major axis from the horizontal:
Ellipse in a chart
http://www.ozgrid.com/forum/showthread.php?t=46470

 

[JackBean]

Reconstruction Of Ellipse From Four Points

This is for the simple case where the major axis of the ellipse is horizontal.
It takes only 4 points here to reconstruct the ellipse.
It may be that more than 4 points are needed if the first 4 make a rectangle.

Excel's Solver will solve this, but not consistently.
It depends on the starting conditions.

For the same ellipse as previously, except that Phi = 0, take 4 points:

In Solver:
Set Target Cell = $F$2
The Target Cell should be minimized.
Changing Cells = $F$3:$F$6

Constraints:
$F$5 >= 0
$F$6 >= 0
$G$9 = 1
$G$10 = 1
$G$11 = 1
$G$12 = 1

Cell Formulas
Range	Formula
F2	=SUM(G8:G11)

Copy Down Four Cells:

Cell Formulas
Range	Formula
G8	=(E8-F$3)^2/F$5^2+(F8-F$4)^2/F$6^2

Before running Solver:

Excel Workbook
	A	B	C	D	E	F	G
1	Parametric	0.012566
2	Phi	0			Sum	14.37605
3	Xc	1			Xc	2.5
4	Yc	2			Yc	0.75
5	a	5			a	6
6	b	2			b	1
7	X	Y	t		X	Y	Equation
8	2.782059	3.868658	0		2.782059	3.868658	9.728237
9	-3.96057	2.250666	0.012566		-3.96057	2.250666	3.411417
10	-2.13846	0.443075	0.025133		-2.13846	0.443075	0.691849
11	1.751128	0.022697	0.037699		1.751128	0.022697	0.544548
...

After running Solver:

Excel Workbook
	A	B	C	D	E	F	G
1	Parametric	0.012566
2	Phi	0			Sum	4
3	Xc	1			Xc	1.000001
4	Yc	2			Yc	2
5	a	5			a	5.000001
6	b	2			b	2
7	X	Y	t		X	Y	Equation
8	2.782059	3.868658	0		2.782059	3.868658	1
9	-3.96057	2.250666	0.012566		-3.96057	2.250666	1
10	-2.13846	0.443075	0.025133		-2.13846	0.443075	1
11	1.751128	0.022697	0.037699		1.751128	0.022697	1
...

To get this to work in Excel, at any angle, with VBA Code, and the other things you want it to do, it looks like you have your work cut out for you.
As your search confirms, it is not likely that custom-made solutions are always available.
This has been done, but it may not be readily available, done with the program that you want, or do exactly what you want.

Here is a reference to a Matlab Question and Answer:
Thread Subject: SOLVE 4 NONLINEAR EQUATIONS (HELP PLEASE)
http://www.mathworks.fr/matlabcentral/newsreader/view_thread/280862

Ellipse; 4 points (pink)

 

[Landcruiser87]

JackBean,

Thanks for all the great info on your posts! You're right, i'm trying to draw an ellipse with a minimal amount of points, but i'm not going to restrict myself to only those little points in my graphing. I'm working on the subroutine to build the matrix array as of now and predict an ellipse of at least 70 or 80 pts that would include the smaller points. Your last link to the matlab question i think is going to be the key to save the day.

I'll post back with progress as i'm just trying to figure out the best way to attack the problem. Thanks again for your help,

Andy

 

[droaelis]

This is great stuff!
I used the parametric with rotation form and all works smoothly. But I have an additional question. Anyone an idea on how to determine the focus points of your drawn ellipse?

 

[das123]

Hi,

I am also trying to fit a tilted ellipse which also has small no zero Xc,Yc. I have about 30 points. The problem I am facing is the equation:

(X-Xc)^2+(Y-Yc)^2= a^2*cos^2(t)+b^2*(sin^2(t)
=> (X-Xc)^2+(Y-Yc)^ - a^2*cos^2(t) - b^2*(sin^2(t)=0

As the tilt is also to be determined,(phi is to be determined), for fitting purpose I put the parameter t=Phi and got a solution.

As you have already done this, I thought I will ask you.

Thanks