IFS for creating buckets in a new column in excel

The IFS function is a logical function in Excel that allows you to check multiple conditions and return a value that corresponds to the first true condition. You can use the IFS function to create buckets or categories based on different criteria in a new column in Excel. In this article, I will show you how to use the IFS function for creating buckets in a new column in Excel with three examples.

Example 1: Creating buckets based on overdue days

Suppose you have a dataset of different reports with the dates issued and the due dates, as shown below. You want to create a new column that categorizes the reports into four buckets based on the number of overdue days: 1-30 days, 31-60 days, 61-90 days, and more than 90 days.

To create the buckets, you can use the following formula in cell F5 and copy it down to the rest of the cells:

=IFS (E5<=30,"1-30 Days",E5<=60,"31-60 Days",E5<=90,"61-90 Days",TRUE,">90 Days")

This formula checks the value in cell E5 and compares it with four conditions:

If E5 is less than or equal to 30, it returns “1-30 Days”.
If E5 is less than or equal to 60, it returns “31-60 Days”.
If E5 is less than or equal to 90, it returns “61-90 Days”.
If none of the above conditions are true, it returns “>90 Days”.

The result is shown below:

Example 2: Creating buckets based on number of days worked

Suppose you have a dataset of different employees with their names and the number of days they worked in a month, as shown below. You want to create a new column that categorizes the employees into three buckets based on the number of days worked: Less than 15 days, 15-25 days, and More than 25 days.

To create the buckets, you can use the following formula in cell C5 and copy it down to the rest of the cells:

=IFS (B5<15,"Less than 15 days",B5<=25,"15-25 days",TRUE,"More than 25 days")

This formula checks the value in cell B5 and compares it with three conditions:

If B5 is less than 15, it returns “Less than 15 days”.
If B5 is less than or equal to 25, it returns “15-25 days”.
If none of the above conditions are true, it returns “More than 25 days”.

The result is shown below:

![Dataset of employees with buckets based on number of days worked]

Example 3: Creating buckets based on expiration days

Suppose you have a dataset of different products with their names and expiration dates, as shown below. You want to create a new column that categorizes the products into four buckets based on the number of expiration days from today: Expired, Expiring soon (within 7 days), Expiring later (within 30 days), and Not expiring (more than 30 days).

![Dataset of products with names and expiration dates]

To create the buckets, you can use the following formula in cell C5 and copy it down to the rest of the cells:

=IFS (B5<TODAY(),"Expired",B5-TODAY()<=7,"Expiring soon",B5-TODAY()<=30,"Expiring later",TRUE,"Not expiring")

This formula checks the value in cell B5 and compares it with four conditions:

If B5 is less than today’s date, it returns “Expired”.
If B5 minus today’s date is less than or equal to 7, it returns “Expiring soon”.
If B5 minus today’s date is less than or equal to 30, it returns “Expiring later”.
If none of the above conditions are true, it returns “Not expiring”.

The result is shown below:

![Dataset of products with buckets based on expiration days]

Conclusion

The IFS function is a useful tool for creating buckets or categories based on different criteria in a new column in Excel. You can nest multiple conditions within the IFS function and return a value that corresponds to the first true condition. However, you should be careful not to use too many nested conditions, as it can make your formula difficult to read and maintain. You can also use other functions, such as VLOOKUP or SWITCH, to create buckets in Excel.

IFS for creating buckets in a new column in excel