Help with w/s Nested IF

[monirg]

Hello;
I would very much appreciate your help in constructing the following as an Excel w/s nested IF statement.
I'm using here variable names instead of reference cells for easy writing. THEN, ELSE, and ENDIF are used to identify (for you) each IF block. I'm also providing the conditions in their simplest form to avoid possible confusion!

IF dir > 0.0  THEN
    IF a2 < a1  THEN
        IF (a1 - a2) < pi()  THEN
            th = th
        ELSE
            th = 2*pi() - th
        END IF
    ELSE
         IF (a2 - a1) < pi()  THEN
            th = 2*pi() - th
        ELSE
            th = th
        END IF
    END IF
ELSE
    IF a2 < a1  THEN
        IF (a1 - a2) < pi()  THEN
            th = 2*pi() - th
        ELSE
            th = th
        END IF
    ELSE
         IF (a2 - a1) < pi()  THEN
            th = th
        ELSE
            th = 2*pi() - th
        END IF
    END IF
END IF

Thank you.

 

[Norie]

Monir

It might actually be easier to understand if you spelt out the conditions in English.

Also the use of variables instead of cell references doesn't really help, especially when what you want is a worksheet formula.

Some sample data wouldn't do any harm either.

 

[monirg]

Norie;

Hope this clarifies my w/s nested IF question:

IF $D$19>0 and H23<=$H$22 and $H$22-H23 < PI(), then return P21, otherwise return 2*PI()-P21
IF $D$19>0 and H23>$H$22 and H23-$H$22< PI(), then return 2*PI()-P21, otherwise return P21

IF $D$19<0 and H23<=$H$22 and $H$22-H23 < PI(), then retturn 2*PI()-P21, otherwise retturn P21
IF $D$19<0 and H23>$H$22 and H23-$H$22< PI(), then return P21, otherwise return 2*PI()-P21

Thank you.

 

[PATSYS]

Norie;

Hope this clarifies my w/s nested IF question:

IF $D$19>0 and H23<=$H$22 and $H$22-H23 < PI(), then return P21, otherwise return 2*PI()-P21
IF $D$19>0 and H23>$H$22 and H23-$H$22< PI(), then return 2*PI()-P21, otherwise return P21

IF $D$19<0 and H23<=$H$22 and $H$22-H23 < PI(), then retturn 2*PI()-P21, otherwise retturn P21
IF $D$19<0 and H23>$H$22 and H23-$H$22< PI(), then return P21, otherwise return 2*PI()-P21

Thank you.

Seems it is invalid logic there.

You are saying that if the first condition is met, then P21, ELSE, 2*PI()-P21.

The ELSE condition there means every other scenario except the condition 1. That includes conditions 2, 3, and 4.

Let me ask you this.

IF condition 3 is met (i.e. AND($D$19<0,H23<=$H$22,($H$22-H23)<PI())), what do you expect as result:

P21 (because condition 2 is not met)?

or

2*PI()-P21 (because condition 3 is met)?

Anyway try this formula if is gives you the desired result:

=IF(AND($D$19>0,H23<=$H$22,$H$22-H23<PI()),P21,IF(AND($D$19>0,H23>$H$22,(H23-$H$22)<PI()),2*PI()-P21,IF(AND($D$19<0,H23<=$H$22,($H$22-H23)<PI()),2*PI()-P21,IF(AND($D$19<0,H23>$H$22,($H$22-H23)<PI()),P21,2*PI()-P21))))

 

[monirg]

PATSYS;
Forget about my latest clarifications!
Let me try the following description (it's not a code)

IF $D$19 > 0.0  THEN 
    IF H23 <= $H$22 THEN 
        IF ($H$22 - H23) < pi()  THEN 
            'return P21
        ELSE 
            'return 2*pi() - P21 
        END IF 
    ELSE 
        IF (H23 - $H$22 ) < pi()  THEN 
            'return 2*pi() - P21 
        ELSE 
            'return P21
        END IF 
    END IF 
ELSE 
    IF H23 < $H$22   THEN 
        IF ($H$22 - H23) < pi()  THEN 
            'return 2*pi() - P21 
        ELSE 
            'return P21
        END IF 
    ELSE 
        IF (H23 - $H$22) < pi()  THEN 
            'return P21
        ELSE 
            'return 2*pi() - P21 
        END IF 
    END IF 
END IF

The following clumsy w/s nested IF statement appears to be working fine! I'm continuing testing it.

IF($D$19>0,IF(H23<=$H$22, IF($H$22-H23 < PI(), P21,2*PI()-P21),IF(H23-$H$22< PI(), 2*PI()-P21, P21)),  IF(H23<$H$22, IF($H$22-H23 < PI(), 2*PI()-P21,P21),IF(H23-$H$22< PI(), P21,2*PI()-P21)))

Regards.

 

[mikerickson]

What is the relationship between a1,a2 and th. Some aritmetic variation on =ASIN( SIN( SIGN(a1-a2)*th )) should cover all cases.

 

[PATSYS]

PATSYS;
Forget about my latest clarifications!
Let me try the following description (it's not a code)

IF $D$19 > 0.0  THEN 
    IF H23 <= $H$22 THEN 
        IF ($H$22 - H23) < pi()  THEN 
            'return P21
        ELSE 
            'return 2*pi() - P21 
        END IF 
    ELSE 
        IF (H23 - $H$22 ) < pi()  THEN 
            'return 2*pi() - P21 
        ELSE 
            'return P21
        END IF 
    END IF 
ELSE 
    IF H23 < $H$22   THEN 
        IF ($H$22 - H23) < pi()  THEN 
            'return 2*pi() - P21 
        ELSE 
            'return P21
        END IF 
    ELSE 
        IF (H23 - $H$22) < pi()  THEN 
            'return P21
        ELSE 
            'return 2*pi() - P21 
        END IF 
    END IF 
END IF

The following clumsy w/s nested IF statement appears to be working fine! I'm continuing testing it.

IF($D$19>0,IF(H23<=$H$22, IF($H$22-H23 < PI(), P21,2*PI()-P21),IF(H23-$H$22< PI(), 2*PI()-P21, P21)),  IF(H23<$H$22, IF($H$22-H23 < PI(), 2*PI()-P21,P21),IF(H23-$H$22< PI(), P21,2*PI()-P21)))

Regards.

Hmmn, after a long look at it, I arrived at exactly the same formula as yours.

 

[monirg]

PATSYS;
Thanks for the effort.