I have 10 trucks that needs a components replacement for every cummaltive 60000km.

[sheik_nadia]

I have 10 trucks that needs a components replacement for every cummaltive 60000km. I have my trucks in column A. Amd cumulative kms by month in the rest of the columns. The trucks needs a components replacement at every cummaltive 60000km. So it becomes 120000kms for the the service and 180000kms for one after that.I want to use a formulae to highlight in which month would we need a component replacement based on every 60000kms but it's cummaltive requiment of 60000kms . What formulaes/conditional formatting or other formulae can I use in my cummalive monthly information per truck to flag or highlight the month that we reach the cummaltive 60000kms requirements?

[#2 · Accepted solution]

@sheik_nadia

To highlight the months where each truck reaches the cumulative distance of every 60,000 km (i.e., 60,000 km, 120,000 km, 180,000 km, etc.), you can achieve this by combining a formula to detect these milestones and using Conditional Formatting to highlight the cells where the cumulative km requirement is met.

Step-by-Step Process:

1. Data Structure Setup:

• Column A: Truck names (e.g., A2 for 10 trucks).

• Columns B onward: Cumulative kilometers by month for each truck (e.g., B2).   

      2. Example layout:

A             | B        | C         | D         | ... | M

--------    |------    |------    |------     |---.|-----

Truck 1 | 5000   | 15000| 22000| ... | 60500

Truck 2 | 10000 | 25000| 31000| ... | 120500

Truck 3 | 20000 | 35000| 60000| ... | 180000

      3. In this case, each row represents the cumulative kilometers for a particular truck by month.

     4. Formula to Identify Replacement Milestones: To detect when a truck reaches every multiple of 60,000 km (i.e., 60,000 km, 120,000 km, 180,000 km, etc.), you can use the following formula for conditional formatting:

Formula:

=MOD(B2,60000)=0

This formula checks if the cumulative kilometers in a particular cell is exactly divisible by 60,000. If it is, the condition will be TRUE, meaning that the truck has hit a replacement milestone in that month.

     5. Applying Conditional Formatting:

1. Select the Range: Select the range where your cumulative kilometers are listed (e.g., B2:M11).
2. Open Conditional Formatting: Go to Home > Conditional Formatting > New Rule.
3. Use a Formula to Determine Which Cells to Format: Choose Use a formula to determine which cells to format.
4. Enter the Formula: Enter the following formula (assuming your data starts in cell B2):

=MOD(B2,60000)=0

     5. Set the Format: Click Format, and choose a color to highlight the cells where the formula is true (i.e., where the truck reaches every multiple of 60,000 km).

     6. Apply: Click OK to apply the conditional formatting rule.

Explanation of the Formula:

• MOD(B2, 60,000) = 0: The MOD function returns the remainder when dividing the cumulative km value by 60,000. If the remainder is 0, it means that the cumulative km is exactly a multiple of 60,000, indicating that the truck needs a component replacement.

Example:

Assume Truck 1 in row 2 has the following cumulative kilometers across months:

Truck 1 | 5000 | 15000 | 22000 | 33000 | 60500 | 121000 | ...

• When the cumulative kilometers hit 60,000 km (between 33,000 km and 60,500 km), the cell with 60,500 km will be highlighted.
• The same will happen for 120,000 km (or the nearest cumulative km that is >= 120,000) and so on.

Summary:

• Use the formula =MOD(B2,60000)=0 in Conditional Formatting to flag the cells where the truck reaches every multiple of 60,000 km.
• This approach will automatically adjust for each truck and each month as the cumulative kilometers increase. The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

@NikolinoDE 

hi,

When I type out the example it and use it as explained it works. Yet I apply the same logic on my calculated template and it doesn't so I just don't get where it is going wrong.

[#4]

Hi
Finally realized the MOD formulae won't work if the cummaltive kms is not a rounded up absolute number (eg. If it is 5232.68kms instead of 5000kms) so just needed to adjust the mod formulae to round up the cummaltive kms and it's perfect. Thank you!

[#5]

I'm glad that you've made progress and were able to implement your solution.

I wish you continued success with Excel!

[#6]

Much appreciated.
The formula then just become =AND(MOD(B2,60000)>=0, MOD(B2,60000)<=5000
use that rule format a highlight conditional formats to highlight in read.
You a 🌟

[#7]

The MOD formula is not ideal when the cumulative kilometers are not perfectly divisible by the service interval, like in your example where the cumulative kilometers are 155,041 instead of 150,000.

To address this, you can use a formula that checks if the cumulative kilometers have exceeded or are close to the next service interval, within a tolerance range (e.g., +/- a few kilometers).

Adjusted Approach

Instead of using MOD, you can use a formula that checks if the cumulative kilometers are within a range of the next service interval. Here's how to do that:

1. Define a Tolerance:

For example, allow a 5,000 km buffer before and after each service interval.



2. Formula to Highlight Near Service Intervals: Use a formula that checks if the cumulative kilometers are within a certain range of the next multiple of your service interval (e.g., 150,000 km).

Example formula:

=AND(MOD(B2, 150000) >= 0, MOD(B2, 150000) <= 5000)

This formula checks if the value in B2 is within 5,000 km of the next service interval (150,000 km).



Steps to Implement:

1. Select Your Data Range:

Select the entire range containing cumulative kilometers.



2. Conditional Formatting:

Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.



3. Enter the Formula: Use the formula for the first cell in your range, and Excel will apply it to the entire selection.

=AND(MOD(B2, 150000) >= 0, MOD(B2, 150000) <= 5000)


4. Set Formatting:

Choose the formatting style (like a color fill) to highlight cells where the cumulative kilometers are near the next service interval.



5. Adjust Tolerance:

If 5,000 km is too large or too small for your service planning, you can adjust the tolerance in the formula (e.g., replace 5000 with another value).




This will highlight any month where the cumulative kilometers are close to the next service point, even if the cumulative kilometers aren't exact multiples of your service interval.