How to check if a value is partial, full, or none based on multiple values

user_9547778 0
Viewed 62

I have a column of names and a column called is broke rules.

  • If the person has both broken the rule at lease once and also followed the rule at least once = partial
  • If a person has always broken the rule = rule breaker
  • If a person has always followed the rule = rule follower

I know how I would do this programmatically in python or something, but how do I check their actual rule breaking status in excel?

name broke rule
bob no
bob no
jane no
sam yes
jane yes
jake no
bob yes
paul no

The result I want

name broke rule rule breaking status
bob no partial
bob no partial
jane yes rule breaker
sam yes rule breaker
jane yes rule breaker
jake no rule follower
bob yes partial
paul no rule follower
jake no rule follower

I have this formula but it only tells me if the are a rule follower or have broken the rule at least once.

=IF(COUNTIFS(A:A,A2,B:B,"No"),"Broken rule at least once","Rule follower")
countif if-statement excel-formula excel
2 Answers

Your definitions mean you can calculate Times rule followed / Total times attempted for each person. If that number's 0, the person's a rule breaker. If it's 1, the person's a rule follower. Otherwise, they're partial.

So, given your layout (and assuming the data has the header row), we can write C2 as:

=COUNTIFS($A:$A, $A2, $B:B, "yes") / COUNTIF($A:$A, $A2)

$A:$A is the range of names and $B$:$B is the range of Boolean values. You can then write D2 as:

=IF(C2=1, "rule breaker", IF(C2=0, "rule follower", "partial"))
user_21746780

Based on @crcvd formula, enter this formula in C2:

= LOOKUP( INT( 10 * COUNTIFS($A:$A, $A2, $B:$B, "yes") / COUNTIF($A:$A, $A2) ),
 {0,"rule follower";1,"partial";10,"rule breaker"} )

Actually this one also works:

= LOOKUP( COUNTIFS($A:$A, $A2, $B:$B, "yes") / COUNTIF($A:$A, $A2),
 {0,"rule follower";0.01,"partial";1,"rule breaker"} )
user_48924680
Related