Conditional formatting based on Cell above... Unless

I am using conditional formatting to colour cells based on a comparison of data between cells. It's a complex scenario with different outcomes needed.

The first outcomes I will detail and they are quite easy -

1. blank cells should have no colour
2. cells with a hyphen should have no colour
3. cells with a value lower than the cell above should be red
4. cells with a value higher than the cells above should yellow
5. cells with a value lower than the cells below should be orange

And that has worked fine, however, there are some advanced rules I have run into problems with:

4. cells with a value higher than the cells above should yellow UNLESS the cell above is blank
5. cells with a value lower than the cells below should be orange UNLESS the cell below is blank

Well, I have some other problems... but this is a good place to start! Any help is appreciated :)

Here is my conditional formatting. The first line is attempting to resolve the issue(not working):

and here is a selection of the data:

It's a mind melter! Thanks

Solution 1:

I created a sample about your problem and you can try it:

Select Range F3:F31 For White enter the formula to Conditional Formatting rule:

=OR(OR(F4="",F4="-"),OR(F2="",F2="-"))

For Red enter the formula to Conditional Formatting rule:

=OR(AND(F3="L1",F2="L2"),AND(F3="E3",OR(F2="L2",F2="L1")),AND(F3="E2",OR(F2="L2",F2="L1",F2="E3")))

For Yellow enter the formula to Conditional Formatting rule:

=OR(AND(F3="L2",OR(F2="L1",F2="E3",F2="E2")),AND(F3="L1",OR(F2="E3",F2="E2")),AND(F3="E3",F2="E2"))

For Orange enter the formula to Conditional Formatting rule:

=OR(AND(F3="L1",F4="L2"),AND(F3="E3",OR(F4="L2",F4="L1")),AND(F3="E2",OR(F4="L2",F4="L1",F4="E3")))