The aging phenomenon is very common in our day-to-day purposes. To differentiate several aging groups into various categories, we use the aging formula in Excel. It can be useful for various companies’ day-to-day purposes also. This article will mainly focus on how to illustrate the aging formula in Excel using the IF function. I hope you find this article very informative and gather lots of knowledge regarding the aging formula in Excel.
Aging Formula in Excel Using IF: 4 Suitable Examples
To demonstrate the aging formula in Excel using the IF function, we have found four different examples through which we can give you a clear view of this matter. These examples include the IF formula, nested IF, and combining IF with the AND function.
1. Using Nested IF Formula
Our first Example is based on the nested IF formula. Here, we take a dataset that includes several names and ages.
We want to find out the possible team of the person based on the age condition. For that reason, we use the nested IF formula to apply several IF formulas. To do this Example, we need to follow the steps carefully.
Steps
- At first, select cell D5.
- Then, write down the following formula in the formula box
=IF(C5<20,"Avengers",IF(C5<40,"Panthers",IF(C5<60,"Lions","Warriors")))
- Press Enter to apply the formula.
- Then, drag the Fill handle icon down the column to cell D11.
🔎 Breakdown of the Formula
IF(C5<20,”Avengers”,IF(C5<40,”Panthers”,IF(C5<60,”Lions”,”Warriors”)))
⇒ IF(C5<20,”Avengers” …): This formula denotes if the value of cell C5 is less than 20 which means if age is less than 20, it will return a string “Avengers”. It actually demonstrates the age below 20 will be in the Avengers group. If it doesn’t meet the condition, it will automatically go to the next condition.
⇒ IF(C5<40,”Panthers” ….): This formula denotes if the value of cell C5 doesn’t meet the above condition, it will automatically consider this second condition. The second condition demonstrates if the value of cell C5 is less than 40, it will return a string called “Panthers”. This actually demonstrates the age limit of less than 40 will be in the Panthers group. If it doesn’t meet the condition, it will automatically go to the next condition.
⇒ When the value of cell C5 doesn’t match the first two conditions, it will automatically move to the third condition.
⇒ IF(C5<60,”Lions” ….): This formula denotes if the value of cell C5 is less than 60, it will return a string called “Lions”. This actually demonstrates the age limit of less than 40 will be in the Lions group. If it doesn’t meet the condition, it will automatically go to the next condition.
⇒ Finally, if the value of C5 doesn’t meet any of the conditions above, it will automatically return a string “Warriors”. Actually, it means if the age limit of cell C5 is above 60, it will be in the Warriors group.
2. Aging Formula for Number of Days Worked
Our next example is based on the IF function to determine the aging bucket for the number of days worked. To establish this example, we take a dataset that consists of some employees and their joined dates. We will use the TODAY function here.
First, we find the number of days worked by a certain employee in that company. Then, we define them in a certain group by using the IF function. Follow the following steps carefully.
Steps
- At first, select cell D5.
- Then, write down the following formula in the formula box
=TODAY()-C5
- After that, press Enter to apply the formula.
- Then, drag the Fill handle icon down the column to cell D11.
🔎 Breakdown of the Formula
TODAY()-C5
This formula denotes the difference between today’s day and the date of cell C5. Then, it returns a number value of the worked days of the employee.
- Next, select cell E5.
- Then, write down the following formula
=IF(D5<=60,"Less than 2 Months",IF(D5<=120,"2 Months+",IF(D5<=180,"4 Months+","6 Months+")))
- After that, press Enter to apply the formula.
- Then, drag the Fill handle icon down the column to cell E11.
🔎 Breakdown of the Formula
IF(D5<=60,”Less than 2 Months”,IF(D5<=120,”2 Months+”,IF(D5<=180,”4 Months+”,”6 Months+”)))
⇒ IF(D5<=60,”Less than 2 Months”….): It demonstrates when the value of cell D5 is less than or equal to 60, it will return the string “Less than 2Months”.That means if the employee worked less than equal to 60 days, it will automatically go to the Less than 2 Months section. If the value of cell D5 doesn’t meet the condition, it will automatically go to the next condition.
⇒ IF(D5<=120,”2 Months+”….): When the cell value doesn’t meet the above condition, it will come to this condition to look up whether it meets this one or not. This formula denotes if the value of cell D5 is less than or equal to 120, it will return the string “2 Months+”. That means if the employee worked less than equal to 120 days, it will automatically go to the 2 Months+ sections. If the value of cell D5 doesn’t meet the condition, it will automatically go to the next condition.
⇒ IF(D5<=180,”4 Months+”….): This formula denotes if the value of cell D5 is less than or equal to 180, it will return a string called “4 Months+”. This actually demonstrates. If it doesn’t meet the condition, it will automatically go to the next condition.
⇒ Finally, if the value of C5 doesn’t meet any of the conditions above, it will automatically return a string “6 Months+”. Actually, it means if the age limit of cell C5 is above 180, it will be in the 6 Months + group.
3. Using IF Formula to Determine Expiration Days
Our next example is basically based on the expiration days of the products. We want to create an age bucket after the date of expiration using the IF function. To express this example, we take a dataset that includes product no. and their expiration date. We will use the TODAY function here also.
We want to calculate the days after expiration and the age bucket using the IF formula. Follow the steps properly.
Steps
- At first, select cell D5.
- Then, write down the following formula in the formula box
=TODAY()-C5
- After that, press Enter to apply the formula.
- Then, drag the Fill handle icon down the column to cell D11.
🔎 Breakdown of the Formula
TODAY()-C5
This formula denotes the difference between today’s day and the date of cell C5. Then, it returns a number value of the worked days of the employee.
- Next, select cell E5.
- Then, write down the following formula
=IF(D5<=15,"Less than 15 days",IF(D5<30,"Between 15 and 30 days","More than 30 days"))
- After that, press Enter to apply the formula.
- Then, drag the Fill handle icon down the column to cell E11.
🔎 Breakdown of the Formula
IF(D5<=15,”Less than 15 days”,IF(D5<30,”Between 15 and 30 days”,”More than 30 days”))
⇒ IF(D5<=15,”Less than 15 days”….): It demonstrates when the value of cell D5 is less than or equal to 15, it will return the string “Less than 15 days”.That means if the product passes its expiration date less than or equal to 15 days, it will automatically go to the Less than 15 days section. If the value of cell D5 doesn’t meet the condition, it will automatically go to the next condition.
⇒ IF(D5<30,”Between 15 and 30 days”….): When the cell value doesn’t meet the above condition, it will come to this condition to look up whether they meet this one or not. This formula denotes that if the value of cell D5 is less than 30, it will return the string “Between 15 and 30 days”. That means if the product passes its expiration date of fewer than 30 days, it will automatically go to the Between 15 and 30 days sections. If the value of cell D5 doesn’t meet the condition, it will automatically go to the next condition.
⇒ Finally, if the value of C5 doesn’t meet any of the conditions above, it will automatically return a string “More than 30 days”. Actually, it means if the expiration date of the product is above 30 days, it will be in the More than 30 days group.
4. Combining IF with AND Function
Our last method is mainly focused on the aging formula with the combination of IF and AND functions. To show this example, we take a dataset including invoice no., invoice date, and amount. We will use the TODAY function here also.
At first, we want to calculate the days past. After that, we will try to establish an aging report by combining IF with AND functions. Follow the following steps carefully.
Steps
- At first, select cell E5.
- Write down the following formula to calculate the days past.
=TODAY()-C5
- Then, press Enter to apply the formula.
- After that, drag the Fill Handle icon under the column up to cell E11.
- Next, we consider the condition of any invoice that appears below 30 days. To know this, first of all, we need to select cell F5.
- Write down the following formula in the formula box.
=IF(E5<=30,D5,0)
- Then, press Enter to apply the formula. As the days past are more than 30, so, we don’t get an invoice on that day for the specific product.
- After that, drag the Fill handle icon down the column. You will see only one invoice will appear in that section.
🔎 Breakdown of the Formula
IF(E5<=30,D5,0)
This formula denotes that if the value of cell E5 is less than or equal to 30, then it will return the value of cell D5 otherwise it returns zero. It actually means if the days past is less than or equal to 30, it returns the invoice amount otherwise it returns 0.
- Next, we turn our focus to the invoice between 31 to 60 days.
- At first, select cell G5.
- Write down the following formula in the formula box.
=IF(AND(E5>30,E5<=60),D5,0)
- Then, press Enter to apply the formula.
- After that, drag the Fill handle icon under the column.
🔎 Breakdown of the Formula
IF(AND(E5>30,E5<=60),D5,0)
⇒ AND(E5>30,E5<=60): This formula denotes two different conditions. One is when the value of cell E5 is greater than 30 and the other is when the value is less than 60. The AND function basically makes a condition when the value of cell E5 is greater than 30 but less than 60. To combine two different conditions, we utilize the AND function.
⇒ IF(AND(E5>30,E5<=60),D5,0): It demonstrates when the condition is met, we will get the return value as invoice amount otherwise we get zero.
- For the 61-90 days section, select cell H5.
- Then, write down the following formula in the formula box.
=IF(AND(E5>60,E5<=90),D5,0)
- Press Enter to apply the formula.
- After that, drag the Fill Handle icon down the column.
- Our final case is for above 90 days passed.
- At first, select cell I5.
- Then, Write down the following formula in the formula box.
=IF(E5>90,D5,0)
- Press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column.
🔎 Breakdown of the Formula
IF(E5>90,D5,0)
This formula denotes that if the value of cell E5 is greater than 90, then it will return the value of cell D5 otherwise it returns zero. It actually means if the days passed are greater than 90, it returns the invoice amount otherwise it returns 0.
Download Practice Workbook
Download this practice workbook
Conclusion
We have shown four different examples to demonstrate the aging formula in Excel using IF. All of the examples are fairly easy to understand. In practical use, you can use the aging formula more frequently. I hope you find this article very informative for your future purposes.
<< Go Back to Ageing | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
