Trying to find a Conditonal Formatting formula that highlights cells outside a given range of values

[Tom153]

Hi all,

I’m trying to create a conditional formatting rule for 'Column B' of the attached spreadsheet from Row 14 downwards.

The formula needs to highlight the cells where values DO NOT fall within a given range of acceptability as per the rules below:

Lowest limit:

Weight < (Core length – 0.03) *(Expected weight)

So that’s:

$B14 < ($A14 – 0.03) *($C14)

Upper limit:

Weight > (Core length + 0.03) *(Expected weight)

So that’s:

$B14 > ($A14 + 0.03) *($C14)

I've already had a go with the formula below, but it doesn't appear to be working:

[#2]

@Tom153 Haven't gone through your formula to check it it really does what you intend to, but I did change the CF rule so that it will give a consistent result. Note that when you apply a rule to each cell in the entire column B, you need to use B1 (without the $-signs) as the starting point. Also added a rule to skip conditional formatting if the cell in B is blank. Not sure if it's needed, but just in case.

See attached.