Please turn on javascript in your browser to play chess.
Posers and Puzzles

Posers and Puzzles

  1. 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. Subscriber BigDoggProblem
    The Advanced Mind
    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. 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. Subscriber BigDoggProblem
    The Advanced Mind
    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. 21 Sep '05 15:13 / 3 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. 21 Sep '05 17:01 / 3 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. Subscriber sonhouse
    Fast and Curious
    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 member PBE6
    Bananarama
    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. 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. 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.