I’ve received a couple of trig questions lately. I liked trig in high school and college, but I can’t say I put it to use much. There’s only one thing that you need to know about Excel and trigonometric functions: Everything is in radians, not degrees. If you do anything trig-related in Excel, you have to convert to degrees (unless you want radians, of course).
Here’s one problem:
In this example, Peter knows a fixed point on a plot of land. He also knows the coordinates were the corner of a building will be when it’s built. He wants to figure the angle and distance from the known point to the corner of the building so he can point his theodolite at it.
I had to look up theodolite, and it turns out I’ve been calling it a transit all these years.
Finding the distance is easy, thanks to old Pythagoras and his right triangle formula, c2 = a2 + b2. The formula in B4 is: =SQRT((B1^2)+(B2^2)). That one I didn’t have to look up. The rest of of the trig functions weren’t so quick to return to me.
Next I needed the angle. Cell B5 is =DEGREES(ATAN(B2/B1)). Note that I didn’t forget to convert to degrees, since the ATAN function would return radians. To refresh my memory, I used this Trigonometric Formula page.
Finally, I turned that fractional angle into minutes and seconds, thusly:
B6: =TRUNC(B5)
B7: =TRUNC((B5-B6)*60)
B8: =ROUND((B5-B6-(B7/60))*60,0)
Now Peter can point his theodolite (or whatever you do with those things) at 75° 57′ 1″ and 6,185mm away will be the start of the new building.
I frequently translate between latitude-longitude and UTM coordinates. Many USGS data sources report a degree of latitude or longitude as a single value, e.g. 394013 is 39 degrees 40 minutes and 13 seconds. The custom format, 00°00’00” makes 394013 appear 39°40’13” It is easier to read and does not interfer with fractional seconds. If you adopt this convention,
Cell B6:=INT(B5)*10000+INT((B5-INT(B5))*60)*100+MOD((B5-INT(B5))*3600,60)
*ALWAYS* better to use ATAN2 rather than ATAN since angles that are odd-integer multiples of 90 degrees (or PI()/2 radians) do occur, but ATAN would choke on its #DIV/0! argument.
No need for more than one formula.
=TEXT(DEGREES(ATAN2(B1,B2))/24,”[hh]°mm’ss”””)
BTW, you fubarred the B8 formula, which should be
=ROUND(((B5-B6)*60-B7)*60,0)
since the actual angle in your example is 75°57’49.52?. Guess Peter will have to tear down and start again. You do have E&O insurance, don’t you?
To simplify Pythagoras’ theorem in Excel, use the SumSquare Function: B4 =SQRT(SUMSQ(B1,B2))
fzz: Thanks. Never heard of ATAN2. My liability for Excel help is limited to the amount I was paid. :)
Charles: Never heard of SUMSQ (that makes two function in one comment). What is that function used for? The only time I’ve summed the squares is solving right triangles, and it seems like overkill if that’s its only use.
I can see using SumSq if you wanted to figure out variance by hand…
RMS averaging in the electronics industry would use a SumSq formula.
P.S. You talked several months back about making a post on handling keyboard input from a userform, did you ever get a chance to look at that?
I don’t remember keyboard input on a userform, but it sounds like something I’d say. Remind me what we were talking about and I’ll put it on the list. Thanks.
Dick, here’s the link to the thread on your site.
http://www.dailydoseofexcel.com/archives/2006/12/20/pseudo-scroll-2/#comments
I am looking for a comprehensive book on functions. Excel “HELP” is not! I would like to find a book that states the function, the syntax, some uses, and some examples. HELP has some very basic ones, but it seems every time I read OfficeUsers.org I read about another function I have never heard of. Does anyone out there know of a book like that? Or, maybe a website?
http://www.dailydoseofexcel.com/daily-dose-of-excel-book-list/
Get Excel 2007 Formulas. It’s full of practical examples.
I still occasionally use my ‘Excel 5 Worksheet Function Reference’, probably about the last paper documentation Microsoft included with Excel.
please want formal of vb coding
(SQRT(SUMSQ(((E5-E8)*3600*30.9206),(((E6-E9)
*3600*30.81512)*COS(19*PI()/180)))))/1000
karthikeayn:
You can figure this out for yourself. It’s not difficult.
Search XL VBA help for ‘square root’ (w/o the quotes) and for ‘cos’ to find the VBA functions that correspond to those Excel functions. For most Excel functions that do not have a VBA equivalent, you can use the WorksheetFunction method as in Application.WorksheetFunction.SumSq(…) For more look up XL VBA help for ‘sumsq’ or ‘pi’
I am trying to use excel to calculate the following, but do not know how to write the formula:
For all integers m and n, if 2m + n is odd then ma and n are both odd.
I need to provide a counterexample that shows this is not an accurate statement.
What I was thinking of doing was creating a quick formula for =2*odd number + odd number and dragging it until the answer was even, which would give me the counter example.
This seems so simple, but I cannot figure it out. Help!
Whether m is even or odd, 2m is even. 2m + n is even where n is even and 2m + n is odd where n is odd. I don’t know what ma is in your statement; I suppose it’s a typo. If 2m + n is odd, all you know about m and n is that n is odd.
how write 10°50’59” in excel sheet
Enter it as 10:50:59 (as a time), and use a custom format of hh°mm’ss”
Anyone wanting to follow the excellent advice of fzz, and use ATan2 in VBA, rather than ATan, look here:
http://newtonexcelbach.wordpress.com/
Also why to write your own function, rather than use worksheetfunction.
I’m just curious as to where the X and Y come from, if he started out with theodolite H and V angles?
Carmen – I’m not sure I follow the question, but you use ATan2 (or ATan) if you have x,y coordinates for two points and you want to find the angle of the line connecting them. If you are starting with angles and a distance you use Tan, Sin, or Cos to find x,y coordinates.