Excel Conditional Formatting Offset Greater than

Question

I need to create a conditional formatting rule that will apply some color if the cell to the left has a value greater than 500, however it is not accepting my formula:

=OFFSET(0,-1) > 500

Can anyone tell me what am I doing wrong?

Solution 1

Edit: I think you would not be able to use conditional formatting this way with icon sets. I got the following error when I tried:

You cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets.

I was, however, able to achieve the same by applying this formula to right column and then applying conditional formatting rule on this column as show in the screen shot.

=IF(OFFSET(E10,0,-1)>500,1,IF(OFFSET(E10,0,-1)=500,0,-1))

The formula should be:

=OFFSET(E10,0,-1)>500

In Excel, the Offset function returns a reference to a range that is offset a number of rows and columns from another range or cell.

The syntax for the Offset function is:

Offset( range, rows, columns, height, width )

• range is the starting range from which the offset will be applied.
• rows is the number of rows to apply as the offset to the range. This can be a positive or negative number.
• columns is the number of columns to apply as the offset to the range. This can be a positive or negative number.
• height is the number of rows that you want the returned range to be.
• width is the number of columns that you want the returned range to be.

Solution 2

There is a way of achieving the equivalent of Offset in Conditional formatting with relative references.

In the cell you want conditionally coloured, select the conditional formatting option Use a formula to determine which cells to format. In the formula bar use the following syntax:

=INDIRECT(ADDRESS(ROW()+X,COLUMN()-Y))>Z

Here, X is the vertical offset, Y is the horizontal offset and Z is the value against which you are testing.

In the OP's example, it would be:

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))>500

You then just need to define the formatting; you can't have the traffic light symbol but you can set the cell's background or font to be a specific colour.

Comments

[nmuntz]

Tried that and it's giving me a "Please enter a valid formula" error.

[nmuntz]

That worked out great ! Thank you very much! I would have never guessed that you cant use formulas with icon sets