# Logical Functions for ad-hoc analysis

Here are six more useful functions to use in Excel. This time, logical functions that return a result based on the conditions.

### AND

Returns TRUE if ALL of the arguments evaluate to TRUE. In this example, the result is TRUE if the value in cell E2 is greater than 10, AND the value in F2 is less than 200

1 |
=AND(E2>10, F2<200) |

### OR

Returns TRUE if ANY of the arguments evaluate to TRUE. In this example, the result is TRUE if the value in cell E2 is greater than 10, OR the value in F2 is less than 200

1 |
=OR(E2>10, F2<200) |

### NOT

Returns the reversed logical value of its argument. ie. If the argument is FALSE, then TRUE is returned and vice versa. In this example, the result is TRUE if a value in E2 is NOT greater than 500.

1 |
=NOT(E2>500) |

### IF

To return a value other than True or False, based on whether a condition has been satisfied, use the IF function.

IF statements are not only useful, they teach junior analysts the basics of Else/ElseIf statements.

The IF function needs 3 parameters:

1 – The test

2 – The result if the test is TRUE

3 – The result if the test is FALSE

1 2 3 |
=IF(test,"true","false") =IF(B2>C2,"Win","Lose") /* text needs to be in double quotes */ |

Nesting simply means to combine formulas, one inside the other, so that one formula handles the result of another.

1 2 3 4 5 |
=IF(B5>10,"Platinum", /* if >10 then Platinum */ IF(B5>3,"Gold", /* if >3 then Gold */ IF(B5>7.5,"Silver", /* if >7.5 then Silver */ IF(B5>2.5,"Bronze", /* if >2.5 then Bronze */ "Starter")))) /* else Starter */ |

### SUMIF

1 2 3 |
=SUMIF(range,"criteria") =SUMIF(B1:B4,">= 40") /* to add up only those values in a range */ |

### COUNTIF

1 2 3 |
=COUNTIF(range,"criteria") =COUNTIF(B1:B4,"South Africa") /* to count up only those values in a range */ |

Big and small businesses use Excel because it’s easy to learn and allow analysts and stakeholders to speak the same language. If more sophisticated add-ons are required (Power BI, Power Pivot, Power Maps) these are open source and easy to use.

Excel offers functionality and plenty of bang for your buck with a usable interface and plenty of add-ons for scalability.

Photo by **Sebastian Voortman **from **Pexels**