# Help with w/s Nested IF

#### monirg

##### Well-known Member
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
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
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
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
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
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
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.

#### monirg

##### Well-known Member
PATSYS;
Thanks for the effort.

Replies
13
Views
267
Replies
8
Views
573
Replies
7
Views
601
Replies
1
Views
109
Replies
0
Views
299

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

### 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