Logic in Excel

Welcome

• this session is for 🌶🌶🌶 advanced Excel users, who are confident writing formulas
• if you can’t access the chat, you might need to join our Teams channel: tinyurl.com/kindnetwork
• you can find session materials at tinyurl.com/kindtrp

The KIND network

• a social learning space for staff working with knowledge, information, and data across health, social care, and housing in Scotland
• we offer social support, free training, mentoring, community events, …
• Teams channel / mailing list

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:

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

OR

• OR is TRUE when either of the statements are TRUE

XOR

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

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

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

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"

IF

• 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

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 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

Real life examples

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

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