How to Illustrate the Aging Formula in Excel using the IF Function (4 Methods)

Breakdown of the Formula

IF(C5<20,”Avengers”,IF(C5<40,”Panthers”,IF(C5<60,”Lions”,”Warriors”)))

⇒ IF(C5<20,”Avengers” …): If the value in cell C5 is less than 20 (indicating an age below 20), it will return “Avengers.” Otherwise, it moves to the next condition.

⇒ IF(C5<40,”Panthers” ….): If the value in cell C5 is less than 40 (but not less than 20), it will return “Panthers.” Otherwise, it moves 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” ….): If the value in cell C5 is less than 60 (but not less than 40), it will return “Lions.” Otherwise, it defaults to “Warriors” for ages above 60.

⇒ 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.

Breakdown of the Formula 

TODAY()-C5

This formula calculates the difference between today’s date and the date in cell C5, giving us the number of days worked by the employee.

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.

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.

Breakdown of the Formula

TODAY()-C5

This formula calculates the difference between today’s date and the date in cell C5, giving us the number of days after the product’s expiration.

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”….): If the value in cell D5 is less than or equal to 15, it will return “Less than 15 days.” This means that if the product has passed its expiration date by 15 days or less, it falls into this category. If the value of cell D5 doesn’t meet this condition, it moves to the next condition.

⇒ IF(D5<30,”Between 15 and 30 days”….): If the value in cell D5 is less than 30 (but not less than 15), it will return “Between 15 and 30 days.” This category covers products with expiration dates between 15 and 30 days. If the value of cell D5 doesn’t meet this condition, it defaults to “More than 30 days” for products with expiration dates beyond 30 days.

⇒ 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.

Breakdown of the Formula

IF(E5<=30,D5,0)

If the value in cell E5 is less than or equal to 30, it returns the invoice amount (cell D5); otherwise, it returns 0.

Breakdown of the Formula

IF(AND(E5>30,E5<=60),D5,0)

⇒ AND(E5>30,E5<=60): This condition checks if the value of cell E5 is greater than 30 and less than or equal to 60.

⇒ IF(AND(E5>30,E5<=60),D5,0): If the condition is met, it returns the invoice amount (cell D5); otherwise, it returns 0.

Breakdown of the Formula

IF(E5>90,D5,0)

If the value of cell E5 is greater than 90, it returns the invoice amount (cell D5); otherwise, it returns 0.