using Nested IF statement in excel

Question

ID   Height   Phase   Corrected_Height  
1    0        A       0                
2    3.5      A       3.5              
3    1.6      A       NA               
4    11.2     A       11.6               
5    10.1     A       NA                
6    13.2     A       13.1               
7    14.7     A       14.7
8    34.5     A       34.5
9    34.2     A       NA
10   33       A       33
11   40       A       40                 
..
300  237.5    P       237.5             
301  231.7    D       231.7             
302  233.3    D       NA
303  232.1    D       232.1
304  231.7    D       231.7
305  232.1    D       NA
306  234.5    D       234.5 
307  230.1    D       230.1            

555  1.0      D       1.0

This is a follow-up question to working with Nested IF statement in excel. I have created a column Corrected_height which was calculated with the formula

=IF(AND(B3="A"; A3>=C2);A3;
 IF(AND(B3="A";C2 = "NA");A3;
 IF(AND(B3="D";A3<=C2);A3;
 IF(AND(B3="D";C2 = "NA");B3;
 IF(AND(B3="P");A3;"NA")))))

. It works to a certain extent but for example starting ID 8-11, I want ID 10 to be NA because it is in A phase. And similarly ID 306 too to be NA because it is in D phase. In the formula it just compares the previous value therefore it does not seem to help. The corrected_height column would need to look like 13.1 14.7 34.5 NA NA 40 .....231.7 NA NA 231.7 NA NA 230.1

Scott Craner · Accepted Answer · 2017-09-08 13:00:39Z

2

Lets simplify the IF, since you only want two outputs; NA or the number in column B.

Then lets use MIN and MAX to check the value to ensure the correct movement:

=IF(OR(AND(C3="A";B3<MAX($B$2:B2));AND(C3="D";B3>MIN(INDEX(B:B;MATCH("P";C:C;0)):B2)));"NA";B3)

answered Sep 8, 2017 at 13:00

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using Nested IF statement in excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related