There is another thread about this but all of the answers there aren't helping.
In the range: D12:AY500 - I have a matrix of numbers such that: y=Date, x=Time:
..
I would like it so the cells are green IF R3=1 and D12:AY500>75
I've tried a number of things like this:
But I don't get the result I'm looking for...
Any Advice?
Your attempt shows you have selected the range D12:AY500, chosen an appropriate rule type (Custom formula) and accepted the default formatting which I assume is green enough for you. So you are very nearly there.
However CF effectively loops through the cells in the selected range and, for a formula rule, triggers the chosen formatting when the rule applied to the cell at each step returns TRUE or equivalent. In stepping through, the formula is adjusted just as it would if entered in a cell in the sheet. Hence with =R3=1 the cell in E13 would be tested with =S4=1, and as S4 does not contain 1, the formatting would not trigger regardless of other additive criteria. The testing is against R3 in every cell being tested, when that is anchored: $R$3.
However =if($R$3=1,D12:K22>75) would not solve the issue as it uses incorrect syntax.
Two tests are involved and for that AND is a suitable function. When both tests are passed AND returns TRUE and the CF is triggered:
=and($R$3=1,D12>75)
So that is the formula I recommend here.
The second test does not use a fixed reference, so when the CF steps into E13 the formula effectively adapts to: =and($R$3=1,E13>75) and for the sample data the formatting would trigger for that cell.
