This creates the chart of the ellipse shown in Figure 3, except that the points are not yet connected. We now highlight the range R4:S24 and select Insert > Charts > Scatter. the first such point (shown in cells R4 and S4) are calculated by the formulas Each value in column Q corresponds to a point on the ellipse as shown in columns R and S. The result is that column Q contains the values 0 to 2π in increments of π/10. We then highlight range Q6:Q24 and press Ctrl-D. We now create a chart of the ellipse using a scatter chart of points at regular intervals around the ellipse, as shown in Figure 3.įirst, we create column Q by placing zero in cell Q4, the formula =PI()/10 in cell Q5 and =Q5+Q$5 in cell Q6. Where M4 contains the formula =A4-D$8 and N4 contains =B4-E$8. the first data point is inside the ellipse (cell O4), This is determined by the following formula: The rotation Q matrix in range J11:K12 is calculated by the formula =COS(K6) in cells J11 and K12, =SIN(K6) in cell J13 and =-J13 in cell K11.Ĭolumn O shows whether each of the data point (in A4:B13) are located inside or outside the ellipse. The angle in degrees is shown in cell K8 using the formula =K6*180/PI(). The angle (in radians) the ellipse makes with x-axis is as shown in cell K6, as calculated by the formula =ATAN2(E4,G4-D4). The lengths of the two axes of the ellipse are shown in cells K4 and K5, as calculated by the worksheet formulas =SQRT(G4)*SQRT(H8) and =SQRT(H4)*SQRT(H8). Solving these two simultaneous equations yields the eigenvalues as shown in cells H13 and H14, as calculated by =(H11+SQRT(H11^2-4*H12))/2 and =H11-H13. Alternatively, for a 2 × 2 matrix, they can be calculated using the fact that the sum of the eigenvalues is equal to the trace of the covariance matrix (=D4+E5) and the product of the eigenvalues is equal to the determinant of the matrix (=D4*E5-E4^2). The eigenvalues of the covariance matrix (range G4:H4) can be calculated using the array formula =eVALUES(D4:E5). The mean vector is shown in range D8:E8 where D8 contains the formula =AVERAGE(A4:A13) and E8 contains =AVERAGE(B4:B13). The covariance matrix for the input data is calculated by the formula =COV(A4:B13), as shown in range D4:E5, with inverse shown in range D11:E12 as calculated by =MINVERSE(D4:E5).
95 in cell H9 and the formula =CHISQ.INV(H9,2) in cell H8 (resulting in a chi-square value of 5.99).
To create a 95% confidence interval, we instead place. We begin by showing how to manually create a confidence ellipse when chi-square = 2.25 (cell H8), which is the same as a 67.5% confidence ellipse, as shown in cell H9 which contains the formula =CHISQ.DIST(H8,2,TRUE). ExampleĮxample 1: Create a chart of the 95% confidence ellipse for the data in range A3:B13 of Figure 1. In the case of a bivariate normal distribution, we can create a plot of the confidence ellipse.
0 kommentar(er)
