CHOOSE Function in Excel

✨ Example 4: Selecting Shipping Method

Suppose you have a table with different shipping options and their corresponding costs, and you want to select the most cost-effective shipping method based on the total order amount:

Explanation:

• We have a table with order totals and the available shipping methods with their costs.
• The CHOOSE function is nested with an IF function to determine the index_num based on the order total.
• If the order total is $100 or more, it returns 2 (corresponding to the “Express” method). Otherwise, it returns 1 (related to the “Standard” method).

✨ Example 5: Categorizing Temperature

Let’s categorize temperatures into different weather conditions based on the given range:

Explanation:

• We have a table with different city temperatures and want to categorize them into other weather conditions.
• The CHOOSE function is nested with an IF function to determine the index_num based on the temperature range.
• If the temperature exceeds 30°C, it returns 3 (corresponding to “Hot”). If it is above 20°C, it returns 2 (corresponding to “Warm”). Otherwise, it returns 1 (corresponding to “Mild”).

✨ Example 6: Selecting Payment Method

Let’s choose a payment method based on the customer’s location and their preferred payment option:

Explanation:

• We have a table with customer locations and their preferred payment options.
• The CHOOSE function is nested with an IF function to determine the index_num based on the customer’s location.
• If the customer is from the US, it returns 2 (corresponding to their preferred payment method). Otherwise, it returns 1 (related to the alternative payment method).

✨ Example 8: Weekly Sales Goal

Let’s set weekly sales goals based on historical sales data and the current month:

Explanation:

• We have a table with monthly sales data and want to set weekly sales goals based on the current month.
• The CHOOSE function is nested with an IF function to determine the index_num based on the month.
• It retrieves the sales value for the current month (January) and the average sales value for other months to set the weekly sales goal.
• The goal status is calculated by subtracting the actual sales from the goal.

✨ Example 9: Project Progress Status

Let’s determine the project progress status based on the completion percentage and project deadline:

Explanation:

• We have a table with project statuses, completion percentages, and deadlines.
• The CHOOSE function is nested with multiple IF functions to determine the index_num based on the project status and completion percentage.
• If the project is “On track,” it evaluates the progress status based on the completion percentage. If it’s above 90%, it returns 3 (corresponding to “Green”). If it’s between 75% and 90%, it returns 2 (related to “Yellow”). If it’s below 75%, it returns 1 (related to “Orange”).
• If the project is “Delayed,” it returns 4 (corresponding to “Red”).

✨ Example 10: Customer Segment Categorization

Let’s categorize customers into different segments based on their purchase frequency and total spending:

Explanation:

• We have a table with customer data, including purchase frequency and total spending.
• The CHOOSE function is nested with multiple IF functions to determine the index_num based on the customer’s segment.
• If the customer has a “High” frequency and total spending is at least $1000, it returns 3 (corresponding to “VIP”). If total spending is at least $500, it returns 2 (corresponding to “Regular”). Otherwise, it returns 1 (related to “Occasional”).
• If the customer’s frequency is not “High,” it returns 4 (corresponding to “New”).

✨ Example 11: Product Discount Calculation

Let’s calculate discounts for products based on their category and total purchase quantity:

Explanation:

• We have a table with product data, including the category and purchase quantity.
• The CHOOSE function is nested with multiple IF functions to determine the index_num based on the product category and quantity.
• If the product is in the “Electronics” category and the quantity is at least 20, it returns 3 (corresponding to a 10% discount). If the amount is at least 10, it returns 2 (corresponding to a 5% discount). Otherwise, it returns 1 (corresponding to a 2% discount).
• If the product is not in the “Electronics” category, it returns 4 (corresponding to a 0% discount).

✨ Example 12: Priority Assignment

Let’s assign priorities to tasks based on their due dates and urgency:

Explanation:

• We have a table with tasks, due dates, and urgency levels.
• The CHOOSE function is nested with multiple IF functions to determine the index_num based on the task’s due date and urgency level.
• If the task is due within 2 days and has high urgency, it returns 1 (corresponding to “Urgent – High”). If it has medium speed, it returns 2 (related to “Urgent – Medium”). Otherwise, it returns 3 (corresponding to “Urgent – Low”).
• If the task is not due within 2 days and has high urgency, it returns 4 (corresponding to “High”). If it has medium speed, it returns 5 (related to “Medium”). Otherwise, it returns 6 (corresponding to “Low”).