Forum Discussion
Conditional Formating
Hi,
First, you may join all you rules into one. In file I joined two with rule
=(L3>$K3)*(L3<>"")
applying it to
=$L$3:$L$323,$AF$3:$AF$323
Second, your time is only looks the same. If you copy AF92 and AK92 (both 1:24) and paste as values it'll be 0.0583333333333333 and 0.0583333333333335.
You may round the cells formatted as time to 1 second as
=MROUND(<cell>,"0:00:01")
See for AF and AK columns
Hi,
First, you may join all you rules into one. In file I joined two with rule
=(L3>$K3)*(L3<>"")
applying it to
=$L$3:$L$323,$AF$3:$AF$323
Second, your time is only looks the same. If you copy AF92 and AK92 (both 1:24) and paste as values it'll be 0.0583333333333333 and 0.0583333333333335.
You may round the cells formatted as time to 1 second as
=MROUND(<cell>,"0:00:01")
See for AF and AK columns
When I tried your formula
=(L3>$K3)*(L3<>"")
It made cells that had nothing in them highlighted....would that because I didn't erase the old rule?
So I can make 1 rule to work for all the cells on the whole sheet? I don't have to do a different rule for each column?
Thanks! the MROUND part worked!
In attached file I combined all rules in one. From my point of view when you add new column to highlight it's easier to add another region to the rule rather than generate new rule for each new region.
However, if you are comfortable with your existing rules - keep them, they also work.
- Thank you! That solved it for me!
Those cells that you said i could round, already have a formula in it to find the time it took. So are you meaning to make it round? By making another conditional format rule? or how would I make that cell round as well as calculate?
Please check the formula within the file I attached previous time. In column AF
=IF((([@Stop4]-[@Start4])*[@[How many guys4]]>0),MROUND(([@Stop4]-[@Start4])*[@[How many guys4]],"0:00:01"),"")
As for conditional formatting changes are optional.
- correct rule formula shall return TRUE or FALSE (or equivalent 1 and 0, etc.). In your case for some cells it returns "" which causes error in rule and it won't work for that cell. Actually result is the same as for FALSE but better to use more correct formulas;
- better to follow "one colour - one rule", all your rules could be combined in one, but that's optional
