Solved

# Calculating length/distance of line within a circle

Posted on 2014-03-24
494 Views
Please see attached document that contains two circles.

I want to be able to compute the length of the red line (for any type of circle and any straight line at various positions inside the circle).

Does anyone know what the formula/function is for calculating the length of the red line(s) inside the circle?

Thanks,
EEH

<<Excel TA added by byundt>>
Computing-distance-of-line-withi.docx
0
Question by:ExpExchHelp
• 16
• 7
• 3
• +2

LVL 2

Expert Comment

ID: 39951306
If the angle subtended by the red line from the centre of the circle is x then the length of the line is 2 x radius x sin(x/2)
0

Author Comment

ID: 39951319
Thank you... my math is a bit rusty.  ;)

Would you be willing to provide me an example (where I can specify different sizes of circle) in MS-Excel?

Thank you in advance,
EEH
0

LVL 74

Expert Comment

ID: 39951339
What information would you know ahead of time? You either have to know the measure of the angle, or you have to know the distance from the center. In either case you need to know the radius.
0

LVL 37

Expert Comment

ID: 39951349
When I open up the document, all I see is two circles (I don't have MS Word on this computer, so I use libre office which may be the problem).
It would be much easier for the experts if you could upload images as an image file type (jpg, bmp, png) instead of a Word document.

What do you know about the red lines? Do you know the angle? Do you know how far the center of the line is from the center of the circle?

Check this link, it should have all the calculations you might need.
http://mathworld.wolfram.com/CircularSegment.html
0

Author Comment

ID: 39951371
kaufmed:

Yes, the radius is known.   I'd like to use Excel for automatically calculating the distance, so purposes of the model, any value for the radius can be entered by the user.

Does that help?   Do you have a suggestion for building this in Excel?

EEH
0

Author Comment

ID: 39951381
TommySzalapski:

Thanks... copy that.   I've attached a JPG w/ two circles.   I need to compute the distance of the red lines (in Excel).   As previously asked by kaufmed, the radius is known.

EEH
Circles.JPG
0

LVL 37

Expert Comment

ID: 39951382
Thanks. I can see that much better.

Yes, you can take any of the formulas from the wolfram link and put them in Excel, but you need to know one more piece of data than the radius (as kaufmed mentioned) Angle or distance from the center of the circle would work.
0

LVL 37

Expert Comment

ID: 39951391
Or to use the terms in the wolfram link, you have R (the radius) you need s, r, h, or theta and you can get all the others (including the one you want, a).
0

Author Comment

ID: 39951490
TommySzalapski:

Yes, angle is also known.     In the Excel solution, both values (angle and radius) can be entered into "input cells".

I'm still trying to figure out how to apply those formulas into Excel in order to compute the distance.   Any help is greatly appreciated.

Thanks,
EEH
0

LVL 2

Assisted Solution

alcindor earned 250 total points
ID: 39951496
See attached spread-sheet, change the radius or angle to see the length of the red line
Book1.xlsx
0

Author Comment

ID: 39951599
alcindor:

Thank you for the worksheet... I appreciate it.

Ok -- hopefully, I'm understanding this correctly.   Please see attached XLS with confirming information.

1. Based on the intersecting object #1 (which is at 90 degrees), wouldn't the distance be twice the radius?

2, If angle is 70 degrees, distance is 7.74 miles.   Just "eye-balling" the distance, I think it should be less though.

3. And if angle is 270 degrees, I end up with a negative value of -1.76.

What am I missing?

EEH
Computation-of-Distance.xlsx
0

LVL 80

Expert Comment

ID: 39951605
Excel works in radians, so alcindor's formula in http:/Q_28396172.html#a39951306 would become:
=2*F2*SIN(F3/2*PI()/180)

In that formula, F2 is the radius and F3 is the angle in degrees subtended by the red line.
0

LVL 80

Accepted Solution

byundt earned 250 total points
ID: 39951630
For the spreadsheet with the compass, the formula would be:
=2*B2*SIN(B3/2*PI()/180)

Note that the angle for intersecting object #1 is 180 degrees, and for intersecting object #2 is 70 degrees.
Computation-of-DistanceQ28396172.xlsx
0

Author Comment

ID: 39951665
byundt:

Thanks for chiming in... appreciate your help.

Out of curiosity, why is the distance only 7.07 miles at 90 degrees?   It's going right through me (i.e., I'm "stationary")... so, wouldn't it be twice the radius (just like at 180 degrees)?

EEH
0

LVL 74

Expert Comment

ID: 39951677
Twice the radius equals the diameter. No two points on a circle can ever be--when drawing a connecting line between them--further apart than the diameter of the circle. Any angle less than 180 deg is going to  result in a chord with a length less than the diameter.
0

LVL 80

Expert Comment

ID: 39951684
ExpExchHelp,
If you look at the workbook I posted, you'll see that I changed the angle to 180 degrees in cell B3 for situation #1, and I got an answer of 10 miles.

If the red line went from 90 degrees (East) to 180 degrees (South) on the compass (i.e. an upward sloping line), the included angle would be 90 degrees and the formula gives the correct length of 7.07 miles.
0

LVL 2

Expert Comment

ID: 39951704
From your compass diagram and using my corrected formula, the angle in the first case is 2 * 90 = 180 degrees or Pi radians which gives an answer of 2 x the radius.

For the second line, the angle is 2 * 70 degrees or pi * 140 / 180 radians which gives an answer of approximately  1.87938 x the radius
0

Author Closing Comment

ID: 39951826
Thank you all for an excellent solution.   I think this covers it... if I end up w/ additional questions, I hope you don't mind my following up in the next couple of days.

Thx,
EEH
0

Author Comment

ID: 39952013
byundt, alcindor:

I realized I do have one follow-up question.

Per attached XLS, I also would like to calculate the distance (delta) between "entering the red zone" and "entering the green zone".    Please see bottom of the XLS (cells w/ red background/white font color).

Is that doable?   If yes, how can the additional distance be calculated?

Thanks,
EEH
Distance.xlsx
0

Author Comment

ID: 39952088
Copy that... thanks, Paul.

EEH
0

LVL 80

Expert Comment

ID: 39952102
ExpExchHelp,
Your latest question has redefined the angle, so the calculated distance doesn't mean what you think it does.

I assume that the sub is not moving--or that you have defined the problem with the motion of the ship relative to the sub.

Do you know the distance between submarine and target? If so you can calculate what you want. If not, you will need to know the included angles (between sub and bottom blue line) as the blue line intersects the red and green circles.

Please advise the data you have at hand.
0

LVL 80

Expert Comment

ID: 39952139
If B2 is the angle between target's path and sub, and B3 is the distance to the target (estimated), then the shortest distance to the target's path will be given by:
=B3*COS(B2*PI()/180)

The red and green chord lengths may be calculated using the Pythagorean theorem:
Green circle chord = 2 SqRt(R green^2 - shortest distance to blue line ^2)
=2*SQRT(B4^2-B11^2)                B4 is the green circle radius, B11 is the shortest distance to target's path

Red circle chord = 2 SqRt(R red^2 - shortest distance to blue line ^2)
=2*SQRT(B6^2-B11^2)                B6 is the red circle radius, B11 is the shortest distance to target's path

The requested distance is half the difference between lengths of red and green chords:
=(B13-B12)/2           B13 is red chord length; B12 is green chord length

DistanceQ28396172.xlsx
0

Author Comment

ID: 39953045
byundt:

Very impressive... thank you so much for helping me out on this, Brad.

I'd like to read up on some of the calculations.   Could you please provide me a pointer to a website which includes more info on these calculations?

Again, thanks!!!

EEH
0

Author Comment

ID: 39953063

I also just noticed the posting before the one that included the modified XLS.

"Do you know the distance between submarine and target? If so you can calculate what you want. If not, you will need to know the included angles (between sub and bottom blue line) as the blue line intersects the red and green circles."

The range to the sub can be estimated.   If yes, would that simplify the calculation?   Would it make a difference?   (Hopefully not).

Again, any pointers to a website that provides more info on the math would be fantastic.  Thanks!  ;)
0

LVL 80

Expert Comment

ID: 39953218
The modified .xlsx file uses an estimated range (distance between sub and target) as part of its calculations.

The math required for these calculations is covered in a high school trigonometry class. If you need a refresher on that subject (or if you never got the chance to take it), you will find a number of tutorials on the web. You need the bits that cover Pythagorean Theorem, chord, sine and cosine. Here is one link: http://www.clarku.edu/~djoyce/trig/ for such a series of tutorials. There are many others, so pick the one that most resonates with you.

Given the files that you posted, you seem like a pretty visual type person. That skill will help you a lot, because you can check the results of calculations against an accurate sketch. When the two agree, you know that you are using the right formulas.

Checking against boundary conditions (the extreme values of the problem) is another useful skill. You were doing that when the suggested formulas weren't matching common sense when you said " It's going right through me (i.e., I'm "stationary")... so, wouldn't it be twice the radius (just like at 180 degrees)?" The correct conclusion to draw from the answer to that question is that the formula must be wrong. And when I suggested a slightly different formula that worked perfectly in that situation, it should have made you feel better about the accuracy of the suggested formula.
0

Author Comment

ID: 39953407

As always, I appreciate your in-depth feedback.    Yes, I'll brush up on the trig.

Final questions (on this post I hope)... would you mind clarifying your labels:

1. Shortest distance to blue line (in nm)= Target Distance cos 70
2. Green circle chord = 2 SqRt(R green^2...
3. Red circle chord = 2 SqRt(R red^2...

Which blue line (ship to submarine distance) or ship's course are they referring to?

Apologies in advance for these fundamental questions.   Just want to make sure I can explain it properly to colleagues.

EEH
0

LVL 80

Expert Comment

ID: 39953449
A chord is a line segment that runs from one side of a circle to the other. Your original question was how to calculate the length of a chord.

You had two blue lines on your diagram. One blue line was the range (distance between sub and target). The range will change its angle and shorten as the target gets closer to the sub. The other blue line was a horizontal blue line representing the path of the target relative to the sub.

To clarify the labels:
1.  Shortest distance to blue line (in nm). The blue line here would be the path of the target. The shortest distance would be a line perpendicular to the path that goes straight to the sub. This is the shortest distance between the path of the target and the sub.

2.  Green circle chord. This is the portion of the target's path that passes from one side of the green circle to the other.

3.  Red circle chord. This is the portion of the target's path that passes from one side of the red circle to the other.
0

Author Comment

ID: 39953453
Excellent -- thank you, Brad!!

Btw, I've posted a follow-on question (dynamically resizing circles for animation purposes).  If there's a solution, I'd like to integrate it into the calculation XLS.

Any ideas on that?
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28396822.html

Cheers,
EEH
0

Author Comment

ID: 39956176

Please forgive me for another follow-on question.    If needed, I'll gladly post a new question and cross-reference the two posts.    Let me know if that's what you prefer.

Anyhow, I've tweaked the model (visually) and included animation of the ranges.

My question though goes back to the calculation for the "1. Shortest distance to blue line (in nm)= Target Distance cos 70" (unless I'm misunderstanding, in Navy terminology, this >> MAYBE << considered "Closest Point of Approach").

If you open the XLS and don't change the values (through data entry) form, the numbers -- when eyeballing them -- make sense to me (and, I'm sure they're correct as well).

Here's what I'm trying to get more clarification on.   Let's say the following is changed:

- Cell C2 changes from 75 to 90 degrees, then C19 = 21.21 nm
- At the same time, the weapon range chord (C15) = 30 nm miles.

Now, although the >> ship's image << did NOT move, I'd visualize it to be positioned to my right (at the 90 degree marker).    So my question, as the ship crosses right above "me", why is the value in C19 <> C30?  Wouldn't it cross through 100% of the weapons range distance (i.e., 30 nm)?

Again, I'm sure the calculations are correct... I just want to be able to explain this demo model w/o "mixing things up".

Thank you again.
EEH
Distance-v02.xlsm
0

Author Comment

ID: 39962493

I've opened up a new thread that expands some of the calculations.

Not sure if you have time or patience to look at this... but in case, the URL is as follows:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28400116.html

Thank you,
EEH
0

Author Comment

ID: 39966918
Experts:

Per Brad's solution (XLS), I have a follow-on question.

That is, if the range (both weapon range radius = 10 nm, why does the following equation (at 90 degrees) -- result in an output of 14.14 nm?
=2*C7*SIN(C3/2*PI()/180.

Again, given the 90 degree relative bearing, the ship would go right above the submarine and thus cross the entire distance of the weapon range (radius of 10 nm * 2 = diameter of 20nm).

So, again, why 14.14 nm vs. 20 nm?

EEH
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Lithium-ion batteries area cornerstone of today's portable electronic devices, and even though they are relied upon heavily, their chemistry and origin are not of common knowledge. This article is about a device on which every smartphone, laptop, an…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

#### 708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!