Mileage Countdown Alert

[MIKEPRICE]

Can anybody help me, i want to enter a weekly mileage of a vehicle into a spreadsheet which in turn will calculate a countdown based on a service mileage requirement . i need a single cell to keep updating everytime a new weekly cell is entered for that specific vehicle. I can then conditional format the running total to alert on a separate dashboard sheet when the criteria dictates

[JohnTopley]

Try in A1

=(Service_Interval -SUM(B1:B50))

Where Service_Interval is a value (20,000 miles) or a cell containing 20000

B1:B50 are your weekly mileage figures

You can then CF when value in A1 is less than your desired figure e.g 100 miles (to next service)

Does this help?

[dosydos]

Weekly Mileage	Goal mileage
40,000	75,000
	Please Login or Register to view this content.

[MIKEPRICE]

Hi John thanks for a prompt response, sorry to thick but are you saying copy the formula =(Service_Interval -SUM(B1:B50)) in my case the mileage interval is 18500 so =(Service_18500 -SUM(B1:B50)) i guess i can Ctrl and select different multiple cells as my weekly entry is shown in a calendar

[JohnTopley]

If it is a calendar that is gong to more difficulty as the cells are "random". Why not make life easier by recording them in the way I described? But if your are happy to copy/paste then fine. I would simply added (type) them in both locations

Can you post your actual Excel file with calendar?

formula would be =(18500-SUM(B1:B50))

Most service intervals are also time-related i.e once a year or mileage (which ever is first). Do you need to consider this?

[MIKEPRICE]

Hi John, i would prefer if i could show the odometer reading rather than the weekly total

[JohnTopley]

You still going to have to calculate weekly mileage or have a calculation like:

Initial Odometer reading +18500 - Current Odometer reading

Initial odometer reading = Reading at last service.

Current Odometer reading = Latest weekly reading

[MIKEPRICE]

Hi John please find enclosed snippit the first table takes the weekly mileage from a calendar then the table below simply takes the figure from the previous week to show how many miles for that week.

End Mileage 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
FJ59 GKC 188255 188724 188744 189289 190156 190215 190539 191130 191388 191985 192538 193558 193766 0 0 0 0 0
FJ11 DBX 124729 125425 125985 126384 126824 127044 127288 127595 127860 128372 128758 129157 129558 0 0 0 0 0
KS12 KUT 164219 165083 165682 166202 167416 168387 169883 171374 172121 173000 173992 174387 175094 0 0 0 0 0
MX14 AZD 44716 45000 45500 46333 46620 47690 48200 48813 49845 50104 50552 51373 52309 0 0 0 0 0


Weekly Mileage 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
FJ59 GKC 469 20 545 867 59 324 591 258 597 553 1020 208 -193766 0 0 0 0 0
FJ11 DBX 696 560 399 440 220 244 307 265 512 386 399 401 -129558 0 0 0 0 0
KS12 KUT 864 599 520 1214 971 1496 1491 747 879 992 395 707 -175094 0 0 0 0 0
MX14 AZD 284 500 833 287 1070 510 613 1032 259 448 821 936 -52309 0 0 0 0 0

[JohnTopley]

Based on a SINGLE common Service interval in C8

=$C$8-SUM(B11:S11)

Calculate weeks to service based on weekly average mileage to date

=INT($U11/AVERAGEIF(B11:S11,"<>0"))

See attached.

[BlindAlley]

I have added two columns, 1 for calculating the average weekly mileage and 2 using that average to fire an alert cell with CF.

[JohnTopley]

Average mileage should AVERAGIF to ignore 0 mileage weeks i.e future dates.


@Mike,
If there a week where a vehicle is not used I.e. 0 mileage, do you want to ignore that week(s) in calculating any average?

[MIKEPRICE]

Hi John, thanks again for all your help, i have a couple of further questions whats happens once the target has been reached (ie 18000, in other words what would be the best way to reset itself to start recalculating the next service, also one of the vehicles has to go in every 6 weeks or a certain mileage more often the date is the foremost argument, can you add a timely formula?

[JohnTopley]

Mike,
You need to define how your "real" data is to presented and updated before I attempt any formulae.

For each vehicle:

Registration number
Service interval: weeks
Service Interval: miles
Date of Last service (a DATE not week number)
Last service Odometer reading
..... other data ???

Week numbers 1 to 52 (assuming maximum service interval of 1 year) can be relative to the "Last Service Date"

After a service, reset all mileage to zero ???

[MIKEPRICE]

Hi John, sorry if i am making this complicated , i take your point. Basically the 4 vehicles on the spreadsheet are as follows:
Registration Service Interval
FJ59 GKC 18000 we will always use the mileage rather than time on these due to the high volume
FJ11 DBX 18000 " " "
KS12 KUT 18000 " " "
MX14 AZD this vehicle is leased and has to go in every 6 weeks or as we have a 50,000 per annum agreement or 5769 miles which ever comes first

So i guess we just formulate accordingly as the rows will not change. regarding last service i guess we create a box that manually gets updated from which the formula looks to
and the end of all this as long as the data is displayed across the spreadsheet i have created a Dashboard which effectively provides a simplistic overview taking the bullet point results out of the spreadsheet.
If i have forgotten anything i apologize. I guess at the end of the day all i am looking at is manually entering a weekly mileage into a spreadsheet then all the other stuff like MOT/ Tax/ will pull through also, so at the end the Dashboard provides an simple overview of the fleet. In other words the last column Service Required will flow through onto the Dashboard.

[JohnTopley]

Can you please post a sample of your expected "real world" file.

[MIKEPRICE]

Hi John i think i have attached 2 files i have clicked on manage attachments and selected upload, is this correct? The Dashboard normally would show a tick cross or exclamation mark to advise if any action is required, this would come from conditional formatting from the other sheets. The Calendar is where i maintain the mileage and services.I realise the code in BC column is one cell to the right out needs to be AI not AJ

[JohnTopley]

I have added fields to the Calendar:

Date of Last Service

Mileage since Last Service

Next service date (for yearly add 365 to Last service date and for your hire vehicle 42 days)

Changed Service Alert as below

=IF(OR($BD17<=($BG17*2),TODAY()+28>$BE17),"SERVICE REQUIRED","")

Checks whether "Miles to next service" < 2 x "Average Weekly Mileage" OR "Next Service Date" is < 28 days from TODAY. Change 28 to suit.

As per my previous post, I would suggest a 52 week year (weeks labelled 1 to 52) based on the "Last service Date" so Week 1 is the relevant mileage for a given vehicle in the first week after its service.

When a service has been completed set weeks 1 to 52 to 0 for the vehicle.

See table in row 34 onwards