Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In Microsoft Excel 2007 and am attempting to track vehicle mileage

I’m looking for Excel to calculate mileage until service, from two cells, and output 3 different icons or colors to been easily viewed.

So far I have managed to use the Conditional Formatting three color icons and the cell formula =K2-G2 to determine whether a vehicle is early or past mileage for service. (K2 is service mileage, G2 is current mileage)A green arrow appears when it is to early ( i.e 500 current mileage for a planned 1,000 service date outputs a positive 500 and a green check) and a red X when it overdue for service ( i.e. when its current mileage is 1,200 for a planned 1,000 service date, it outputs a negative 200 and a red X)

Now I’m trying to see if I can have another conditional format that will display either the yellow exclamation point or a colored yellow cell when the mileage is within 300 miles of the due date ( i.e when current mileage is 800 for a planned 1,000 mile service) I’m assuming that the formula would look something like =G2>K2-300 or =(G2>K2-300)>K2 since I no longer want the yellow “!” or color to display once it is over the service miles. It should then show the red X

To summarize: I want a green check when Current mileage (G2) Service miles (K2) I want a Yellow “!” or cell color when Current mileage (G2) is 300miles away from Service miles, but not > Service miles.

share|improve this question

1 Answer 1

I recommend creating a formula in the formatted cell that outputs a 1, 2 or 3 then set the formatting based on that:

I'm assuming that your formatting is in its own cell, if not create a new column for the indicators.

In the cell with the formatting enter one of these formulas:


If you want to have the formatting return as blank for blank cells

=IF(G2>K2,1,IF(AND(G2>=(K2-300),G2<=K2),2,IF(G2<(K2-300),3,"")))


If you want to keep the formula simple

=IF(G2>K2,1,IF(G2<(K2-300),3,2))


Then in the formatting settings tick Show Icon Only, change Type to Number, and set Tick to >=3 and Exclamation to >=2.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.