Md. Araf Bin Jayed
As machines are not a hundred percent error-proof, sometimes errors can be found in the final result. As a result, users have to find those errors or defects and solve them. Defect age is related to this situation. This is the difference between the time from which the error has been solved and the time the users found the error. Measuring this time correctly is important in terms of production. In this article, I will show you how to apply the defect aging formula in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
Step-by-Step Procedures to Calculate Defect Aging by Applying Formula in Excel
In this article, you will see the step-by-step procedures to apply the defect aging formula in Excel. To apply this formula, I will combine the IF function and the TODAY function. Also, in the later sections, I will apply the aging formula in Excel using the IF function. Then, I will apply the aging formula in Excel for days and months.
To apply the defect aging formula, I will use the following sample data set. I have some dates for finding defects and solving them in some random machines.
Step 1: Preparing Data Set
In our first step, I will prepare the data set for applying the aging defect formula. To do that,
- Firstly, make an extra column along the main data set under column E.
- Then, name the column Defect Aging.
- Here, I will apply the formula to calculate the result in days.
Read More: How to Calculate Days with Aging Formula in Excel
Step 2: Applying Formula to Calculate Defect Aging
In the second step, I will apply the formula for calculating defect aging to the newly created column in the previous step. For that,
- First of all, use the following combination formula of the IF function and the TODAY function in cell E5.
=IF(D5<>"",D5-C5,TODAY()-C5)
Formula Breakdown
=IF(D5<>””,D5-C5,TODAY()-C5)
- Firstly, the IF function will see if cell D5 is empty or not.
- Secondly, if it finds the cell value then, it will subtract it from the cell value of D5 to calculate the date.
- Moreover, if it does not find any value in C5, then it will delete the current date from D5.
- Then, press Enter to get the desired result in cell E5, showing defect aging for the first machine, that is 10 days.
- Thirdly, use the AutoFill feature, to drag the same formula for the lower cells of that column.
Read More: How to Do Aging Analysis in Excel (with Quick Steps)
Step 3: Showing Final Result
In the last step of my procedure, I will show the final result after applying the function in all the cells of the defect aging column.
- After applying AutoFill, the formula will get adjusted for the lower cells from cell E6:E8.
- Then, you can see the values of defect aging for all the machines.
Read More: How to Use IF Formula for Aging Buckets in Excel (3 Suitable Examples)
Applying Aging Formula in Excel Using IF Function
In this section, I will show you how to apply the aging formula in Excel using the IF function. Here, I will apply the IF function in the nested format. I will apply the nested IF formula to show the age group of some random people of different ages. Go through the following steps for a better understanding.
Step 1:
- Firstly, take the following data set that contains the names and ages of some random people in columns B and C respectively.
- Then, to know the age groups, insert the following nested IF formula in cell D5.
=IF(C5<16,"Children",IF(C5<=25,"Young Adults",IF(C5<35,"Middle-aged Adults","Senior Citizen")))
Formula Breakdown
=IF(C5<16,”Children”,IF(C5<=25,”Young Adults”,IF(C5<35,”Middle-aged Adults”,”Senior Citizen”)))
- IF(C5<16,“Children” …): This formula denotes if the value of cell C5 is less than 16 which means if the age is less than 16, then it will return a string “Children”. It actually shows that people with the age below 16 will be in the Children group.
- IF(C5<=25,“Young Adults” ….): This part means if the value of cell C5 doesn’t meet the first condition, it will automatically consider this second condition. The second condition demonstrates if the value of cell C5 is less than or equal to 25, it will return a string called “Young Adults”. This actually denotes the age limit of less than or equal to 25 will be in the Young Adults group.
- IF(C5<35,“Middle-aged Adults” ….): If the value of cell C5 doesn’t match the above two criteria then, it will go through this condition. This formula says if the value of C5 is less than 35, it will return a string called “Middle-aged Adults”. This actually demonstrates the age limit of less than 35 will be in the Middle-aged group.
- Finally, if the value of C5 doesn’t match any of the above criteria, then it will automatically return a string “Senior Citizen”.So, if the age limit of C5 is above 35, it will be in the Senior Citizen group.
Step 2:
- Secondly, press Enter and see the age group for Andrew in cell D5 which is Young Adults.
- Consequently, drag the same formula to the lower cells of that column by using Fill Handle.
Read More: Aging Formula in Excel Using IF (4 Suitable Examples)
Applying Aging Formula in Excel for Days and Months
In the last section of this article, I will demonstrate the procedure to apply the aging formula for days and months in Excel. Here, I will also use a combination of some Excel functions that include the INT, YEARFRAC, and TODAY functions. The steps to apply this formula are as follows.
Step 1:
- First of all, to determine one’s age in days and months, I will use the following sample data set.
- Here, I have the names and dates of birth of some random people.
- Then, to calculate the age of the first person in days type the following combination formula.
=INT((YEARFRAC(C5,TODAY(),1)*365))
Formula Breakdown
=INT((YEARFRAC(C5,TODAY(),1)*365))
- YEARFRAC(C5,TODAY(),1): The YEARFRAC function will count the difference in the year between cell C5 and the current year in fractions.
- (YEARFRAC(C5,TODAY(),1)*365): Then, I will multiply the year by 365 to convert it into days.
- INT((YEARFRAC(C5,TODAY(),1)*365)): Lastly, the INT function will turn the fraction value into its nearest integer.
Step 2:
- Secondly, hit Enter to see the desired age in days.
- Then, with the help of AutoFill use the same formula in the following cells of the column.
Step 3:
- Thirdly, to apply the aging formula for months, in cell E5 use the following combination formula.
- Here, the applied formula is the same as the previous one, but in the place of 365, I will multiply the year value by 12 to convert it into months.
=INT((YEARFRAC(C5,TODAY(),1)*12))
Step 4:
- Finally, after pressing Enter and dragging Fill Handle, you will get the desired ages in months for all the people.
Read More: How to Use Multiple If Conditions in Excel for Aging (5 Methods)
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to apply the defect aging in Excel. Please share any further queries or recommendations with us in the comments section below.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.
