Function and is also interesting. Combined with SI, this gives:
= IF (AND (condition1; condition2 ;....... conditionZ) true false)
displays on the various conditions to be verified (eg B3> C2 .... B9 = C2, etc. ..).
if all conditions are met, the result is true, otherwise the result is false.
you can use these results as arguments for other forms or directly enter a value, eg
= IF (AND (B3 <100 B3> 200, C1 = "yes"); "value out of range ";"")
- Verify that the value entered in B3 is between 100 and 200
- Verify that cell C1 contains the word yes
- Inscribed "Value out of range" if B3 is <100 or> 200 or leaves the cell blank ( "") if B3 is between 100 and 200.
Another example: click Format / Conditional Formatting / The formula is:
= IF (AND (B3 <100 B3> 200, C1 = "yes"); true, false)
then click Format and choose the type of display (including color of the cell) when the conditions are true.
This document entitled « Basic Excel Formulas 2 » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.