Excel - Uncover Vehicle Repair Cost Secrets - Episode 2627

У вашего броузера проблема в совместимости с HTML5

Microsoft Excel Tutorial: Calculate Average Cost per Year in Excel.

Welcome to episode 2627 of MrExcel’s YouTube channel! In this video, we will be discussing a question sent in by Derek about calculating annual repair costs by age of vehicle. Derek has a fleet of vehicles that he needs to account for and wants to know the average cost to maintain and repair each vehicle. So, in this episode, we will uncover the secrets to calculating these costs using Excel.

To begin, we will be working with a data set similar to Derek’s, with a list of all the repairs made on the fleet including the vehicle, date, and cost. Our first step is to determine the date each vehicle was placed in service. This can be done by using the MINIFS function to find the earliest service date for each vehicle. We will also approximate the date in service for new vehicles by subtracting a certain number of days from the first service date.

Next, we will use the VLOOKUP function to retrieve the date placed in service for each vehicle. Then, using the DATEDIF function, we will calculate the age of the vehicle at the time of each repair. This will give us a table showing the vehicle and its age at the time of each repair.

To get the total spent in each year for each vehicle, we will create a pivot table. This will allow us to see the average cost to repair each vehicle based on its age. It is interesting to note that there is a period where the average cost decreases, which could be due to a small sample size or fake data. However, with real data, this method can provide a good approximation of the average cost to repair a vehicle based on its age.

I want to thank Derek for sending in this great question and I hope this video has helped you uncover the secrets to calculating vehicle repair costs using Excel. If you enjoyed this video, please don’t forget to like, subscribe, and ring the bell to be notified of future episodes. And as always, feel free to leave any questions or comments in the section below. Thank you for watching and we’ll see you next time for another netcast from MrExcel.

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/

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