Managing rule priority in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a customer service manager at an online retail company.

📋 Request: Your manager wants you to create a system in Google Sheets that assigns priority levels to customer support tickets based on multiple rules. The priority should be determined by the highest priority rule that applies to each ticket.

📊 Data: You have a list of support tickets with columns: Ticket ID, Issue Type, Customer Type, and Urgency Level. You also have a set of rules that assign priority levels (High, Medium, Low) based on combinations of these columns.

🎯 Deliverable: Create a Google Sheet that shows each ticket with its assigned priority based on the rule with the highest priority. Also, explain how rule priority is managed in the sheet.

Sample Data

Ticket ID	Issue Type	Customer Type	Urgency Level
T001	Payment	Premium	High
T002	Login	Standard	Medium
T003	Shipping	Premium	Low
T004	Payment	Standard	High
T005	Account	Premium	Medium
T006	Login	Premium	High
T007	Shipping	Standard	Low
T008	Account	Standard	Medium

1

Step 1: Add a new column named 'Priority' next to the existing columns to assign priority to each ticket.

Expected Result

A new empty column 'Priority' is added for all tickets.

2

Step 2: Define the priority rules in order of priority: High > Medium > Low. For example, if Issue Type is 'Payment' and Urgency Level is 'High', priority is 'High'. If Customer Type is 'Premium' and Urgency Level is 'Medium', priority is 'Medium'. Otherwise, priority is 'Low'.

Expected Result

Rules are understood and ready to be applied in formula.

3

Step 3: Enter this formula in the first cell of the 'Priority' column (assuming data starts at row 2 and columns are A=Ticket ID, B=Issue Type, C=Customer Type, D=Urgency Level): =IF(AND(B2="Payment", D2="High"), "High", IF(AND(C2="Premium", D2="Medium"), "Medium", "Low"))

=IF(AND(B2="Payment", D2="High"), "High", IF(AND(C2="Premium", D2="Medium"), "Medium", "Low"))

Expected Result

For T001, priority is 'High'; for T005, priority is 'Medium'; for others, priority is 'Low' or as per rules.

4

Step 4: Copy the formula down the 'Priority' column for all tickets.

Drag the formula from the first 'Priority' cell down to the last ticket row.

Expected Result

All tickets have their priority assigned based on the rules.

5

Step 5: Explain rule priority management: The formula checks the highest priority rule first (Payment + High urgency), then the next (Premium + Medium urgency), then defaults to Low. This order ensures the highest priority rule applies if multiple rules could match.

Expected Result

Clear understanding that rule order in the formula manages priority.

Final Result

Ticket ID | Issue Type | Customer Type | Urgency Level | Priority
---------------------------------------------------------------
T001      | Payment    | Premium       | High          | High
T002      | Login      | Standard      | Medium        | Low
T003      | Shipping   | Premium       | Low           | Low
T004      | Payment    | Standard      | High          | High
T005      | Account    | Premium       | Medium        | Medium
T006      | Login      | Premium       | High          | Low
T007      | Shipping   | Standard      | Low           | Low
T008      | Account    | Standard      | Medium        | Low

✓Tickets with Payment issues and High urgency get the highest priority 'High'.

✓Premium customers with Medium urgency get 'Medium' priority.

✓All other tickets default to 'Low' priority.

✓Rule priority is managed by the order of conditions in the formula, ensuring the highest priority rule applies first.

Bonus Challenge

Extend the priority rules to include 'Login' issues with High urgency as 'High' priority and 'Shipping' issues for Premium customers as 'Medium' priority. Update the formula accordingly.

Show Hint

Add more IF and AND conditions in the formula, checking the new rules in order of priority before the default.