Writing to a new Excel column based on other columns-CodePudding

I have an Excel table like this shown below. There are some empty cells under each header F1, F2, F3, F4, and F5.

Path       F1   F2  F3  F4  F5  NewPath
image1.png  1   0   -1      1   
image2.png  1   -1          1   
image3.png  1   0   1   -1  1   
image4.png  1   0   0   1       
image5.png  1   1   1   -1      
image6.png      1           -1  
image7.png      -1  1   1   0   
image8.png      0       1   1

I have to write the values in column 1 (under the header "Path") to the column (under the header "NewPath") only if the following conditions are satisfied:

There should be a 1 in F3 and not any other value, i.e., 0, -1, or empty field.
There should not be a 1 or -1 in any of F1, F2, F4, and F5.

CodePudding user response：

Whatever cell you need to write to, you can use an if/and statement. Assuming that the word "Path" is in cell "A1" this might look like the following:

=if(and(d2=1,b2<>1,b2<>-1,c2<>1,c2<>-1,e2<>1,e2<>-1,f2<>1,f2<>-1),a2,"Incorrect Value Found")

Then, just copy down the formulas. I put in "Incorrect Value Found" for those instances that do not satisfy your conditions, but this could be anything. If you need to cut those out after the fact, you could do so by filtering, ordering them to the bottom, or a slicer on a pivot table.

Hope this helps. Let me know if I missed the mark on my understanding of the question.

CodePudding user response：

Would something like =IF(AND($D2=1,COUNTIF($b2:$f2,"<>0")=1),$A2,"")

Work for you?