Spreadsheet Functions

Introduction

  • Logic in Spreadsheet Functions: Logic is defined as a mirror-image of the world, as noted by Ludwig Wittgenstein, emphasizing how logical expressions interact with data.

Datasets Overview

  • Dataset 1: A Kenyan dataset containing average retail market prices for selected food crops across the months of March and September from 2014 to 2018.

  • Dataset 2: Information regarding the subsidies provided by the Kenyan government for food crops during the same months and years.

Logic Functions in Spreadsheets

IF and IFS Functions

  • IF Function: Returns one value if a logical expression evaluates to TRUE, or another if it evaluates to FALSE.

    • Syntax: =IF(logical ext{}expression, value ext{}if ext{}true, value ext{}if ext{_}false)

    • Parameters:

    • logical_expression: Expression or cell reference that evaluates to TRUE or FALSE.

    • valueiftrue: The value returned if the expression is TRUE.

    • valueiffalse: [OPTIONAL] The value returned if the expression is FALSE.

  • IFS Function: Evaluates multiple conditions and returns the value corresponding to the first TRUE condition.

    • Syntax: =IFS(condition1, value1, [condition2, value2, …])

    • Parameters:

    • condition1: First condition to evaluate, can be a boolean, number, or cell reference.

    • value1: Returned value if condition1 is TRUE.

    • [condition2, value2, …]: Additional conditions and corresponding values.

Example Uses of IF and IFS Functions

  1. Task Example 1: Categorize prices -> If price < 50 → Output = “Cheap”, Else → “Expensive”.

    • Pseudocode:

      • IF price < 50

        • Output: “Cheap”

      • ELSE

        • Output: “Expensive”

    • Formula in Cell D3: =IF(B3<50, "Cheap", "Expensive")

  2. Task Example 2: Categorize prices into three groups:

    • If price < 50 → Output = “Cheap”, If price > 70 → “Expensive”, Else → “Affordable”.

    • Formula in Cell E3: =IFS(B3<50, "Cheap", B3>70, "Expensive", B3<70, "Affordable")

Alternative IF Functions
IFERROR and IFNA Functions

  • IFERROR Function: Catches errors in formulas and returns a specified value if an error is found.

    • Syntax: =IFERROR(value, [value ext{}if ext{}error])

    • Parameters:

    • value: Value to check for errors.

    • [valueiferror]: The value returned if value is an error.

  • IFNA Function: Specifically captures #N/A errors and allows for a designated replacement value.

    • Syntax: =IFNA(value, value ext{}if ext{}na)

    • Parameters:

    • value: The evaluated value.

    • valueifna: The value returned if value is an #N/A error.

Example Uses of IFERROR and IFNA Functions

  1. Example 1: Detect errors in price values.

    • If value = ERROR → Output = “Error detected”, Else → Return value.

  2. Example 2: Handling #N/A values.

    • If value = NA → Output = “Not applicable”, Else → Return value.

SUMIF and SUMIFS Functions

  • SUMIF Function: Adds numbers in a range that meet a single criterion.

    • Syntax: =SUMIF(range, criterion, [sum ext{_}range])

    • Parameters:

    • range: The range to evaluate.

    • criterion: The condition to apply.

    • [sum_range]: The actual cells to sum.

  • SUMIFS Function: Adds numbers based on multiple criteria.

    • Syntax: =SUMIFS(sum ext{}range, criteria ext{}range1, criterion1, [criteria ext{_}range2, criterion2, …])

    • Parameters:

    • sum_range: The cells to sum.

    • criteria_range1: The range to evaluate.

    • criterion1: The condition.

Example Uses of SUMIF and SUMIFS Functions

  1. Example 1: Sum prices > KSh 50.

    • Formula: =SUMIF(B3:B10, ">50")

  2. Example 2: Sum prices > KSh 50 and < KSh 70.

    • Formula: =SUMIFS(B3:B10, B3:B10, ">50", B3:B10, "<70")

COUNTIF and COUNTIFS Functions

  • COUNTIF Function: Counts cells matching a single condition.

    • Syntax: =COUNTIF(range, criterion)

    • Parameters:

    • range: The cells to evaluate.

    • criterion: The condition.

  • COUNTIFS Function: Counts cells matching multiple criteria.

    • Syntax: =COUNTIFS(criteria ext{}range1, criterion1, [criteria ext{}range2, …])

    • Parameters:

    • criteria_range1: The range to evaluate against criterion1.

    • criterion1: The condition to apply.

Example Uses of COUNTIF and COUNTIFS Functions

  1. Example 1: Count prices > KSh 50.

    • Formula: =COUNTIF(B3:B10, ">50")

  2. Example 2: Count prices > KSh 50 and < KSh 70.

    • Formula: =COUNTIFS(B3:B10, ">50", B3:B10, "<70")

The SWITCH Function

  • SWITCH Function: Evaluates an expression against multiple cases and returns the value associated with the matched case.

    • Syntax: =SWITCH(expression, case1, value1, [case2 ext{}or ext{}default, …], [value2, …])

    • Parameters:

    • expression: The value to evaluate.

    • case1: First case to be checked against expression.

    • value1: Returned value when case1 matches expression.

Example Use of SWITCH Function

  • Categorizing food crops based on type (e.g., Legumes, Vegetables, Fruits, Grains) with an example of their respective cases.

  • Formula: =SWITCH(crop ext{_}name, "Beans", "Legume", "Cabbages", "Vegetable", "Potatoes", "Vegetable", "Tomatoes", "Fruit", "Bananas", "Fruit", "Grain")

SWITCH versus IFS Function

  • SWITCH Ideal Use: When matching a single value to multiple outcomes, providing efficiency since only matching conditions are evaluated.

  • IFS Ideal Use: For evaluating multiple independent conditions, offering flexibility for various outputs.

Other Logic Functions

  • AND Function: Returns TRUE if all arguments are TRUE, otherwise FALSE.

    • Syntax: =AND(logical ext{}expression1, [logical ext{}expression2, …])

    • Examples:

    • =AND(TRUE, FALSE) returns FALSE.

  • NOT Function: Returns the inverse of a logical value.

    • Syntax: =NOT(logical ext{_}expression)

    • Examples:

    • =NOT(TRUE) returns FALSE.

  • OR Function: Returns TRUE if any argument is TRUE.

    • Syntax: =OR(logical ext{}expression1, [logical ext{}expression2, …])

  • XOR Function: Returns TRUE if an odd number of arguments are TRUE.

    • Syntax: =XOR(logical ext{}expression1, [logical ext{}expression2, …])

Logic Functions Example Uses

  • Categorize price trends for food crops based on original and subsidized prices over different months.

    • Use logical expressions to define conditions for rising, dropping, or unidentified trends.

    • Implement a comprehensive analysis method by combining AND and SWITCH functions for a more detailed outcome assessment based on logical evaluations.