Excel - Uncover Vehicle Repair Cost Secrets - Episode 2627

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.