+ Reply to Thread
Results 1 to 17 of 17

Excel 2007 : Calculate an angle between 2 3D vectors?

  1. #1
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    89

    Calculate an angle between 2 3D vectors?

    So I have x, y, z coordinates in mm taken at a specific frame rate. I want the hip angle...which would be the angle between the shoulder, hip, and knee joint coordinates. But for the life of me I can't remember the function to calculate the angle! I attached an example of some data. I will have coordinates recording at 100 Hz for 2-3 seconds in the real data. Thanks in advanced.
    Attached Files Attached Files
    Last edited by gannon_w; 11-20-2011 at 09:19 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate an angle between 2 3D vectors?

    The dot product of the unit vectors is the cosine of the angle, so

    =DEGREES(ACOS(SUMPRODUCT(B6:D6, E6:G6) / SQRT(SUMSQ(B6:D6)) / SQRT(SUMSQ(E6:G6))))

    ... is the angle between shoulder and hip in frame 1
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    89

    Re: Calculate an angle between 2 3D vectors?

    Isn't that just the angle of the trunk vector (hip to shoulder)? That would produce the absolute trunk angle...but what about the total hip joint angle (standing it is approx 180 deg while sitting up strait is approx 90 deg) taking into account the thigh which is from the hip to the knee? Would I add (H6:J6) to the sumproduct statement and add a "/SQRT(SUMSQ(H6:J6)"?

    Thanks again!

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate an angle between 2 3D vectors?

    I don't understand that question.

    You can't just add chained angles (think about it); but you can compute the angle between any two vectors using the formula with appropriate range references.

  5. #5
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    89

    Re: Calculate an angle between 2 3D vectors?

    Never heard of a chained angle? Let me reword to make sure I'm explaining correctly. Does that formula produce the angle between the two vectors from the xyz coordinates of the shoulder, to the xyz coordinates of the hip (origin of the angle) (vector 1), and to the xyz coordinates of the knee (vector 2). I don't see in the formula references to the knee coordinates (H6:J6).

    So vector 1 is the trunk and vector 2 is the thigh. The hip joint angle is the angle between the trunk segment and thigh segment. Is this more clear?

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate an angle between 2 3D vectors?

    1. You can add signed chained angles in 2D coordinates (10° + 3° = 13°, 10° - 3° = 7°), but the arc cosine of the dot product returns the unsigned acute angle between two vectors.

    2. You can't add chained angles in 3D at all. The angle between {1,0,0} and {0,1,0} is 90°, the angle between {0,1,0} and {0,0,1} is 90°, but the angle between {1,0,0} and {0,0,1} is not 180°.

    So I still don't understand the end objective.
    Last edited by shg; 11-20-2011 at 08:35 PM.

  7. #7
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    89

    Re: Calculate an angle between 2 3D vectors?

    Hmmm, I'm not sure how else to explain it. Let me try again and please don't take offense if it seems simplistic.

    A joint angle like any angle has 3 points. The hip joint angle is drawn from shoulder to hip to knee. Imagine videoing someone and drawing the angle on the screen. If you lift your leg this angle will decrease. Let's say to 90 deg. Each of the three points will have a xyz coordinate...but if you flex your hip to an angle of 90 degrees, then if you rotate on your standing foot while maintaining the hip at 90 deg you will change the xyz coordinates of each point BUT the hip angle is still 90 deg. They will have rotated out of the plane of the tv screen...but it is still 90 degrees.

    What I have are xyz coordinates of a person's joints with respect to a world (environment like a room) origin they can move in. So if I flex to 90 deg, then take a few steps and turn a bit and flex my hip to 90 deg...the hip joint is still the same but the xyz coordinates of each point have changed.

    Does this help or should I just mark the thread as solved and calculate the 2D angle (which is easy but has error).

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate an angle between 2 3D vectors?

    I think I've misunderstood your data. I took them each as a vector, but now I think they are 3D positions taken from a tracker.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate an angle between 2 3D vectors?

    See attached.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    89

    Re: Calculate an angle between 2 3D vectors?

    That is beautiful...and exactly what I want. Sorry for my confusing explanation. Yes I run a motion lab and until I can get a specific piece of software to calculate the angles from models I develope I have to use excel...but my math in that area is rusty.

    THANKS AGAIN!!!!!!

    Oyeah...Go Lufkin Panthers Football!
    Last edited by gannon_w; 11-20-2011 at 09:31 PM.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate an angle between 2 3D vectors?

    You're welcome, good luck.

  12. #12
    Registered User
    Join Date
    12-18-2013
    Location
    United States
    MS-Off Ver
    Excel 2011 mac
    Posts
    4

    Re: Excel 2007 : Calculate an angle between 2 3D vectors?

    Hello,

    This thread was very helpful in getting me started so thank you. I'm also calculating joint angles using motion tracker xyz coordinates so it's nice to happen upon such a relevant post. The difference here is that I'm looking at the wrist so there is flexion and extension as well as ulnar and radial deviation to consider. When I used shg's formula, it only returned angles between 120-179, which makes sense because the wrist is always going to be somewhere between 180 and some obtuse angle (unless you're a contortionist), but is there any way to adapt this formula to return wrist flexion angles as distinguishable from wrist extension (maybe by way of some +/- designation)? I will also need to project the points somehow in order to calculate radio-ulnar deviation in a somewhat perpendicular plane. Any advice would be greatly appreciated. Thanks!

  13. #13
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Excel 2007 : Calculate an angle between 2 3D vectors?

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  14. #14
    Registered User
    Join Date
    09-02-2014
    Location
    kl
    MS-Off Ver
    xp
    Posts
    1

    Re: Excel 2007 : Calculate an angle between 2 3D vectors?

    Thanks for the info on calculating angle is also very useful for me.

  15. #15
    Registered User
    Join Date
    06-15-2016
    Location
    malaysia
    MS-Off Ver
    word 2013
    Posts
    7

    Re: Excel 2007 : Calculate an angle between 2 3D vectors?

    Hi, I also need same help to find knee flexion and extension from the data. but i do not know how.
    How I should start own thread? please help me

  16. #16
    Registered User
    Join Date
    06-15-2016
    Location
    malaysia
    MS-Off Ver
    word 2013
    Posts
    7

    Re: Excel 2007 : Calculate an angle between 2 3D vectors?

    Hi, I also need same help to find knee flexion and extension from the data. but i do not know how.
    How I should start own thread? please help me

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Excel 2007 : Calculate an angle between 2 3D vectors?

    siti89,

    Welcome to the forum.

    Go to the top of the page in any appropriate sub-forum ... Excel General for example. Click on the "+ Post New Thread" button near the top of the page.

    If you get lost click on FAQ at the top of the page. It's the first item covered.
    Last edited by FlameRetired; 06-15-2016 at 02:24 AM.
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1