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: Excel Calculate Average Repair Costs By Age Of Vehicle - Episode 2627 Sample Files - MrExcel Publishing
This video includes the DATEDIF function in Excel.
Creating a pivot table in Excel.
VLOOKUP in Excel.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
#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
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: Excel Calculate Average Repair Costs By Age Of Vehicle - Episode 2627 Sample Files - MrExcel Publishing
This video includes the DATEDIF function in Excel.
Creating a pivot table in Excel.
VLOOKUP in Excel.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
#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
Transcript of the video:
Calculating annual repair costs by age of vehicle. Hey, this is a great question sent in by Derek.
He has a fleet of vehicles that he's accounting for.
He has a list of all the repairs made on the fleet with vehicle, date and cost.
From that once to figure out the average cost to maintain and repair the vehicle.
So a 10-year-old truck, does it cost more than a three-year-old truck?
So here's a data set, similar to Derek's data. I've changed a lot of information.
We have the service description, the vehicle, the date that the service happened.
So this repair happened on this date.
It goes way back, I was surprised, all the way to 2004. I said, "Really?
You have a vehicle that's almost 20 years old?" He's like, "Yeah, it just keeps working forever".
But then you can see there's other vehicles here that have been placed out of service.
So vehicle three, vehicle four, and so on.
All right, so now, if this is my data, the very first thing I want to know is for each of these vehicles, when was it placed in service?
If we have records, I can go back and look for that, but if I don't have records, I could probably come up with a pretty good approximation of it.
So let's get a unique list of vehicles here.
And then for each vehicle, what was the first date that it came in for service or maintenance? So MINIFS - the MINIFS function.
For the minimum range, I'm going to use service date.
So control, shift, down arrow there. I'll press F4 comma.
And then next thing is the criteria range.
That's looking through all the vehicle information. Control, shift, down arrow there.
Press F4, comma.
And then finally we're looking to see if it's equal to V-001, close paren.
Now this is going to show the right answer in the wrong format. I've already pre-formatted this as a date.
But you're most likely going to get a serial number.
So you have to come here and choose short date. Double click to copy that down.
All right, now this next part, this is where we're going to do a little trying to approximate something. Think about a new vehicle.
If they're really doing a good job maintaining it at 5000 miles, it's going to come in for its first oil change or whatever the manufacturer recommends.
So what is that, six months or something like that?
So we could approximate the date in service.
As this first service date minus, I don't know, like 90 days or 120 days.
Again, you'll have to format that as a date to have that work. In a perfect world, there's 57 vehicles.
You have the summer intern go look up the in-service date for all of these and get real data there. But if we don't have time for that.
The board meeting is in an hour.
We just need an approximation, this is going to be pretty close.
All right, so now what I have is I have a table showing me for each vehicle what the date placed in service was.
We'll get that back with either an XLOOKUP or a VLOOKUP.
Let's do VLOOKUP, it might work with more versions of Excel.
So we're looking up A2 in this range right here. Control, shift down, control, shift right.
I'll press F4 to lock that down, and then what do we want?
We want the third column and exact matches only.
Again, you might have to format that as a date if you get a serial number instead.
All right, so now we have the date placed in service. And the date that the maintenance happened.
So to get the age of that.
And by the way, it looks like Derek has nothing with an age of zero.
So in other words, it's the first year of the vehicle being in service. So we're going to use datedif.
Great function. It's so old.
Microsoft knows there's problems with this, with very specific things from January 31st to March 1st. They've just pretended it doesn't exist.
The way it works is you put it in the earlier date, to the later date, and then show that in years.
And that's going to show something in the first year as a zero.
So, I'll add one to it and we get the age like that.
All right, so now for every single repair, this is the vehicle and how old the vehicle was at that point.
So we need to figure out the total spent in each year for each vehicle.
So we're going to do that with a pivot table.
Insert pivot table. We'll let it go to a new worksheet.
Click okay. All right.
Over here on the far right-hand side, we're going to put vehicles down the left-hand side.
The age of vehicle across the top, that's the columns area.
And then the total cost right there.
Great, I am being stung by a change that I made to pivot table defaults. See all these zeros out here?
I overrode the normal behavior of pivot table. So I'm going to have to change that.
I go into Options. And say For Empty Cells, this usually says blank.
I hate blanks.
In this particular case, (first time in 30 years of using Excel).
I actually prefer the blanks because I'm going to use a function called AVERAGE. Which will automatically exclude blanks.
So we have a lot of vehicles here in their first year of service.
Oh, look at that.
That one, any service that happened in that first year happened to be zero, so I mean that's interesting right there.
And sure enough, there is one vehicle that looks like it's been around for 20 years.
And a second and third vehicle that are in their 18th year, which is impressive.
A lot of vehicles in their 16th year.
So for each of these years, I'm going to use the AVERAGE function. Could I build this into the pivot table?
I'd have to use some DAX for that. So I'm just going to go quick and dirty out here.
We're going to do no decimal places. Maybe add a currency symbol, maybe not.
All right, and then for each year, that shows us the average cost repair.
Now, you would expect that this would keep going up.
It's interesting that out here, there's actually a period in years 15, 16, 17 where it comes down.
That could just because we have a small population size.
And it could be that this is fake data.
That's right, I changed the real data using RANDBETWEEN.
So your real data though, that should be a much better approximation of the average cost to repair, based on the age of the vehicle.
I can see that would be very, very interesting.
I want to thank Derek for sending that great question in, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to leave any questions in the comments below.
He has a fleet of vehicles that he's accounting for.
He has a list of all the repairs made on the fleet with vehicle, date and cost.
From that once to figure out the average cost to maintain and repair the vehicle.
So a 10-year-old truck, does it cost more than a three-year-old truck?
So here's a data set, similar to Derek's data. I've changed a lot of information.
We have the service description, the vehicle, the date that the service happened.
So this repair happened on this date.
It goes way back, I was surprised, all the way to 2004. I said, "Really?
You have a vehicle that's almost 20 years old?" He's like, "Yeah, it just keeps working forever".
But then you can see there's other vehicles here that have been placed out of service.
So vehicle three, vehicle four, and so on.
All right, so now, if this is my data, the very first thing I want to know is for each of these vehicles, when was it placed in service?
If we have records, I can go back and look for that, but if I don't have records, I could probably come up with a pretty good approximation of it.
So let's get a unique list of vehicles here.
And then for each vehicle, what was the first date that it came in for service or maintenance? So MINIFS - the MINIFS function.
For the minimum range, I'm going to use service date.
So control, shift, down arrow there. I'll press F4 comma.
And then next thing is the criteria range.
That's looking through all the vehicle information. Control, shift, down arrow there.
Press F4, comma.
And then finally we're looking to see if it's equal to V-001, close paren.
Now this is going to show the right answer in the wrong format. I've already pre-formatted this as a date.
But you're most likely going to get a serial number.
So you have to come here and choose short date. Double click to copy that down.
All right, now this next part, this is where we're going to do a little trying to approximate something. Think about a new vehicle.
If they're really doing a good job maintaining it at 5000 miles, it's going to come in for its first oil change or whatever the manufacturer recommends.
So what is that, six months or something like that?
So we could approximate the date in service.
As this first service date minus, I don't know, like 90 days or 120 days.
Again, you'll have to format that as a date to have that work. In a perfect world, there's 57 vehicles.
You have the summer intern go look up the in-service date for all of these and get real data there. But if we don't have time for that.
The board meeting is in an hour.
We just need an approximation, this is going to be pretty close.
All right, so now what I have is I have a table showing me for each vehicle what the date placed in service was.
We'll get that back with either an XLOOKUP or a VLOOKUP.
Let's do VLOOKUP, it might work with more versions of Excel.
So we're looking up A2 in this range right here. Control, shift down, control, shift right.
I'll press F4 to lock that down, and then what do we want?
We want the third column and exact matches only.
Again, you might have to format that as a date if you get a serial number instead.
All right, so now we have the date placed in service. And the date that the maintenance happened.
So to get the age of that.
And by the way, it looks like Derek has nothing with an age of zero.
So in other words, it's the first year of the vehicle being in service. So we're going to use datedif.
Great function. It's so old.
Microsoft knows there's problems with this, with very specific things from January 31st to March 1st. They've just pretended it doesn't exist.
The way it works is you put it in the earlier date, to the later date, and then show that in years.
And that's going to show something in the first year as a zero.
So, I'll add one to it and we get the age like that.
All right, so now for every single repair, this is the vehicle and how old the vehicle was at that point.
So we need to figure out the total spent in each year for each vehicle.
So we're going to do that with a pivot table.
Insert pivot table. We'll let it go to a new worksheet.
Click okay. All right.
Over here on the far right-hand side, we're going to put vehicles down the left-hand side.
The age of vehicle across the top, that's the columns area.
And then the total cost right there.
Great, I am being stung by a change that I made to pivot table defaults. See all these zeros out here?
I overrode the normal behavior of pivot table. So I'm going to have to change that.
I go into Options. And say For Empty Cells, this usually says blank.
I hate blanks.
In this particular case, (first time in 30 years of using Excel).
I actually prefer the blanks because I'm going to use a function called AVERAGE. Which will automatically exclude blanks.
So we have a lot of vehicles here in their first year of service.
Oh, look at that.
That one, any service that happened in that first year happened to be zero, so I mean that's interesting right there.
And sure enough, there is one vehicle that looks like it's been around for 20 years.
And a second and third vehicle that are in their 18th year, which is impressive.
A lot of vehicles in their 16th year.
So for each of these years, I'm going to use the AVERAGE function. Could I build this into the pivot table?
I'd have to use some DAX for that. So I'm just going to go quick and dirty out here.
We're going to do no decimal places. Maybe add a currency symbol, maybe not.
All right, and then for each year, that shows us the average cost repair.
Now, you would expect that this would keep going up.
It's interesting that out here, there's actually a period in years 15, 16, 17 where it comes down.
That could just because we have a small population size.
And it could be that this is fake data.
That's right, I changed the real data using RANDBETWEEN.
So your real data though, that should be a much better approximation of the average cost to repair, based on the age of the vehicle.
I can see that would be very, very interesting.
I want to thank Derek for sending that great question in, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to leave any questions in the comments below.
