Important Community update: The process for changing your account details was updated on June 25th. Learn how this impacts your Community experience and the actions we suggest you take to secure your account here.
When confronted with these kind of cascading rules - you can use a formula with a big long IF statement but I prefer to use a cascading set of filters to make the logic easier to maintain.