Microsoft Excel Tutorial: Calculate Average Cost per Year in Excel.
Derek has a fleet of vehicles and a list of all repairs made in the last 20 years. He wants to figure out how the annual repair and maintenance costs change as the vehicle ages.
In this video, MrExcel discusses how to calculate annual repair costs based on the age of a vehicle. He provides a data set of repairs made on a fleet of vehicles and explains how to determine the first date each vehicle was placed in service. He suggests using an approximation for vehicles without records by subtracting a certain number of days from the first service date. MrExcel then demonstrates how to calculate the age of each repair using the DATEDIF function. He suggests using a pivot table to calculate the total spent in each year for each vehicle. He also mentions the use of the AVERAGE function to determine the average cost of repairs based on the age of the vehicle. MrExcel concludes by mentioning that the data used in the demonstration is fictional and thanks the person who sent in the question.
To download the workbook from today: https://www.mrexcel.com/youtube/lfn5FGRA5wg/
This video includes the DATEDIF function in Excel.
Creating a pivot table in Excel.
VLOOKUP in Excel.
Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
#excel
#microsoft
#exceltips
#microsoftexcel
#exceltricks
#walkthrough
#pivottable
#pivot_table
#excelpivot
#excelpivottablestutorial
This video answers these common search terms:
how to calculate yearly average in excel
how to get monthly average excel
how to average monthly data in excel
how to find daily average in excel
how to calculate daily average in excel
how to calculate daily average in excel formula
how to calculate average daily sales in excel
how to calculate average age in excel formula
how do you calculate average age in excel
how to average multiple columns in excel
how to average multiple data in excel
how to calculate average in excel
how does excel calculate average
how to use minifs function in excel
how to use minifs in excel
how to use minifs excel
excel formula to find the earliest date
how to use datediff in excel
how to do a datedif function excel
how to use datedif function excel
how to use datedif in excel
how to use the datedif excel function
where is datedif in excel
where is datedif in excel
how to use excel formula datedif
how to use a datedif function in excel
how to use datedif function in excel
how to use the datedif function in excel
Table of Contents
(0:00) Problem Statement: Average cost to maintain vehicle based on age of vehicle
(0:22) Data has Vehicle ID, Repair Date, Repair Cost
(0:50) Find earliest service date per vehicle using MINIFS
(1:50) Approximate Date Placed in Service from First Repair Date
(2:32) Use VLOOKUP in original data to get In-Service date
(3:05) Use DATEDIF to find age in Years in Excel
(3:57) Building a pivot table
(4:13) My pivot table defaults are wrong for this analysis
(4:31) Displaying blank cells in a pivot table for AVERAGE function
(5:00) AVERAGE function ignores blank cells
(5:50) Clicking Like really helps with the algorithm