[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Calculation of distances (via Ms Excel)

Posted on 2014-03-31
Medium Priority
745 Views
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.

EEH
Excel-Calculations.docx
0
Question by:ExpExchHelp
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 4

LVL 101

Expert Comment

ID: 39968400
Are these for an Excel training course?

mlmcc
0

LVL 20

Assisted Solution

GrahamMandeno earned 2000 total points
ID: 39968466
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

Author Comment

ID: 39969143
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.

EEH
Calculation-of-Distances.xlsm
0

LVL 20

Assisted Solution

GrahamMandeno earned 2000 total points
ID: 39971160
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)).

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

Author Comment

ID: 39972288
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

LVL 20

Accepted Solution

GrahamMandeno earned 2000 total points
ID: 39975852
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 :)

All the best,
Graham
0

Author Closing Comment

ID: 39976395
Graham:

Your solution to the problem is most excellent!!!

Thank you,
EEH
0

Author Comment

ID: 39976405
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

LVL 20

Expert Comment

ID: 39981098
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

Author Comment

ID: 39982808
Graham:

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

EEH
0

Author Comment

ID: 39983065
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month13 days, 15 hours left to enroll