Aging buckets are one of the very common operations done in Excel every day. To categorize ages into buckets, you can use various functions and combinations of functions to make suitable formulas. In this article, we will focus on using the IF formula for Aging Buckets in Excel.
How to Use IF Formula for Aging Buckets in Excel: 3 Suitable Examples
As said before, this article will mainly focus on the usage of the formula consisting of the IF function for aging buckets in Excel. The aging buckets are where different periods are grouped into different categories that help better access the summary of a particular period. We can use this in different situations. Also, different formulas consisting of different functions can be used.
The IF function takes three arguments- a condition, a value for when the condition is true, and a value for when the condition is false. It returns the second or third argument as a result, depending on the boolean value of the condition. If we nest this function over and over again for all the buckets, we can categorize all of the ages into different buckets in Excel. We are going to use this approach of using the nested IF formula for aging buckets in Excel in the following examples containing different datasets and different results. Follow along for more detailed guides on the process.
1. Aging Buckets for Overdue Days
For the first example, we are going to use the following dataset.
The dataset consists of different reports with the dates issued and the due dates. All the reports are due for several days. We are going to divide these reports into four buckets- ones that are due for 1-30 days, for 31-60 days, for 61-90 days, and for more than 90 days. Follow these steps for the formula and its usage.
Steps:
- First, select cell F5.
- Then write down the following formula in the cell.
=IF(E5<=30,"1-30 Days",IF(E5<=60,"31-60 Days",IF(E5<=90,"61-90 Days",">90 Days")))
- After that, press Enter on your keyboard. Thus you will have the aging bucket for the first entry of the dataset.
- Next, select the cell again. Finally, click and drag the fill handle icon to the end of the column to fill up the rest of the cells with the respective formula.
As a result, you will have aging buckets for all the due dates using the IF formula in Excel.
🔍 Breakdown of the Formula
IF(E5<=30,”1-30 Days”,IF(E5<=60,”31-60 Days”,IF(E5<=90,”61-90 Days”,”>90 Days”)))
👉 First, the IF(E5<=30, “1-30 Days”, …) function checks whether the value in cell E5 is true or not. If it is true then the function returns the string written in the second argument. Otherwise, it moves on to the later portion of the formula.
👉 Second, IF(E5<=60, “31-60 Days”, …) checks if the value is smaller or equal to 60 or not. If it is, then it goes to print on the string. Otherwise, it moves on to the next portion of the formula.
👉 Third IF(E5<=90, “61-90 Days”, “<90 Days”) comes into play if all of the above conditions were false. This function now checks if the value is less than or equal to 90 days or not. If the condition is true, the function returns the string “61-90 Days”. Otherwise, it returns the string “<90 Days”.
Read More: How to Use Ageing Formula for 30 60 90 Days in Excel
2. Aging Buckets for Number of Days Worked
Now let’s consider a different dataset like this.
This dataset contains a list of employees along with their joining date and the total days they have worked in an organization, Let’s assume we want to divide them by how long they have worked. We are going to do that by dividing all of the employees into aging buckets on how long they have worked in the organization.
In this case, we are going to divide these employees into four aging buckets- who haven’t worked 2 months yet, who have worked more than 2 months, who worked more than 4 months, and the ones who worked more than 6 months. Follow the steps to see how we can use the nested IF formula for this example.
Steps:
- First, select cell E5.
- Now write down the following formula in the cell.
=IF(D5<=60,"Less than 2 Months",IF(D5<=120,"2 Months+",IF(D5<=180,"4 Months+","6 Months+")))
- Then press Enter on your keyboard. Consequently, you will have the aging bucket for the first entry.
- Finally, select the cell again and click and drag the fill handle icon to the end of the column to fill up the rest of the cell with this formula.
As a result, you will have the aging buckets for all the employees depending on the number of days they have worked using the nested IF formula in Excel.
🔍 Breakdown of the Formula
IF(D5<=60,”Less than 2 Months”,IF(D5<=120,”2 Months+”,IF(D5<=180,”4 Months+”,”6 Months+”)))
👉 First, the IF(D5<=60, “Less than 2 Months”…) function checks whether the value of cell D5 is within 60 or not. If the condition is met, the function goes on to return the string “Less than 2 Months”. Otherwise it moves on to the next portion of the formula.
👉 Now if the previous condition is false, the formula moves on to the IF(D5<=120, “2 Months+”,…) portion of the formula. In this section, the function searches whether the value in cell D5 is within 120 now. If the value is indeed within 120, the function returns the string “2 Months+” otherwise it moves on to the next part of the formula.
👉 Finally, if all of the conditions above were false, the formula moves on to the IF(D5<=240, “4 Months+”, “6 Months+”) portion. In this portion, the IF function checks for the condition of whether the value in cell D5 is within 240 or not. If the condition is true, the function returns the string “4 Months”. While it returns “6 Months” when the condition is false.
3. Aging Buckets for Expiration Days
Moving on to our next example, we are using the following dataset.
The dataset contains expiration dates for different products. In this example, we are going to categorize these products into different aging buckets depending on the number of days after expiration. Follow along for a detailed guide on how to use the formula consisting of the nested IF formula for aging buckets in Excel for this example.
Steps:
- First of all, select cell E5.
- Then write down the following formula in the cell.
=IF(D5<=15,"Less than 15 days",IF(D5<30,"Between 15 and 30 days","More than 30 days"))
- After that, press Enter on your keyboard. As a result, you will have the aging bucket for the first entry of the dataset.
- Now select the cell again. After that, click and drag the fill handle icon to the end of the column to fill out the rest of the cell with this formula.
Owing to these steps, we will have the aging buckets for all of the entries in the dataset.
🔍 Breakdown of the Formula
IF(D5<=15,”Less than 15 days”,IF(D5<30,”Between 15 and 30 days”,”More than 30 days”))
👉 First, the formula checks for the condition in IF(D5<=15, “Less than 15 days”,…) is true or not. If the formula is true it returns the string in the second argument. When the condition is false, it moves on to the next portion of the formula.
👉 If the above condition is not met, the formula moves on to the IF(D5<30, “Between 15 and 30 days”, “More than 30 days”) portion where it checks for a new condition. Next, It returns the string “Between 15 and 30 days” if this new condition is met. Otherwise, the returns “More than 30 days.
Read More: Use Stock Ageing Analysis Formula in Excel
Download Practice Workbook
You can download the workbook containing all the examples used for demonstration in this article from the link below. It contains all the examples on different worksheets. Download and try it yourself while you go through the article.
Conclusion
These were different examples of how you can use the IF formula for aging buckets in Excel. There can be many more like this one in our everyday usage. Hopefully, you are now confident in using the IF formula for aging buckets. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.
Related Articles
- Ageing Formula with Current Date in Excel
- How to Calculate Days with Aging Formula in Excel
- How to Use Ageing Formula in Excel Excluding Weekends
- Aging Formula in Excel Using IF
- How to Use Multiple If Conditions in Excel for Aging
- How to Do Aging Analysis in Excel
- How to Calculate Aging of Accounts Receivable in Excel
- Applying Formula to Find Defect Aging in Excel
- How to Make an Accelerated Aging Calculator in Excel
Hello Sir !
Thanks for the detailed explanation. This has helped me to create age buckets for one of my work activities. But I have a situation. I also have age in negative numbers -1, -2, -3, etc. I need to create to 2 age groups D-30 and D-60. Is it possible using IF formula. Could you please assist.
Really appreciate your help !
Hello MANESH KURKUTE,
Thanks for your response. Yes, we can certainly create the age bucket with negative numbers too. For this, just apply the following formula to have age 0 to -30 to the D-30 Days and -30 to -60 to the D-60 Days.
=IF(C5<= -30,”D-60 Days”,IF(C5<0,”D-30 Days”,IF(C5<=30,”1-30 Days”,IF(C5<=60,”31-60 Days”,IF(C5<=90,”61-90 Days”,”>90 Days”)))))
Regards,
NAIMUL HASAN ARIF
Exceldemy