0

I have a document that is created to monitor complaince. I would like to have the following rules:

  1. Highlight the cell if it meets a goal - I've figured this out and the formatting works.
  2. Highlight the cell if it is increasing towards the goal - I haven't figured out how to change the formatting if the number is improving from the previous number.
  3. Highilight the cell if it is decreasing below the goal - I've figured this out and the formatting works.
  4. Highlight the cell if it is below the goal more than twice - I have not figured out how to do this.
2
  • Just to be clear –– are you talking about the value in the same column, previous row (and maybe two rows up), or are we talking about the previous value of this cell before the last time it was changed? If it’s the first case, I don’t understand why you can do #3 but not #2. If it’s the second case, I’d like to see how you do #3, because I believe it’s impossible. Mar 2, 2013 at 21:33
  • 1
    Data sample would be very much appreciated
    – Peter L.
    Mar 3, 2013 at 8:24

1 Answer 1

1

It would be more helpful if you listed the version of Excel you are using as we could then give more specific answers.

In general though, there are two ways to access cells by their relative offset.

Personally, I always change the Row/Column references into "R1C1" mode. You can do this through the Options settings. Once done, you can do things like say =R[-1]C<RC in a conditional format test. This says "Is the previous row, same column less than the current row and column?" Much easier than trying to get your head around the normal reference mode. You may also want to anchor either the row or column so that you can apply the same conditional test to anywhere in your selected range. So =R[-1]C1<RC1 can be applied to all columns because it forces the test to only look at column 1 comparing column 1 of the current row with column 1 of the previous row.

The other thing you can do is to use the offset function to refer to a different cell. So offset(RC,-1,0) for example. This method has the advantage that it is not error prone to problems when moving data around. With the relative reference method, if you cut/paste rows around or insert loads of rows, you may well find that some of the conditional formulae refer still to the original rows and not to the inserted ones. This doesn't happen if you use the offset formula.

Give it a go a few times and you will soon pick up the method.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .