I am trying to enter a formula that forecasts a date in the cell taking a maximum number of miles using the average number of miles that month
example - average miles for august is 15000, maximum number of miles allowed is 500,000 - what would the date be?
The excel sheet has a column with the average number of miles, but not the max number of miles.
What do you mean "average miles for august"? Is that the average miles per day for the month?
For your example, what is the starting date? August 1?
You should have a cell someplace with the maximum number of miles, or your formula will have to hard code the number, never a good idea.
If 15,000 is an average daily mileage, then here is the calculation for when you will reach 500,000 starting August 1.
Values as displayed
Underlying formulas
We are taking our current odometer reading and then calculating an average based on the in service date and todays date.
I attached a copy of the sheet I am working in. Trying to get a formula to work in cell I4 that has a date for replacement once the equipment reaches 805000 kms
Seems to me like a basic "distance=rate*time" type of problem you (hopefully) spent some time with back in algebra class.
If the rate is 15000 km per month, and you want to know how many months it takes to get to 0.5E6 km, then solve your d=r*t for t (t=d/r), then substitute
d=500000, r=15000, t=500000/15000=33.333 months
When, as in your sample file, you want to know how many months to go from current odometer reading to an odometer reading of 805000 km
d=805000-currentodometer=I$2-G4, r=currentaverage=H4, t=(805000-currentodometer)/(currentaverage)=(I$2-G4)/H4 [note the mix of relative and absolute references for easy copying].
Am I understanding correctly?
Originally Posted by shg
I am trying to get the projected date. is there a way to do that so that you get a MM/DD/YY format instead of the number of months?
It should be as simple as adding the number of months to the date in column D. I'm not sure how you intend to handle the fractional months, but something like =DATE(YEAR(D4),MONTH(D4)+months,DAY(D4)) will truncate the number months from the above calculation to an integer, then add that many months to the date in column D.
Please try this formula and copy down (the result is a date)Formula:
Please Login or Register to view this content.
Last edited by HansDouwe; 08-31-2022 at 06:16 PM.
Thank you for all of your suggestions - between all of them, I was able to get the sheet to work how I wanted
I want to take the information in the s/s and compile some of the columns into either a table or listing into quarters for the next couple of years. What would you suggest?
Projected Replacement in:
Q1 2023 Q2 2023
Unit# Year Make Unit# Year Make
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks