Logical Functions (1)

1. Logical Functions (1) • AND/OR/NOT – these are usually used within other functions to make them more flexible and powerful • A closer look at AND:

2. Using AND to allow more complex IF statements (1) • Our previous example assessed costs based on whether they exceeded a maximum budgeted amount • Now we add another factor to the assessment, specifically whether the service is indispensable or not

3. Using AND to allow more complex IF statements (2) • Results of the 2-factor assessment

4. Thinking about AND • Imagine the rules for a competition: “Only those over the age of 18 who have a valid passport may enter.” • This could be rephrased as “Only IF you are over 18 AND you have a valid passport can you enter” • This could be written for a spreadsheet to understand as follows: =IF(AND(Some Cell=“Over 18”, Some Other Cell= “Have a valid passport”), ”Can enter”, “Can’t enter”) • Both conditions in the AND part have to be true or you can’t enter because only AND(True, True) is equal to True

5. Example • How this would actually look on a spreadsheet:

6. Thinking about OR • Imagine the eligibility rules for a bursary: “Both South African students and Zimbabwean students can apply for the bursary.” • This could be rephrased as “IF you are South African OR you are Zimbabwean you can apply for the bursary” • This could be written for a spreadsheet to understand as follows: =IF(OR(Some Cell=“South African”, Some Cell= “Zimbabwean”), “Can apply”, “Can’t apply”) • If either of the conditions in the OR part was true the person could apply for the bursary, as OR(True, False) is equal to True and OR(False, True) is also equal to True.

7. Example • How this would actually look on a spreadsheet:

8. Thinking about NOT • Imagine the rules for a competition once again, this time sponsored by 5FM: “Only those who are not employees of 5FM may enter.” • This could be rephrased as “Only IF you are over NOT employed by 5FM can you enter” • This could be written for a spreadsheet to understand as follows: =IF(NOT(Some Cell=“Employee of 5fm”), “You can enter”, “You can’t enter”)

9. Example • How this would actually look on a spreadsheet:

10. Text Functions (1) • =REPT(string, number_of_repetitions) • =LEFT(string, number_of_characters) • =RIGHT(string, number_of_characters) • =MID(string, start position, number_of_characters)

11. Text Functions (2) • =LEN(string) • =TRIM(string) • =UPPER (string) • =LOWER (string) • =PROPER(string)

12. Text Functions (3) • =REPLACE(whole string, position of text to replace, number of chars to replace, new text) • =SUBSTITUTE(whole string, text to replace, new text)

13. Numbers or Words? Inside a function: • Words/sentences/strings need to be in inverted commas e.g. =IF(A6=“South African”, “Can Apply”, “Can’t Apply”) • Numbers should not be in inverted commas because this causes Excel to treat them as words and prevents you from using them in calculations, e.g. =IF(B10>5, 100, “B10 is not greater than 5”) • Exceptions exist (for example the logical tests in the COUNTIF and SUMIF functions)

14. Common Errors • When you have entered an erroneous formula, Excel displays an error value

15. Manual vs Automatic Calculation • Formulas are usually recalculated instantly when the values they refer to change • To delay recalculation of all dependent formulas, use the Calculation Options button in the Calculation group of the Formulas tab on the Ribbon to select Manual or Automatic calculation • Alternatively, click the Manual radio button under the Workbook Calculation tab of the Calculation Options dialog box and then press F9 or the Calc Now button to start calculation Or

16. Formula Options • Office button  Excel Options  Formulas offers various options to control the way Excel works with formulas