Excel 2010 Conditional Formatting for a range based on "Less Than" and "OR"

Question

How do I use conditional formatting on a range of values such that every third cell should be filled with a particular color if the value entered in that cell is lower than values in any of the three previous cells?

My range is listed horizontally in a row between A10:FE10.

For example, if value in cell M10 is less than value present in either J10 or value in K10 or value in L10, then I want M10 to be formatted with red fill.

Similarly, if value in P10 is less than value in M10 or N10 or O10, then P10 should be filled with red and so on.

I tried selecting the range and then using the Conditional Format Rule: Format Only Cells that Contain - Cell Value - Less Than - "OR(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-3),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1))"

Then, I manually changed - Applies to M10, P10, S10, V10 etc.

When I tried this with just "Less than" - OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-3), it works.

The OR function is not working with the Less Than rule in Conditional Formatting.

First of all, how do I get conditional formatting for "Less Than" and "OR".

Secondly, is there are a better way to apply the rule to every third cell than to manually enter the cell references.

Answer 1

Building on teylyn’s answer, you could use the formula

=AND(MOD(COLUMN(),3) = 1, OR(M10<J10, M10<K10, M10<L10))

and apply it to every cell in row 10, and it would apply only to columns A, D, G, J, M, P, S, etc.

Answer 2

The formula does not have to be that complicated. Keep in mind that conditional formatting formulas can use relative references. When the format is applied to another cell, the references will be relative to the current cell.

Select cell M10 and enter the conditional format using this formula:

=OR(M10<J10,M10<K10,M10<L10)

Note that there are no $ signs anywhere in the cell references. When this format is copied and applied elsewhere, it will always compare the current cell with the three cells to the immediate left.

The only thing you have to keep in mind is that the correct cell needs to be selected when you define the format. So you must select M10 and then create the formula with the references relative to M10. After that, you can change the range that the format is applied to, or copy and paste the cell. In the screenshot, the format has been applied to M10 to P10.