+ Reply to Thread
Results 1 to 7 of 7

Problem with ACOS(###) returning #NUM! error

  1. 08-27-2008, 03:52 AM #1
    andydao
    andydao is offline
    Registered User
    Join Date
    08-27-2008
    Location
    san jose
    Posts
    4

    Problem with ACOS(###) returning #NUM! error

    I am trying to calculate angle changes from GPS coordinates. I have three points and am using the law of cosines to find the angle. I calculate the distance between the GPS points using the distance formula:

    Please Login or Register  to view this content.
    The points of interest are (B3,D3) (B4,D4) and (B5,D5). There are two examples in the attachment, the first where the formula works and it returns a number, and the second where it returns the #NUM! error. When I click "Show Calculation steps," it says that ACOS(-1) is going to result in a calculation error.

    I have tried calculating the number inside ACOS(_) into another cell. The result is -1. I have tried taking the ACOS(_) of this new cell but it results in a #NUM! error. I have also tried taking the ACOS(_) of a cell into which I paste special the value, and it results in a #NUM! error as well.

    Inputting =ACOS(-1) in a cell results in a numerical answer and no error

    My questions are this:

    Is the formula I am using incorrect and therefore creating some numberical errors? How can I fix this?

    My goal is to calculate angle changes for hundreds of points as a car goes along a route.
    Attached Files Attached Files
    Last edited by VBA Noob; 08-27-2008 at 04:22 AM.
    Reply With QuoteRegister To Reply
  2. 08-27-2008, 08:56 PM #2
    andydao
    andydao is offline
    Registered User
    Join Date
    08-27-2008
    Location
    san jose
    Posts
    4
    bump. no responses so far.

    I have also looked through formatting the cell, but none of the options returned anything other than the error.
    Reply With QuoteRegister To Reply
  3. 08-27-2008, 11:27 PM #3
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I think the value returned is slightly < -1 due to accumulated error, and you need a different formulation. These kinds of problems are kind of common in geographic problems with inverse trig.

    Are you just looking for the change in heading over two segments? (Not that a different formulation will be trivial.)
    Reply With QuoteRegister To Reply
  4. 08-28-2008, 12:34 AM #4
    andydao
    andydao is offline
    Registered User
    Join Date
    08-27-2008
    Location
    san jose
    Posts
    4
    I think I realized the problem. It's because these points are all on a straight line, and my formula can't calculate it.

    How can I modify this formula so that it will just return a value of 0 for this type of case?
    Reply With QuoteRegister To Reply
  5. 08-28-2008, 12:35 AM #5
    andydao
    andydao is offline
    Registered User
    Join Date
    08-27-2008
    Location
    san jose
    Posts
    4
    Sorry, yes I am just looking for a change of heading over two segments. There are a few hundred segments I'm calculating heading change for segment by segment. Do you have a different formulation that would work better?
    Reply With QuoteRegister To Reply
  6. 11-26-2014, 07:10 PM #6
    gatsby137
    gatsby137 is offline
    Registered User
    Join Date
    11-26-2014
    Location
    Batavia, IL
    MS-Off Ver
    2010
    Posts
    1

    Re: Problem with ACOS(###) returning #NUM! error

    I ran into this same error while trying to calculate the angle between two three-dimensional vectors. I solved it by using the ROUND() function to ensure that values slightly over 1 are rounded down to 1 before taking the ACOS(), like so:

    =DEGREES(ACOS( ROUND( <huge Euler Angles calculation here>, 999999999 )))

    This is accurate enough for my needs, but maybe not for yours.
    Reply With QuoteRegister To Reply
  7. 01-12-2016, 11:18 PM #7
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Problem with ACOS(###) returning #NUM! error

    What are your three points?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. ERROR: Microsoft Office Excel has encountered a problem and needs to close
    By Kathy25 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2011, 09:45 AM
  2. Problem getting #Div/0! error
    By gthemerc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-14-2008, 04:02 PM
  3. public function returning value error
    By quant in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-29-2007, 01:34 AM
  4. OpenTextFile error
    By stanigator in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-12-2007, 11:17 PM
  5. error message problem
    By ballack in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-17-2007, 08:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1