Exercise:
- Draw a circle centred on the origin!
- PRACTICE: Draw a circle whose centre can be specified!
- PRACTICE: Draw an ellipse!
Prepraration
It is true for all three problems that x and y cannot be expressed from each other, and that several y values can be assigned to one x value and vice versa. To solve the problem, we first need to derive the formulas, which we can then enter into the Excel spreadsheet. The derivation is shown for the circle centred on the origin.
The equation of a circle centred on the origin:
R² = x² + y²,
where R is the radius of the circle. We can observe that the circle can be parametrized by the central angle φ and the distance r between the origin and the point. Thus, we can also describe it in polar coordinate system:
r(φ) = R.
In relation to φ you can now express x and y :
x(φ) = r(φ) ⋅ cos(φ),
y(φ) = r(φ) ⋅ sin(φ), 0 ≤ φ ≤ 2π
Since all points are now specifically the same distance from the origin, r(φ) is a constant and can be replaced by R.
HINT: The above relations apply not only to the circle, but also to any other function that is similarly parameterized by a distance from the origin and an angle. See: parametric mapping of the Cardioid curve.
The steps to solve the exercise:
- adding φ values uniformly between 0 and 2π
- adding r values for each φ (now constant R)
- calculating x and y from r and φ
Exercise 1: Circle centered on the origin
In the solution we need to take a cell for the parameter R, and then we need three columns: φ, x, y. We need to decide how many points we want to display, i.e. how many values of φ we want to add. Let this number now be n = 20.
How can we add values uniformly between 0 and 2π?
Suppose we want to divide the circle into n parts. One solution is to give a number to each point from 0 to (n-1) (thus dividing the circle into exactly n parts). Then express the angle associated with each point by its row number and n. One interval of division means 2π/n angles, so the i-th point will have the angle i⋅2π/n.
- In cell B2, enter "R = " and in cell C2, enter any number (e.g. 2). Rename cell C2 rr.
- The cells B4, C4, D4, E4 contain the headers: i, phi, x, y.
- In cell B5, enter 0, and in cell B6, enter 1. Then select the two cells, and click on the square in the bottom right-hand corner and drag downwards to copy the formula until you get to n-1 (19). For simplicity, let's call the whole column B i: click on the header of column B - this will select the whole column - and then rename it.
- In cell C5, type the formula for phi, then copy down and finally name the column phi:
= @i*2*PI()/20
HINT: Why is the tw parentheses needed in PI()? Because every Excel function should be referred to with two parentheses after the function name. This is a special built-in Excel function that has no input parameters, so we don't write anything in the parentheses.
HINT: Why is @ needed in front of i? Since the new dynamic formulas in Excel, we can use the @ sign to indicate if we only need the range element in the row. Now we want to refer to the value of cell B5 in cell C5, so we need the @.
- Enter the formula for x in cell D5:
= rr*COS(@phi) - Enter the formula for y in cell E5:
= rr*SIN(@phi) - Double-click to copy down both formulas.
All that's left is the illustration. We want to display the points in the x-y coordinate system, so we have calculated the x and y coordinates of each point.
- Select the x and y columns with their headers.
- In the Insert menu, select the "Scatter with straight lines and markers" option from the diagrams.
The diagram has several problems: it is not circular and it is not closed. We can make the diagram proportional by scaling it. Change the height/width until the grid becomes square. Now the x and y divisions are equal. The other problem can be solved by inserting an extra row in the table, i.e. adding a point with the index n. This will have the same angle as point 0, thus closing the circle.
- Scale the diagram to make it proportional so that the function is truly circular.
- At the bottom of column i, in cell B25, enter 20.
- Copy the formulae from the other columns to get the x,y values in row 25 too.
- Format the diagram, set captions, increase the font size to make it easy to read.
Exercise 2: Circle with arbitrary centre (practice exercise)
To solve this problem, another derivation is needed, since so far we have derived the formulae for the circle centred on the origin. General equation of the circle:
R² = (x-xo)² + (y-yo)²,
where (xo,yo) are the coordinates of the centre of the circle.
If we offset each point of the circle by the coordinates of its centre, we again get a circle centered on the origin and the previous relations become true:
x(φ) - xo = r(φ) ⋅ cos(φ),
y(φ) - yo = r(φ) ⋅ sin(φ), 0 ≤ φ ≤ 2π
From here, and using that the radius remains constant:
x(φ) = R ⋅ cos(φ) + xo,
y(φ) = R ⋅ sin(φ) + yo, 0 ≤ φ ≤ 2π
The solution of the problem is similar to the previous one, the difference is that instead of one, there are 3 parameters: R, xo, yo.
HINT: If we want to solve this exercise in the same Excel document as the other one, but on a different tab, we cannot rename the cell corresponding to the radius value to rr again, because that name is already taken. We can't call it R2 either, because it is also taken, pointing to cell R2. You can use an absolute reference instead of a name, or you can call it "radius". Similarly, columns i and phi should be given a new name, or referred to by relative reference.
To double check, we can display the centre of the circle too.
- Right-click on an empty part of the chart, then use the Select data menu to add another data series to the chart.
- For the x values, refer to the cell in which the xo value is written, and for the y values, refer to the cell containing yo.
- Format the diagram, set captions, increase the font size to make it easy to read.
Exercise 3: Ellipse (practice exercise)
To solve the problem, you need the formula for the ellipse. The formula can be found by searching the internet. The steps are similar to the above.