Conditional Formatting

[stevembe]

I have an impossible task to achieve in a spreadsheet. Basically I have the five working days of the week in a column and next to it I have another column for figures, four digits in length e.g. 5935. What I need to do is if the cell value is less than the previous day it shades red, if more than green, if the same gray but if the hundreds unit remains the same it goes blue. I can achieve the first part with conditional formatting but the blue shading if the hundreds unit does not change is baffling me.

Any assistance would be greatly appreciated.

P.S. I am using Office 2010<!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

<!-- controls -->

 

[Expiry]

I think you can use this in your conditional formatting.

=ROUNDDOWN(D11/100,0)=ROUNDDOWN(C11/100,0)

CF works in order, so this will have to be your first condition, so that it only goes red or green if the 100s bit isn't the same (I presume that's what you want).

change the cell refs to suit your range.

 

[barry houdini]

You could use FLOOR to round down to the previous 100, e.g.

=FLOOR(B2,100)=FLOOR(B3,100)