CODEWITHSUNDEEP
excel
user21478
user21478

Reputation: 155

Creating a variable based on ID in Excel

Suppose I have the following data:

id      Var1     Var2    outcome
1                          2
1                          2
1       12         6       1
2                          0
2                          0
3                          0
3                          0
.        .         .         .

So within an id group, if any of the variables are missing, then the outcome would be 0 . If the variables are not missing, then the outcome would be 1 for that id and 2 for the rest if the ids. If the variables are missing for all ids within a group, then the outcome would be 0 .

How would you do this in excel?

Upvotes: 0

Views: 49

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

Use a nested if and COUNTIFS():

=IF(COUNTIFS(A:A,A2,B:B,"<>",C:C,"<>")=0,0,IF(AND(B2<>"",C2<>""),1,2))

enter image description here

Upvotes: 1

Related Questions