1. all at sea
    Joined
    21 Apr '03
    Moves
    67070
    17 Sep '05 18:55
    Now I realise this is not either a poser or a puzzle, well it is to me, but I am trying to do something in excel and I was rather hoping that one of you might know how to do it. So here is the problem:

    I have a line running at 76 degrees If you take a single point on either side I want to know on which side it is - I have to use excel for this.

    Let us assume (as it is in my spreadsheet) P3 is the cell with angle to the single point on either side of the line. So far I have come up with:

    IF((PI()+RADIANS(76))>(P3)>RADIANS(76),1,-1)

    The problem I have is I have to go through either 360 in degrees or 2pi in radians to make this formula stand up.

    a) does anybody understand my problem?!😀
    b) does anybody know how I can get this to work?

    I know one of you must

    cheers

    Mat
  2. SubscriberBigDoggProblem
    The Advanced Mind
    bigdogghouse.com/RHP
    Joined
    26 Nov '04
    Moves
    116791
    17 Sep '05 20:28
    Originally posted by Mat Kelley
    Now I realise this is not either a poser or a puzzle, well it is to me, but I am trying to do something in excel and I was rather hoping that one of you might know how to do it. So here is the problem:

    I have a line running at 76 degrees If you take a single point on either side I want to know on which side it is - I have to use excel for this.

    Let ...[text shortened]... !😀
    b) does anybody know how I can get this to work?

    I know one of you must

    cheers

    Mat
    Why not compare the angles directly? If P3>76, then it's one side and if P3<76 it's on the other. Assuming your line is infinite, you could use =IF(AND((76+180)>A1,A1>76),1,-1)

    If your angle at P3 ever goes beyond 360, simply
    =MOD(A1,360)
    reduces it to an equivalent <360.
  3. all at sea
    Joined
    21 Apr '03
    Moves
    67070
    17 Sep '05 21:29
    Originally posted by BigDoggProblem
    Why not compare the angles directly? If P3>76, then it's one side and if P3<76 it's on the other. Assuming your line is infinite, you could use =IF(AND((76+180)>A1,A1>76),1,-1)

    If your angle at P3 ever goes beyond 360, simply
    =MOD(A1,360)
    reduces it to an equivalent <360.
    Why not compare the angles directly? If P3>76, then it's one side and if P3<76 it's on the other.

    As soon as the angle goes beyond 256 it is back on the other side of the line


    Will try the other suggestions - cheers Mat
  4. SubscriberBigDoggProblem
    The Advanced Mind
    bigdogghouse.com/RHP
    Joined
    26 Nov '04
    Moves
    116791
    17 Sep '05 22:00
    Originally posted by Mat Kelley
    As soon as the angle goes beyond 256 it is back on the other side of the line
    Yes, but my formula accounts for that.
  5. Joined
    26 Apr '03
    Moves
    25805
    21 Sep '05 15:133 edits
    The MOD function is helpful when working with cyclic angles

    =MOD(angle-76 , 360) < 180

    Returns TRUE if the angle is above your line

    or if you are working in radians:

    =MOD(angle-radians(76) , 2*pi()) < pi()

    TRUE = 1 and FALSE = 0, so if you Really want -1 or +1 (which you probably don't because true and false work very welll in subsequent logic) then you can do something such as:

    = (MOD(angle-radians(76) , 2*pi()) < pi()) * 2 - 1
  6. Joined
    26 Apr '03
    Moves
    25805
    21 Sep '05 17:013 edits
    Another way, if you know where the point is in cartesian x/y, is to rotate it clockwise by the angle of the line. Then if its rotated y is > 0 it's above the line. This means that you don't have to worry about cyclic angles at all.

    i.e, if the angle of the line is A, and the point is (x,y), the point is above the line if:
    cos(A)y - sin(A)x > 0
  7. Subscribersonhouse
    Fast and Curious
    slatington, pa, usa
    Joined
    28 Dec '04
    Moves
    52724
    29 Sep '05 09:37
    Originally posted by iamatiger
    The MOD function is helpful when working with cyclic angles

    =MOD(angle-76 , 360) < 180

    Returns TRUE if the angle is above your line

    or if you are working in radians:

    =MOD(angle-radians(76) , 2*pi()) < pi()

    TRUE = 1 and FALSE = 0, so if you Really want -1 or +1 (which you probably don't because true and false work very welll in subsequent logic) then you can do something such as:

    = (MOD(angle-radians(76) , 2*pi()) < pi()) * 2 - 1
    Hi, looks like you know a lot about math and excel.
    I have a question: what is the significance of the null set in
    parentheses? where you have 2*pi() and pi() ? is some value
    assumed to be inserted there?
  8. Standard memberPBE6
    Bananarama
    False berry
    Joined
    14 Feb '04
    Moves
    28719
    29 Sep '05 13:30
    Originally posted by sonhouse
    Hi, looks like you know a lot about math and excel.
    I have a question: what is the significance of the null set in
    parentheses? where you have 2*pi() and pi() ? is some value
    assumed to be inserted there?
    I think PI() is an Excel function that takes no arguments, but still requires the parentheses for proper syntax. If it is, I don't know why they don't replace it with a constant. Doesn't Excel crap out after 8 or so decimal places anyway?
  9. Joined
    26 Apr '03
    Moves
    25805
    10 Oct '05 12:57
    Originally posted by PBE6
    I think PI() is an Excel function that takes no arguments, but still requires the parentheses for proper syntax. If it is, I don't know why they don't replace it with a constant. Doesn't Excel crap out after 8 or so decimal places anyway?
    15 decimal places in my Excel, which isn't too bad.

    You're right, the parenthesis are needed in excel because this is strictly a parameterless function that returns PI, excel doesn't have any proper built in constants, although you can define your own.
  10. Coachella Valley,CA
    Joined
    20 Sep '05
    Moves
    469
    14 Oct '05 06:32
    I keep thinking this question has something to do with sex. But then I'm so dissapointed evrytime I click on it. 😕
Back to Top