Logic in Excel

Session materials

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

No feedback found for this session

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

Task

• 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()

Task

• 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

Task

• 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