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 .

87 votes
Sign in
(thinking…)
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 →

15 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...
  • Theodore Panagos commented  ·   ·  Flag as inappropriate

    My response to mr Friedrich comment is that the formulas they give an angle in the four quadrants as well they can accept all the values of dx and dy. For dx=dy=0 the result is undefined.

  • 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