Sep 12 2024 03:31 PM - edited Sep 12 2024 03:32 PM
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?
Sep 13 2024 01:47 AM
SolutionTo 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:
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:
=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:
Example:
Assume Truck 1 in row 2 has the following cumulative kilometers across months:
Truck 1 | 5000 | 15000 | 22000 | 33000 | 60500 | 121000 | ...
Summary:
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.
Sep 13 2024 05:17 AM
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.
Sep 13 2024 08:57 AM
Sep 13 2024 09:40 AM
Sep 13 2024 11:56 AM
Sep 13 2024 12:04 PM
Sep 13 2024 01:47 AM
SolutionTo 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:
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:
=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:
Example:
Assume Truck 1 in row 2 has the following cumulative kilometers across months:
Truck 1 | 5000 | 15000 | 22000 | 33000 | 60500 | 121000 | ...
Summary:
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.