IF and logic formulas
Flags, pass and fail, grades, multiple conditions and hiding errors the right way. 6 answers, each checked by running it, with a live example and an exercise.
How to check if a cell contains specific text in Excel
=IF(ISNUMBER(SEARCH($E$1,A2)),"Yes","No")Combine SEARCH with ISNUMBER inside IF: =IF(ISNUMBER(SEARCH(text,cell)),"Yes","No").
How to use IF with multiple conditions in Excel
=IF(AND(B2>=$G$1,C2>=$G$2),"Bonus","No bonus")Put the conditions in AND (all must be true) or OR (any can be true) inside IF: =IF(AND(cond1,cond2),"yes","no").
How to assign letter grades with IF or IFS in Excel
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F")Use IFS with the conditions from the highest grade down: =IFS(score>=90,"A",score>=80,"B",score>=70,"C",score>=60,"D",TRUE,"F").
How to check if a cell is blank in Excel
=IF(C2="","Not shipped",C2-B2)Test the cell against "": =IF(C2="","Not shipped",C2-B2). ISBLANK(C2) works too, but only for truly empty cells.
How to replace #DIV/0! with 0 or a blank in Excel
=IFERROR(B2/C2,0)Wrap the division in IFERROR: =IFERROR(B2/C2,0). For only the zero case, test the divisor: =IF(C2=0,0,B2/C2).
How to check if a number is between two values in Excel
=IF(AND(B2>=$F$1,B2<=$F$2),"In range","Out of range")Use AND with both limits: =IF(AND(value>=low,value<=high),"In range","Out of range").