×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Computing angular errors in Excel

Computing angular errors in Excel

Computing angular errors in Excel

(OP)
I am trying to create a Excel spreadsheet that will produce
the amount of error between expected and actual measurements. I am entering negative and positive numbers in a degree/minute/second format.

ie.

Expected results      Actual results       Error Amount
________________      ______________       ____________

Horizontal/Vertical   Horizontal/Vertical
-10 deg   / +35 deg   -10:20:10 / 34:54:10

RE: Computing angular errors in Excel

You can use VBA. Do a Split on your input cells, convert the parts to a Double, subtract. Then convert back to D:M:S format.

Here is a quick and dirty sample to get you started. You will of course need to add some error checking to ensure that there are 2 colons in each input cell! This takes first input from A1 <Cells(1, 1)> and C1 <Cells(1, 3)> and posts the result in E1.

CODE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myDegree As Integer
Dim myMinute As Integer
Dim mySecond As Integer
Dim myStart
myStart = Split(Cells(1, 1), ":")
mystartangle = CDbl(myStart(0) + myStart(1) / 60 + myStart(2) / 3600)
Dim myEnd
myEnd = Split(Cells(1, 3), ":")
myendangle = CDbl(myEnd(0) + myEnd(1) / 60 + myEnd(2) / 3600)
mydiff = myendangle - mystartangle
mysign = Sgn(mydiff)
Debug.Print mysign
mydiff = Abs(mydiff)
myDegree = Int(mydiff)
myMinute = Int(60 * (mydiff - myDegree))
mySecond = Int(3600 * (mydiff - myDegree - myMinute / 60) + 0.5)
Cells(1, 5) = IIf(mysign = -1, "-", "") & CStr(myDegree) & ":" & (myMinute) & ":" & (mySecond)
End Sub

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

Steam Engine enthusiasts: www.essexsteam.co.uk

RE: Computing angular errors in Excel

Why do you insist on that type of
degree call out?  What is wrong
with decimal degrees?

RE: Computing angular errors in Excel

It looks like your format is automatically getting set to h:mm:ss.  Use the '@' format to get the value in degrees (10:20:10 = 0.430671).  

Select the cell, <right-click><Format cells><custom><@>.  

If somehow you have a string of '10:20:10' use =value(10:20:10) to convert it back to a number.  
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources