Logic in Excel

NoteSession materials

• all materials
• slides html / pdf
• exercises .xlsx / completed exercises .xlsx

Previous attendees have said…

• 13 previous attendees have left feedback
• 100% would recommend this session to a colleague
• 92% said that this session was pitched correctly

NoteThree random comments from previous attendees

• Good training material with a lot of helpful formula’s embedded in the document for future use. Good clear training and opportunities for sharing issues to resolve in the moment. Highly recommend any training sessions Brendan is hosting.
• very interesting in being exposed to the wide range of functions related to this subject in Excel
• Nice examples of various functions which many of us may not know existed!

Welcome

• this session is for 🌶🌶🌶 advanced Excel users, who are confident writing formulas

Acknowledgements

Sincere thanks and appreciation to people who volunteered formulas for this session:

• Laura De Jongh (Hanover Scotland)
• Gail Young (NHS Greater Glasgow & Clyde)
• Gail Donaldson (NHS Lanarkshire)
• Matthew Hooks (Scottish Ambulance Service)
• Susanna Kirk (NHS Fife)
• Hilary Guthrie (NHS Lothian)
• Irene Ventura (NHS Lanarkshire)
• Peter Wild (NHS Ayrshire & Arran)
• Chris Spratt (NHS National Services Scotland)

Session outline

• intro to logic in Excel
  • five minutes of Boolean algebra
  • basic logical functions (NOT, AND, OR)
  • putting logical functions to work with IF and IFS
• using logic in Excel
  • dealing with errors and missing values (IFERROR, IFNA, and friends)
  • managing different data types (ISNUMBER and friends)
  • conditional summarising (SUMIF, COUNTIFS, and friends)
  • nesting IF statements and future-proofing your work

Boolean algebra

• the standard way of working with truth-values
  • found all over mathematics, logic, computer science…
• think of a statement like "my cat is blue"
  • call that P to save writing it every time
• assume that P is either completely TRUE or completely FALSE

NOT

• NOT gives us the opposite truth-value
• if P is FALSE, then NOT P is TRUE
• a useful shorthand: the truth table:

P	NOT P
TRUE	FALSE
FALSE	TRUE

AND

• we also have functions to understand what happens when we’re dealing with two statements
• AND is a great example - it’s TRUE when both the statements are TRUE

P	Q	P AND Q
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE

OR

• OR is TRUE when either of the statements are TRUE

P	Q	P OR Q
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE

XOR

• For completeness, we should also talk about XOR
• XOR is TRUE when either of the statements - but not both - are TRUE

P	Q	P XOR Q
TRUE	TRUE	FALSE
TRUE	FALSE	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE

NOT in Excel

• sweet relief: please open the sample workbook for this session in Excel
• have a look at the NOT worksheet
  • NOT() function inputs in the truth-table
  • example use about inhalers
    • note that NOT() will treat any number as TRUE, and 0 as FALSE

NoteTask

• please add a formula to decide whether a person is a non-user of inhalers or not
• it should return TRUE/FALSE based on the number of inhalers used

AND and OR in Excel

• have a look at the AND / OR worksheet
  • have a look at the truth-table for AND and OR
  • specifically, look at the formulas and the way they use AND() and OR()

NoteTask

• in the hypertension/diabetes table, you’ve got several rows of data where each represents a person. Please write two formulas:
  • for diet and lifestyle, you’ll want to return TRUE if a person has either hypertension and/or diabetes
  • for statins, you’ll want to return TRUE is a person has both hypertension and diabetes

IF

• unfortunately our data usually doesn’t come neatly coded into TRUE and FALSE
• that mean we’ll usually need to use our basic logical functions in combination with other functions
• IF is a great example of a function that helps you convert to logical values
• here’s an example formula: =IF(B3 > 150, "Hypertension", "No hypertension")
  • if the value in B3 is over 150, the formula returns "Hypertension"
  • otherwise it returns "No hypertension"
• this is useful in its own right, but gets even more powerful when you return logical values
• have a look at the top table on the IF worksheet

NoteTask

• in the IF worksheet, there’s an incomplete table of blood pressure values
• please write a formula to return TRUE if those values are over 150, and FALSE otherwise
• there’s a named cell on this sheet called Cutoff - please now change that 150 in your IF formula to use the value of the named cell

TRUE and FALSE are 1 and 0

• many other tools let you add up TRUE/FALSE values as if they were 1s and 0s
• this can be very useful - say, to quickly count matching values
• Excel needs a minor workaround: add -- before a logical value to treat it as a number: =SUM(--Table1[Hypertension?])

IFS

• IFS allows you to check for the presence of several conditions fairly concisely
• an example: =IFS(B4 = TRUE,"High",C4 = "CRD","High", D4 > 20,"High", TRUE,"Low")
  • read this horror as pairs of arguments, like B4 = TRUE, "High"
  • each pair contains
    • a logical test - like B4 = TRUE
    • a return value if that test is TRUE - like "High"
  • the final pair of arguments are a catch-all to pick up all non-matched values

Public service announcement

• just because you can, doesn’t mean you should
• IFS get pathologically complicated
• pivot tables might be better for more complicated cases

IFNA, IFERROR, and friends

• these functions are great for dealing with errors and missing values
• that’s useful, because (understatement of the decade) Excel isn’t very good at consistently dealing with errors and missing values

IS and IF functions

• IS**** functions detect errors and missing values and return TRUE/FALSE
• IF**** functions detect, and then:
  • if an error is found display a message of your choice
  • otherwise just repeat the value they refer to

What do they detect?

The important error messages ones

• BLANK = “There’s no value in that cell”
• ERROR = “This formula doesn’t work properly”
• #N/A = “I can’t find what I was asked to find”
• ERR = “A non-N/A error has happened”

SUMIF and COUNTIF

• basically, IF plus summary functions
• three simple-ish examples to see:
  • =SUMIF(B3:F3,">5"), which totals all values in the range greater than 5
  • =COUNTIF(A10:E10,"ramipril"), which counts all occurrences of the work “ramipril” in a range
  • or the close relative =COUNTIF(A10:E10,"*prazole") which uses a wildcard to count all the words ending in “prazole” from a range
• there’s also the more complex SUMIFS, which allows you to sum with multiple conditions

Example applications

• =IF(AND(B4 >= TIMEVALUE("08:30"), B4 <= TIMEVALUE("17:00")), "In hours", "Out of hours") to determine if a time is in- or out-of-hours
• =IF(LEN(B20)=9,CONCATENATE("0",B20),TEXT(B20,"0")) to re-add a dropped leading 0 from a CHI / ISBN
• =IF(ISBLANK(B26), "", B26) to elegantly reference date values to avoid returning 00/01/1900 for missing values
• =IF((TODAY() > DATE(YEAR(B31) + 3, MONTH(B31), DAY(B31))), "Late", "Not late") to determine if an activity has taken place in the past three years
• =FILTER(B35:B41, (C35:C41 > 4)*D35:D41>4) to find all rows with a value more than 4 in both the C and D column
• =IF(COUNTIF(B44,"*ow*"),TRUE,FALSE). IF by itself doesn’t allow wildcard matching, so you can attempt a work-around via COUNTIF