Skip to main content

Conditional Transforms (IF)

Apply different transformations based on conditions using the IF formula for dynamic data processing.

Relevant Formula​

IF​

Specifies a logical test to perform, returning one value if TRUE and another if FALSE.

View IF Formula Reference →

Basic Usage​

=IF(condition, value_if_true, value_if_false)

// Simple comparison
=IF([amount] > 100, "High", "Low")

// Check for blank
=IF(ISBLANK([email]), "Missing", "Present")

Common Use Cases​

Value Categorization​

// Categorize sales amounts
=IF([amount] > 1000, "Large",
    IF([amount] > 500, "Medium", "Small"))

// Age groups
=IF([age] < 18, "Minor",
    IF([age] < 65, "Adult", "Senior"))

Status Flags​

// Order status
=IF([shipped_date] IS NOT NULL, "Shipped", "Pending")

// Inventory alerts
=IF([stock] < [reorder_point], "Reorder", "OK")

Data Validation​

// Check email format
=IF(FIND("@", [email]) > 0, "Valid", "Invalid")

// Validate phone length
=IF(LEN([phone]) = 10, "Valid", "Check Format")

Default Values​

// Provide default for NULL
=IF(ISBLANK([middle_name]), "N/A", [middle_name])

// Default pricing
=IF([price] > 0, [price], [standard_price])

Advanced Techniques​

Nested IF Statements​

// Multiple conditions
=IF([grade] >= 90, "A",
    IF([grade] >= 80, "B",
        IF([grade] >= 70, "C",
            IF([grade] >= 60, "D", "F"))))

Combined with Text Functions​

// Conditional formatting
=IF([status] = "active",
    UPPER([name]),
    LOWER([name]))

// Conditional concatenation
=IF(ISBLANK([middle]),
    CONCAT([first], " ", [last]),
    CONCAT([first], " ", [middle], " ", [last]))

Conditional Calculations​

// Apply discount conditionally
=IF([quantity] >= 10,
    [price] * 0.9,
    [price])

// Calculate with different formulas
=IF([type] = "hourly",
    [hours] * [rate],
    [salary])

IFS Formula (Multiple Conditions)​

For cleaner multiple conditions, use IFS:

View IFS Formula Reference →

=IFS(
    [grade] >= 90, "A",
    [grade] >= 80, "B",
    [grade] >= 70, "C",
    [grade] >= 60, "D",
    TRUE, "F"
)

Best Practices​

  1. Consider Order - Conditions are evaluated top to bottom
  2. Handle All Cases - Include else clause for unexpected values
  3. Use IFS for Many Conditions - Cleaner than nested IFs
  4. Test Edge Cases - NULL, zero, empty string
  5. Document Logic - Comment complex conditional logic

Common Patterns​

Three-Way Split​

=IF([value] > threshold, "High",
    IF([value] < -threshold, "Low", "Normal"))

Percentage Categorization​

=IF([score] / [total] > 0.8, "Excellent",
    IF([score] / [total] > 0.6, "Good",
        IF([score] / [total] > 0.4, "Fair", "Poor")))

Date-Based Conditions​

=IF([order_date] > TODAY() - 30, "Recent", "Old")

Additional Formula References​

Additional examples will be added soon.