# plotting a circle in a spreadsheet

I can't get my head around plotting a circle on a big sphere (Planet Earth). I have a column that has 180 rows - 1 for each degree. The next row is each degree converted to radians. Easy so far.

A1 is the centre point of the radius latitude

B1 is the centre point of the radius longitude

C1 is the radius required in miles

Column D is each degree so there are 180 rows.

Column E is each degree converted to radians

Column F has (first three rows):

A1+((C1/69)*COS(E1))    latitude for degree 1.
A1+((C1/69)*COS(E2))    latitude for degree 2.
A1+((C1/69)*COS(E3))    latitude for degree 3.


Column G is (first three rows):

B1+(C1/(COS(RADIANS(A1))*69)*SIN(E1))   longitude for degree 1.


After the first 180 degrees, the formula changes as you multiply by -SIN & -COS.

A1+((C1/69)*(-COS(E1)))   latitude for degree 181.
A1+((C1/69)*(-COS(E2)))   latitude for degree 182.
A1+((C1/69)*(-COS(E3)))   latitude for degree 183.


and

B1+(C1/(COS(RADIANS(A1))*69)*(-SIN(E1)))   longitude for degree 181.


I get a wonderful circle in the graph which overlays a map, can be moved and resized by changing A1, B1 & C1 (A1 & B1 use a filterxml-webservice, and it's actually just a postcode I change), and looks very good.

My problem is, have I actually got the math right?

Is it correct to divide the radius required in C1 (e.g. 10 miles) by the distance per degree at the equator (app 69 miles)?

I personally live around 53deg latitude, so for me 1 deg longitude is about 41.5 miles but the spreadsheet is used worldwide.

I've removed the \$ signage for clarity (used in spreadsheets to drag formula down rows & across columns).

I've been asked to give a screenshot. Don't know what good it'll do, as I'm just asking if my sums are right... Two circles are shown with the cluster of members in each (the names are fake - just in case you were wondering) I believe the circles (especially the lower one) are too big The radius is set to 35 miles, but it's looking more like 45. If the circles were correct, there would be 15 more members on the sheet

I came to this site as it was here that I found some calculations to help me at: Calculating longitude length in miles?

The very simplified sheet is here

• can you post a screenshot of spreadsheet? Apr 29, 2018 at 14:12
• I meant screenshot of spreadsheet, altough map also helps. Apr 29, 2018 at 15:07
• It's 20MB big, but here goes.... [link] dropbox.com/s/0178rjv6pn33tx9/FakeMembers.ods?dl=1 Using the Radius and County search in the control shows the "missing members" from the circle, but these members are more than 35 miles away. Apr 29, 2018 at 15:13
• didn't find those columns in the spreadsheet. Apr 29, 2018 at 15:33
• Workings sheet, columns BQ to BZ. BQ is the degrees, BR is the radians. BS & BT are the lat & long for the first postcode, BU & BV for the second postcode, BW & BX for the 3rd, and BY & BZ fo the 4th. I only show 2 postcodes, but you can add two more in the Control sheet. In my explanation, I was just trying to keep things easier to explain with a simpler spreadsheet. Apr 29, 2018 at 15:47