To complete an analytical review of the depreciation of your company’s assets, you can calculate the average useful life of these assets. The average useful life, which is a measure of conditional central tendency, can be calculated using the AVERAGEIF function. Central tendency is the middle value or a typical value within a data set. It has the following arguments;
- Range: One or more cells to average, including numbers or names, arrays, or references that contain numbers.
- Criteria: The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.
- Average range: The actual set of cells to average.
You are welcome to download the asset register template to practice.
Applies to: Microsoft Excel 2007, 2010 and 2013.
1. Select the WCoast Asset Reg worksheet.
2. Select cell B57.
3. From the Formulas tab, in the Function Library, select More Functions.
4. Select Statistical, select AVERAGEIF.
5. Enter the following function arguments within Range, Criteria and Average_range function.
- Asset_Category refers to the data range C8:C41.
- Check_Useful_life refers to the data range J8:J41
Note: Defined names have been used. To define a name:
- Highlight the respective data range.
- Place the cursor in the name box.
- Press Enter.
6. Select OK.
7. Copy the formula down to cell B59.
The total depreciation can now be compared to the actual depreciation as per trial balance. Further audit work can be performed if it is concluded that the difference is not acceptable.