Conditional Formatting - Track Cost Fluctuation

Denise Quach

I have a master list to track the cost of an item. Is there a way to format cells to change the font to Red or Green based on whether or not the value is higher than a previous cost? IE: In 2015 cost was $1 and in 2016 the cost rose to $2. I would want the 2016 cost to automatically turn Green to show that there was an increase in price or vice versa.

 

Atus Bartal

Denise,

You need to have a new column with the difference, and set two Conditional Formatting rules for this column: 

If Difference is less than 0 then apply this format to the Difference column. ["this format" is: red letters]

If Difference is greater than 0 then apply this format to the Difference column. ["this format" is: green letters]

 

The problematic part of the operation, I guess, will be setting the right formulae counting the difference in the righ places (since the values are not in the same line). I mean these are "=Cost2-Cost5" or something, but they depend on where the specific values are. 

 

Please let us know if you need more help. 

 

atus

 

 

J. Craig Williams

You may also make use to the Absoulute Reference instead of Relative Reference.

Very similar to functionality in Excel if you are used to that

 

=[Row]Column

 

is relative. Add $ for either row or column and they become fixed -- but will update when rows/columns are added/removed. Sometimes the deletion might get you, but just watch for it.

 

=$[Row]$Column