How to use Conditional Formatting with OR for a range between -10 and 10

Hi Guys,

I have a conditional formatting that I use on a range of cells that turns light blue when the invoice amount reconciles to the amount my calculations work it out to be.

Now this works fine when the difference between the two is zero however due to VAT added on to the invoices sometimes they are out by 3 to 10 pence which is fine as we allow for this. What I wanted was a formula that would allow for the + or - 10 pence either way as well as a zero value and then turn that blue.

I the code below I have the original formula for my conditional formatting that only checked for a zero amount but this wasn't working out as usually the VAT always causes rounding issues and therefore this is why my previous conditional formatting is not really working.

Below is the original formula for the Conditional Formatting;

Please Login or Register  to view this content.

The above formula is great when the calculated amount matches exactly to the invoice amount but is no good when there is a + or - 10 pence variation.

This is what I tried to recify this using the OR function but I am not sure I have grasped the concept of using the OR fuction:

Please Login or Register  to view this content.

Now the above should check to see if the value in cell T7 is from -0.10 to 0.10. Do I need to use a IF statement instead?

Thanks for any help offerred.

Originally Posted by daddylonglegs

That should work fine, you don't need to use IF functions in conditional formatting, usually, you just need a formula which evaluates to TRUE or FALSE which yours does. You could also use ABS, i.e.

=ABS($T7)<=0.1

Wow! Thanks daddylonglegs, I guess I was trying to be to clever, I completely overlooked using the ABS fuction and as soon as I saw your reply the penny dropped.

Thanks very much for your help, it works a treat!

My new formula for my condition is:

Please Login or Register  to view this content.

Thanks again.

Out of interest I found out why my original formula did not work.

I was using the OR function instead of the AND function which is why every condition was being met, changing it to AND corrected that.