excel conditional formatting for minimum rainfall

Question

As you may know, there is "trace" rainfall if it rains but the amount is less than 0.1mm, so we have 0.0 mm < Trace < 0.1 mm.

Now for example, I have a table of rainfall data as follow:

Year Jan Feb Mar Apr
2011 24.0 0.0 9.4 57.8
2012 Tr 6.8 29.3 109.2
2013 Tr 46.1 49.3 71.0
2014 0.1 7.9 0.1 14.6
2015 15.4 Tr 25.3 (blank)

If I want to highlight the max. rainfall for each month with a yellow fill. That's very simple, just set a rule that highlights the top 1 item.

But the problem is highlighting the min. rainfall for each month with a green fill. If I simply set a rule that highlights the bottom 1 item, it is fine for Feb, Mar and Apr. (2011 Feb, 2014 Mar and 2014 Apr will be highlighted.)

However, for Jan, 2014 Jan will be highlighted. But I expect 2012 and 2013 to be highlighted as Trace < 0.1 mm.

If I add a rule that highlights the cell which contains "Tr" in addition to the bottom 1 item rule:
1) for January, 2012, 2013, 2014 are all highlighted (I just want 2012&2013)
2) for February, both 2011 and 2015 are highlighted (I just want 2011)

I also tried the following:
1st rule: Formula: =AND(COUNTBLANK($B2)=0,$B2=0) --> Format Green Fill [Stop if true checked]
2nd rule: cell which contains "Tr" --> Format Green Fill [Stop if true checked]
3rd rule: bottom 1 item --> Format Green Fill [Stop if true not checked]
The logic is:
If there are 0.0 (not blank cell),"Tr" & 0.1, highlight 0.0 only but not the other two...
If there is no 0.0 but there are "Tr" & 0.1, highlight "Tr" only but not 0.1
If there are no 0.0 and "Tr" but there is 0.1, highlight 0.1
However this method fails... I think it's because "Stop if true" isn't for this purpose.

Is there any method to highlight the cell correctly as I want?

Answer 1

Essentially, your rules mean a precedence like this (1 is highest precedence):

1. The first cell in the column with a value of 0
2. The first cell in the column with a value of Tr
3. The cell in the column with the minimum value

This can be done many ways, but the approach I went with was to use the MATCH function to find the row of the cell meeting the 1st criteria above, then if that returned an error, do the same for the second criteria, and then if that again was an error, do the same for the third criteria.

Once you have the row of the cell you want, you simply make your conditional formatting rule compare the row of the current cell to that row number.

Note that I subtracted 1 from the current cell because I wrote the arrays so that they exclude looking at the first row (since they are just labels). For example, when MATCH returns a value of 4, that means the 4th row starting at the top of the lookup array, not the worksheet. Since the lookup array I used started at row 2 of the worksheet, we need to subtract 1 when doing the comparison.

=(ROW(B2) -1) = IFERROR(MATCH(0,B$2:B$6,0),IFERROR(MATCH("Tr",B$2:B$6,0),MATCH(MIN(B$2:B$6),B$2:B$6,0)))

The cool thing about doing it this way is that the conditional formatting can all be done in a single rule. This is because the lookup columns have their rows held constant, but will change the column letter appropriately.

Here is the final product:

This is the rules view for the entire sheet:

Answer 2

Just realized that you may have wanted this instead:

Note that it highlights all occurrences of the minimum value for the month, not just the first one (which is actually much easier to do conceptually).

There I used this formula:

=AND(NOT(ISBLANK(B2)),OR(B2=0,AND(COUNTIF(B$2:B$6,0)=0,B2="Tr"),AND(COUNTIF(B$2:B$6,0)=0,COUNTIF(B$2:B$6,"Tr")=0,B2=MIN(B$2:B$6))))

Basically, for each cell, the conditional formula:

• evaluates to false if it is blank
• evaluates to true if any of the following are true:
  • it's a zero
  • if there are no zeros in the column and it's a "Tr"
  • if there are no zeros and no "Tr"s in the column and it's equal to the minimum value for the column