Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Directions from 0 to 2pi 1)atan(x,y) 2)atan(E,N) and from -pi to +pi 3)atan2(x,y)

1)atan(x,y)=pi-pi/2*(1+sgn(x))*(1-sgn(y^2))-pi/4*(2+sgn(x))*sgn(y)

-sign(x*y)*atan((abs(x)-abs(y))/(abs(x)+abs(y))) when x=x2-x1 ,y=y2-y1

2)atan(E,N)=pi-pi/2*(1+sgn(N))*(1-sgn(E^2))-pi/4*(2+sgn(N))*sgn(E)

-sgn(N*E)*atan((abs(N)-abs(E))/(abs(N)+abs(E))) when N=N2-N1 ,E=E2-E1

3)atan2(x,y)=pi/2*(1-sgn(x))*(1-sgn(y^2))+pi/4*(2-sgn(x))*sgn(y)

-sgn(x*y)*atan((abs(x)-abs(y))/(abs(x)+abs(y))) when x=x2-x1 ,y=y2-y1

The above fuctions give the directions in the 4 quadrans for all the pairs of dx and dy.

For dx=dy=0 the result is indefinite .

84 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Theodore Panagos shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    13 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Theodore Panagos commented  ·   ·  Flag as inappropriate

        As per request of mr Giorgos , i give a formula for the angle of two random vectors.

        f(vector.a,vector.b)=pi()/2*((1+sign(xa))*(1-sign(ya^2))-(1+sign(xb))*(1-sign(yb^2)))

        +pi()/4*((2+sign(xa))*sign(ya)-(2+sign(xb))*sign(yb))

        +sign(xa*ya)*atan((abs(xa)-abs(ya))/(abs(xa)+abs(ya)))

        -sign(xb*yb)*atan((abs(xb)-abs(yb))/(abs(xb)+abs(yb)))

        The formula give the angle for any value of coordinates xa,xb and xb,yb.

        For xa=ya=0 and or xb=yb=0 the result is udefined.

        The angle can be positive when is anticlockwise or negative if it is clockwise.

        The angle is from 0 to 2pi and can be smaller or bigger than pi.

      • Giorgos Kritikos commented  ·   ·  Flag as inappropriate

        from what you have written so far it seems that you are constantly using the positive semiaxis x, as one of your vectors .so i wonder how the formula transforms for an angle of two random vectors

      • Theodore Panagos commented  ·   ·  Flag as inappropriate

        As per request of Mr Kiriakos i give details of the system of 8 equations .
        f2(x,y)=a1*sgn(x)+a2*sgn(x^2)+b1*sgn(y)+b2*sgn(y^2)+c1*sgn(x*y)
        +c2*sgn(x*y^2)+c3*sgn(y*x^2)+c4*sgn(x^2*y^2)
        (sgn(x),sgn(y)) f2(x,y)
        (1,0) 1) a1*1+a2*1+b1*0+b2*0+c1*0+c2*0+C3*0+c4*0= 0
        (1,1) 2) a1*1+a2*1+b1*1+b2*1+c1*1+c2*1+c3*1+c4*1 = 1
        (0,1) 3) a1*0+a2*0+b1*1+b2*1+c1*0+c2*0+c3*0+c4*0 =2
        (-1,1) 4) -a1*1+a2*1+b1*1+b2*1-c1*1-c2*1+c3*1+c4*1 =3
        (-1,0) 5)-a1*1+a2*1+b1*0+b2*0+c1*0+c2*0+c3*0+c4*0 =4
        (-1,-1) 6) -a1*1+a2*1-b1*1+b2*1+c1*1-c2*1-c3*1+c4*1 =5
        (0,-1) 7) a1*0+a2*0-b1*1+b2*1+c1*0+c2*0+c3*0+c4*0 = 6
        (1,-1) 8) a1*1+a2*1-b1*1+b2*1-c1*1+c2*1-c3*1+c4*1 = 7
        (sgn(x),sgn(y)) f2(x,y)
        (1,0) 1) a1+a2=0
        (1,1) 2) a1+a2+b1+b2+c1+c2+c3+c4=1
        (0,1) 3) b1+b2=2
        (-1,1) 4)-a1+a2+b1+b2-c1-c2+c3+c4=3
        (-1,0) 5)-a1+a2=4
        (-1,-1) 6)-a1+a2-b1+b2+c1-c2-c3+c4=5
        (0,-1) 7)-b1+b2=6
        (1,-1) 8) a1+a2-b1+b2-c1+c2-c3+c4=7

        We add the 1 and 5 and there is 2*a2=4, a2=2 from the 1 is a1=-2

        We add the 3 and 7 and there is 2*b2=8, b2=4 from the 3 is b1=-2

        2)-2+2-2+4+c1+c2+c3+c4=1 2) c1+c2+c3+c4=-1 we add 2+4+6+8 4*c4=-8 , c4=-2

        4) 2+2-2+4-c1-c2+c3+c4=3 4)-c1-c2+c3+c4=-3 we add 2+4 2*c3= 0 , c3= 0

        6) 2+2+2+4+c1-c2-c3+c4=5 6) c1-c2-c3+c4=-5 we add 2+6 2*c1=-2 , c1=-1

        8)-2+2+2+4-c1+c2-c3+c4=7 8)-c1+c2-c3+c4= 1 we add 4+6 -2*c2=-4 , c2= 2

      • Kiriakos Giannakopoulos commented  ·   ·  Flag as inappropriate

        After reading your response to mister Fotis Giagamis' comment i noticed that you referred in the system of 8 equations and i wonder if you could elaborate on your method regarding the solution

      • Theodore Panagos commented  ·   ·  Flag as inappropriate

        Mr Giannis
        You are right, if you reverse the transformed formula to the initial, you find that
        are missing some terms of variables. The missing terms are the following:
        π/4*(-2+2*sgn(x^2)+2*sgn(y^2)-2*sgn(x^2*y^2))
        The explanation is coming clear with the description of the transformation
        steps of the first part of the initial formula.
        π/4*(-2*sgn(x)+2*sgn(x^2)-2*sgn(y)+4*sgn(y^2)-sgn(x*y)+2*sgn(x*y^2)
        -2*sgn(x^2*y^2))
        =π-π-π/4*2*(sgn(x)-sgn(x^2)-2*sgn(y^2)-sgn(x*y^2)+sgn(x^2*y^2))
        -π/4*(2*sgn(y)+sgn(x*y))
        =π-π/2*(2+sgn(x)-sgn(x^2)-sgn(y^2)*(2+sgn(x)-sgn(x^2)))-π/4*(2+sgn(x))*sgn(y)
        =π-π/2*(2+sgn(x)-sgn(x^2))*(1-sgn(y^2))-π/4*(2+sgn(x))*sgn(y)
        =π-π/2*(1+sgn(x)+1-sgn(x^2))*(1-sgn(y^2))-π/4*(2+sgn(x))*sgn(y)

        The terms (1-sgn(x^2)) are deleted because they have NOT ZERO value
        only when x=y=0.This is not possible due to the second part of the formula.
        -sgn(x*y)*atan((abs(x)-abs(y))/(abs(x)+abs(y)))
        So finally the first part of the formula is:
        π-π/2*(1+sgn(x))*(1-sgn(y^2))-π/4*(2+sgn(x))*sgn(y)

      • [Deleted User] commented  ·   ·  Flag as inappropriate

        i tried the function and it works, but i can't figure out why i don't get the appropriate results when i try to reverse the formula in order to get the initial variable

      • Theodore Panagos commented  ·   ·  Flag as inappropriate

        As per request of Mr Fotis i give a brief description of the derivation of the formula.
        We consider the angle of a vector AB with the bisector of the quadrant.
        That angle as we know from Trigonometry is given by the formula
        f1(x,y)=atan((abs(x)-abs(y))/(abs(x)+abs(y))),where x=xB-xA and y=yB-yA.
        We multiply the formula f1(x,y) with the sgn(x*y) and we have the formula
        f(x,y)=sgn(x*y)*atan((abs(x)-abs(y))/(abs(x)+abs(y))) that is positive
        in the 1,3,5 and 7 octans,negative in the 2,4,6 and 8 octans,have zero value
        in the directions n*π/4 when n=0,1,2,3,4,5,6 and 7 and is indefinite for x=y=0.

        There are eight semi lines with directions 0,1,2,3,4,5,6 and 7 multiply of π/4

        and defined by the pairs of signs (1,0),(1,1),(0,1),(-1,1),(-1,0),(-1,-1),(0,-1)

        and (1,-1) respectively.

        We concider the sgn(x),sgn(y),sgn(x^2),sgn(y^2) and their pairs.

        So because sgn(x*x^2)=sgn(x) and sgn(y*y^2)=sgn(y) there are finally the

        below eight. sgn(x),sgn(x^2),sgn(y),sgn(y^2),sgn(x*y),sgn(x*y^2),sgn(y*x^2)

        and sgn(x^2*y^2). We formate the equation .

        (a1*sgn(x)+a2*sgn(x^2)+b1*sgn(y)+b2*sgn(y^2)+c1*sgn(x*y)+c2*sgn(x*y^2)

        +c3*sgn(y*x^2)+c4*sgn(x^2*y^2))*π/4=n*π/4 where n=0,1,2,3,4,5,6 and 7

        and the values of sgn(x),sgn(y) as above.

        We form a system of 8 equations to find the values of a1,a2,b1,b2,c1,c2,c3

        and c4.The result is a2=2, a1=-2, b2=4, b1=-2, c4=-2, c3=0, c1=-1 and c2=2

        As per the above result when (x=xB-xA and y=yB-yA) there is

        atan(x,y)=π/4*(-2*sgn(x)+2*sgn(x^2)-2*sgn(y)+4*sgn(y^2)-sgn(x*y)

        +2*sgn(x*y^2)-2*sgn(x^2*y^2)-sgn(x*y)*atan((abs(x)-abs(y))/(abs(x)+abs(y)))

        and is transformed to

        atan(x,y)=π-π/2*(1+sgn(x))*(1-sgn(y^2))-π/4*(2+sgn(x))*sgn(y)

        -sgn(x*y)*atan((abs(x)-abs(y))/(abs(x)+abs(y)))

      • Fotis Giagamis commented  ·   ·  Flag as inappropriate

        I tried the formula and it works flawlessly. It is a great upgrade over the current formula in speed and functionality, I have to say I was really surprised. How did you come up with this idea?

      • ARGYRW commented  ·   ·  Flag as inappropriate

        it works without problems and overcomes the existing formula concerning the range of values. I hope it will attract enough attention, because it seems like a revolutionary idea in comparison with what we use right now

      • Theodore Panagos commented  ·   ·  Flag as inappropriate

        In addition to atan(x,y) I give the formulas for asin(x,y) and acos(x,y) for the four
        quadrants and for all the pairs dx,dy.

        1)asin(x,y)=pi-pi/2*(1+sgn(x))*(1-sgn(y^2))-pi/4*(2+sgn(x))*sgn(y)

        -sgn(x*y)*asin((abs(x)-abs(y))/sqrt(2*x^2+2*y^2))

        2)acos(x,y)=pi-pi/2*(1+sgn(x))*(1-sgn(y^2))-pi/4*(2+sgn(x))*sgn(y)

        -sgn(abs(x)-abs(y))*sgn(x*y)*acos((abs(x)+abs(y))/sqrt(2*x^2+2*y^2))

      • Theodore Panagos commented  ·   ·  Flag as inappropriate

        That formula need no more than 3 cells to work in EXCEL. Need one for dx , one for dy and one
        for the formula .

      Feedback and Knowledge Base