• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 759
  • Last Modified:

Calculation of distances (via Ms Excel)

Experts:

Based on known information of a “triangle”, I need some assistance with calculating distances (through MS-Excel).  

Please see attached Word document that provides more details for the problem(s).    Here's what the documents contains:

Page 1: I'm using MS-Excel to calculate distances (i.e., triangle sides as well as chords).    Problem on page 1 illustrates the process for 2 calculations (1a and 1b; 1c summarizes the values/information).

Page 2:  For problems 2a and 2b, the triangles changed from a "right triangle" to a "non-right triangle".   I'm requesting additional information/guidance as to how I should modify the Excel calculations from [e.g., = 2 * r * SIN (degrees / 2 * PI() / 180)] to ???

That is, the two specific questions I need assistance with are:
Problem 2a-1: What is the length of side B (it should be less than 4.1)?
Problem 2a-2: What is the length of chord (given change from 90 degrees – sides B and C – to 100 degrees)?
Problem 2b-1: What is the length of side B (it should be greater than 4.1)?

If needed, I can provide the XLS that includes the calculations for problem #1.

Thank you in advance,
EEH
Excel-Calculations.docx
0
ExpExchHelp
Asked:
ExpExchHelp
  • 6
  • 4
3 Solutions
 
mlmccCommented:
Are these for an Excel training course?

mlmcc
0
 
GrahamMandenoCommented:
First, I believe your calculation for the length of the chord in #1b is incorrect.  You know:
the radius of the circle, r, and
the length of the chord's perpendicular bisector, b

By Pythagoras, chord = 2 * SQRT( r^2 - b^2)

The formula you have used would work if 70° were the angle subtended by the chord at the centre (between the two radii) but it is not.

Secondly, the side of the triangle, b, can be found using the sine rule:

a/sin(A) = b/sin(B) = c/sin(C)

(where A is the angle opposite side a, etc)

The angle at the centre (70° in your example) is C
The angle on the chord (90° in example #1 and 100° in example #2) is A
Angle B is 180° - A - C

So, by the sine rule:

b = a * sin(B) / sin(A)
   = a * sin(180° - A - C) / sin(A)


This works for both the right-angled and the non-right-angled cases.

I honestly have no idea how you can calculate the length of the chord in the non-right-angled case, since b is no longer the perpendicular bisector.  I will post a follow-up if I think of anything.

Best wishes,
Graham Mandeno
0
 
ExpExchHelpAuthor Commented:
Graham:

Thank you... you provided most excellent information.    And, yes, I realized, you're absolutely correct for the chord calculation (problem 1b).

I'm not sure if I'm fully tracking some of the other information.    

I've taken the liberty to attach the XLS spreadsheet that includes calculations for the first problem.    Please note that the graphs (on that XLS) are not to scale (including the degrees, etc.).   Also, based on your feedback, I've changed the formula in cells C15 and C19.    :)

That said, worksheet #1 includes problem #1.    The 2nd and 3rd worksheets mimic problem 2a and 2b.   All calculations for problem 2a and 2b, however, are still based on problem 1.

Given the information that you provided in previous post, I'd welcome any additional pointers for modifying some of the calculations in support of obtaining the chord distances.

Thank you in advance,
EEH
Calculation-of-Distances.xlsm
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GrahamMandenoCommented:
Hi EEH

I think there is another fundamental flaw in your logic.  The closest point of approach (CPA) is not necessarily on the bearing of 0° (North).  The CPA is in fact the perpendicular bisector of the chord, which simplifies matters a whole lot!  You now have a simple right-angled triangle problem, so the CPA can be found from a sin(B) (or, if you prefer, a cos(C)).

Given your inputs:
Target's bearing (tBearing)
Target's course (tCourse)
Target's speed (tSpeed)
Target's range (tRange)
we can say:
CPA = tRange*ABS(SIN((tBearing-tCourse)*PI()/180))

Everything else pops out from there.  The chord lengths are:
2*SQRT(rWeapon^2-rCPA^2), and

2*SQRT(rSensor^2-rCPA^2), respectively

Best wishes,
Graham
0
 
ExpExchHelpAuthor Commented:
Graham:

Thank you for your continued support (and patience).

You were right when suggesting a modified formula for the CPA.   Thanks for catching that.

Here’s where I currently stand w/ the XLS (see attached):

1.      I’ve modified the CPA formula (problem 1)
2.      I’ve added names to cells (vs. using cell references)
3.      Problem #1 – looks valid to me… can you confirm?
4.      Problem #2a – I’ve provided additional information (bottom left of worksheet).    Based on the info, this is where the change of “right triangle” to “non-right triangle” comes in.    At this time, I’m not confident the XLS formulas (incl. CRT summary) output correct information on distance & time for a) weapon range and b) weapon range + fraction of sensor range.
5.      Problem #2b – again, additional information is provided on the worksheet.   Just like w/ #2a, I’m not confident that the distance/time for sensor range is accurate.

I know you did not suggest that calculations for above #4 and #5 were accurate based on the problem #1a-c.  

That said, do you have any additional advice for calculating/deriving the information for problems 2a and 2b?

Again, thank you for your most excellent help thus far!!
EEH
Calculation-of-Distances-v02.xlsm
0
 
GrahamMandenoCommented:
I'm pretty sure your new calculations of rCPA and the chord lengths are correct, so the original problem is solved, isn't it?

And because of the way these are calculated, the old problems #1 and #2 (course 270° and non-270°) have been reduced to the same problem, because it doesn't matter whether or not the course is a right-angle.

The problem you seem to have now is a totally different one, and that is how to determine the target's course velocity (speed and bearing) from two or more sensor readings.  Assuming a sensor reading gives you both bearing and distance, this should be a straightforward trigonometrical solution.  From two readings you can determine the course bearing and distance travelled, and using the time between them you can determine the speed (assuming an unchanging linear course).

If your sensor reading does not give you both bearing and distance, then you are sunk (excuse the pun :)

Either way, I consider your original question to be answered.

All the best,
Graham
0
 
ExpExchHelpAuthor Commented:
Graham:

Your solution to the problem is most excellent!!!

Thank you,
EEH
0
 
ExpExchHelpAuthor Commented:
Graham:

Thanks for the assistance on the original problem.  

I'll "play" w/ the modified problem (i.e., 2 or more sensor readings).

If I run into additional problems/questions, I'll open a new question (and cross-reference the URLs... if you won't mind).

Again, thank you for your help thus far!  ;)
EEH
0
 
GrahamMandenoCommented:
Hi EEH

You're welcome :)

I'm intrigued though - where are these problems coming from?  Surely not a real military application!

All the best,
Graham
0
 
ExpExchHelpAuthor Commented:
Graham:

It's part of research (combined w/ other topics) -- they are linked to military application.  ;)

EEH
0
 
ExpExchHelpAuthor Commented:
Graham:

I've opened a new thread with related information (as part of the research).

Given your previous help, I was wondering if you might be interested to have a look at this... thank you.

http://www.experts-exchange.com/Other/Math_Science/Q_28406617.html

EEH
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now