# Formulas and icons in Excel

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.

-

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.

-