Solved

Calculation of distances (via Ms Excel)

Posted on 2014-03-31
11
641 Views
Last Modified: 2014-04-07
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
Comment
Question by:ExpExchHelp
  • 6
  • 4
11 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39968400
Are these for an Excel training course?

mlmcc
0
 
LVL 20

Assisted Solution

by:GrahamMandeno
GrahamMandeno earned 500 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

by:ExpExchHelp
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.

Thank you in advance,
EEH
Calculation-of-Distances.xlsm
0
 
LVL 20

Assisted Solution

by:GrahamMandeno
GrahamMandeno earned 500 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)).

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
 

Author Comment

by:ExpExchHelp
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 500 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 :)

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

All the best,
Graham
0
 

Author Closing Comment

by:ExpExchHelp
ID: 39976395
Graham:

Your solution to the problem is most excellent!!!

Thank you,
EEH
0
 

Author Comment

by:ExpExchHelp
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

by:GrahamMandeno
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

by:ExpExchHelp
ID: 39982808
Graham:

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

EEH
0
 

Author Comment

by:ExpExchHelp
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now