excel conditional formatting for minimum rainfall -


as may know, there "trace" rainfall if rains amount less 0.1mm, have 0.0 mm < trace < 0.1 mm.

now example, have table of rainfall data 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 want highlight max. rainfall each month yellow fill. that's simple, set rule highlights top 1 item.

but problem highlighting min. rainfall each month green fill. if set rule highlights bottom 1 item, fine feb, mar , apr. (2011 feb, 2014 mar , 2014 apr highlighted.)

however, jan, 2014 jan highlighted. expect 2012 , 2013 highlighted trace < 0.1 mm.

if add rule highlights cell contains "tr" in addition bottom 1 item rule:
1) january, 2012, 2013, 2014 highlighted (i want 2012&2013)
2) february, both 2011 , 2015 highlighted (i want 2011)

i tried following:
1st rule: formula: =and(countblank($b2)=0,$b2=0) --> format green fill [stop if true checked]
2nd rule: cell contains "tr" --> format green fill [stop if true checked]
3rd rule: bottom 1 item --> format green fill [stop if true not checked]
logic is:
if there 0.0 (not blank cell),"tr" & 0.1, highlight 0.0 not other two...
if there no 0.0 there "tr" & 0.1, highlight "tr" not 0.1
if there no 0.0 , "tr" there 0.1, highlight 0.1
method fails... think it's because "stop if true" isn't purpose.

is there method highlight cell correctly want?

just realized may have wanted instead:

final product

note highlights occurrences of minimum value month, not first one (which easier conceptually).

there used 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, each cell, conditional formula:

  • evaluates false if blank
  • evaluates true if of following true:
    • it's zero
    • if there no zeros in column , it's "tr"
    • if there no zeros , no "tr"s in column , it's equal minimum value column

Popular posts from this blog