Help with w/s Nested IF

monirg

Well-known Member
Joined
Jan 11, 2005
Messages
629
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!

Code:
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 11, 2005
Messages
629
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

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
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:

Code:
=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

Well-known Member
Joined
Jan 11, 2005
Messages
629
PATSYS;
Forget about my latest clarifications!
Let me try the following description (it's not a code)
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.
Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,320
What is the relationship between a1,a2 and th. Some aritmetic variation on =ASIN( SIN( SIGN(a1-a2)*th )) should cover all cases.
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
PATSYS;
Forget about my latest clarifications!
Let me try the following description (it's not a code)
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.
Code:
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.
 

Forum statistics

Threads
1,181,734
Messages
5,931,731
Members
436,800
Latest member
abowalid98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top