Datanalytics

2.1-Formula and Function

7 min read

2025-08-11 11:57

Status:

Tags: Data Analysis Excel

Excel Formulas and Functions - Complete Guide

Tags: DataAnalysis Excel Reference

Overview

Understanding the distinction between formulas and functions is fundamental to working effectively with Excel’s calculation capabilities. This comprehensive guide covers everything from basic concepts to advanced techniques for building robust spreadsheet solutions.

Example Dataset: Sales data in columns A:B (A1:B6).

ProductSales
A100
B200
C150
D300
E250

Formula Fundamentals

What Are Formulas?

  • Definition: An expression that calculates the value of a cell
  • Structure: Always begins with an equals sign (=)
  • Components: Can contain values, cell references, operators, and functions
  • Purpose: Performs mathematical operations, logical evaluations, and data manipulation

Formula Syntax Basics

= [Function_Name]([Arguments]) [Operator] [Cell_Reference] [Value]

Example: =SUM(A1:A10)*B2+100

Example Dataset: Use the sales data above. Formula =SUM(B2:B6)*1.1 calculates total sales with 10% markup.

Order of Operations (PEMDAS)

  1. Parentheses ( )
  2. Exponents ^
  3. Multiplication * and Division /
  4. Addition + and Subtraction -

Example Dataset: Same sales data. Formula =(SUM(B2:B6)+50)/5 averages total sales plus bonus.

Cell Reference Types

Relative References

  • Format: A1, B2, C3
  • Behavior: Changes when formula is copied to other cells
  • Example: =A1+B1 becomes =A2+B1 when copied down one row

Example Dataset: Add column C for doubles (C2: =B2*2). Copy down: C3 becomes =B3*2.

Absolute References

  • Format: $A$1, $B$2, $C$3
  • Behavior: Remains fixed when formula is copied
  • Example: =$A$1+B1 keeps column A, row 1 fixed

Example Dataset: Add tax rate in D1 (0.08). Formula in E2: =B2*(1+$D$1). Copy down keeps tax fixed.

Mixed References

  • Format: $A1 (fixed column) or A$1 (fixed row)
  • Behavior: Partially fixed when copied
  • Use Case: Creating multiplication tables or cross-tabulations

Example Dataset: Rows 1:5 multipliers (1-5), column A products. Formula B2: =A2*$B$1. Copy across/ down adjusts row, fixes row header.

Functions Explained

What Are Functions?

  • Definition: Predefined formulas built into Excel
  • Examples: IF(), FILTER(), SUM(), AVERAGE(), VLOOKUP()
  • Purpose: Perform specific actions when invoked or called
  • Advantage: Simplify complex calculations and provide standardized operations

Example Dataset: Same sales data. =SUM(B2:B6) totals 1000.

Function Categories

  • Mathematical: SUM(), AVERAGE(), MAX(), MIN()
  • Logical: IF(), AND(), OR(), NOT()
  • Text: CONCATENATE(), LEFT(), RIGHT(), MID()
  • Date/Time: TODAY(), NOW(), DATEDIF()
  • Lookup: VLOOKUP(), INDEX(), MATCH()

Example Dataset: For math: =AVERAGE(B2:B6) = 200. Add dates in C (e.g., C2: 1/1/2025), =DATEDIF(C2,TODAY(),"D") days since.

The IF Function - Complete Guide

Basic IF Function

The IF function is Excel’s most versatile logical function, allowing you to make decisions based on conditions.

Syntax: IF(logical_test, value_if_true, value_if_false)

Components Explained

  • logical_test: The condition to evaluate (returns TRUE or FALSE)
  • value_if_true: Result when condition is TRUE
  • value_if_false: Result when condition is FALSE

Simple Examples

=IF(A1>10, "Pass", "Fail")
=IF(B2="", "Empty", "Has Value")
=IF(C3>=100, C3*0.1, 0)

Example Dataset: Add column D for bonus (D2: =IF(B2>200,"High","Low")). For sales >200: “High”.

Nested IF Statements

For multiple distinct conditions, you can nest IF functions:

=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", IF(A1>=60, "D", "F"))))

Modern Alternative - IFS Function (Excel 2016+):

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

Example Dataset: Add scores in E2:E6 (95,85,75,65,55). F2: =IFS(E2>=90,"A",E2>=80,"B",E2>=70,"C",E2>=60,"D",TRUE,"F").

IF with Other Functions

IF with Mathematical Functions

=IF(SUM(A1:A10)>1000, SUM(A1:A10)*0.05, 0)
=IF(AVERAGE(B1:B10)>=75, "Above Average", "Below Average")

Example Dataset: G2: =IF(SUM(B2:B6)>1000,SUM(B2:B6)*0.05,0) = 50 bonus.

IF with Text Functions

=IF(LEN(A1)>0, UPPER(A1), "No Data")
=IF(ISERROR(VLOOKUP(A1,Table,2,FALSE)), "Not Found", VLOOKUP(A1,Table,2,FALSE))

Example Dataset: Add lookup table in H:I (Product: A, Price: 10). J2: =IF(ISERROR(VLOOKUP(A2,H:I,2,FALSE)),"Not Found",VLOOKUP(A2,H:I,2,FALSE)).

Logical Functions Deep Dive

AND Function

Purpose: Returns TRUE only when ALL conditions are TRUE

Syntax: AND(logical1, logical2, ...)

Basic Usage

=AND(A1>10, B1<20, C1="Yes")

Example Dataset: Add columns F: Stock (50,100,30), G: Active (Yes,Yes,No). H2: =AND(B2>150,F2>40,G2="Yes").

With IF Function

=IF(AND(K34>=$L$30, L34>=$M$30, M34>=$N$30, N34>=$O$30, O34>=$P$30), "Extend", "No Extension")

Example Dataset: Assume thresholds in row 30. For sales data extended: =IF(AND(B2>=200,F2>=50),"Extend","No").

Truth Table for AND

Condition 1Condition 2Result
TRUETRUETRUE
TRUEFALSEFALSE
FALSETRUEFALSE
FALSEFALSEFALSE

OR Function

Purpose: Returns TRUE when ANY condition is TRUE

Syntax: OR(logical1, logical2, ...)

Basic Usage

=OR(A1="Red", A1="Blue", A1="Green")

Example Dataset: I2: =OR(A2="A",A2="B",A2="C") for top products.

With IF Function

=IF(OR(K34>=$L$30, L34>=$M$30, N34>=$O$30, O34>=$P$30), "Credit", "No Credit")

Example Dataset: J2: =IF(OR(B2>=200,F2>=100),"Credit","No").

Truth Table for OR

Condition 1Condition 2Result
TRUETRUETRUE
TRUEFALSETRUE
FALSETRUETRUE
FALSEFALSEFALSE

NOT Function

Purpose: Reverses the logical value

Syntax: NOT(logical)

Examples

=NOT(A1>10)  // TRUE if A1 is NOT greater than 10
=IF(NOT(ISBLANK(A1)), A1*2, "No Value")

Example Dataset: K2: =NOT(B2<100) flags low sales inverse.

XOR Function

Purpose: Returns TRUE when an odd number of conditions are TRUE

Syntax: XOR(logical1, logical2, ...)

Example

=XOR(A1>10, B1>20)  // TRUE if only one condition is met

Example Dataset: L2: =XOR(B2>200,F2>50) for exclusive flags.

Complex Logical Combinations

Nested AND/OR Logic

=IF(AND(OR(L34>=$M$30, N34>=$O$30, O34>=$P$30), K34>=$L$30), "Eligible", "Not Eligible")

Business Logic Breakdown:

  • Primary Requirement: K34>=$L$30 (must be TRUE)
  • Secondary Options: At least one must be TRUE:
    • L34>=$M$30 OR
    • N34>=$O$30 OR
    • O34>=$P$30

Example Dataset: M2: =IF(AND(OR(B2>=200,F2>=100),B2>=150),"Eligible","Not").

Real-World Example: Loan Eligibility

=IF(AND(
    AND(Sales_Last_Year >= 250000, Years_In_Business >= 2),
    OR(Asset_Value >= 300000, Credit_Rating_1 >= 3, Credit_Rating_2 >= 7),
    Debt_To_Income <= 0.4
), "Approved", "Denied")

Criteria:

  1. Must Have Both:
    • Sales ≥ $250,000 last year
    • In business ≥ 2 years
  2. Must Have At Least One:
    • Assets ≥ $300,000 OR
    • Credit Rating 1 ≥ 3 OR
    • Credit Rating 2 ≥ 7
  3. Must Have: Debt-to-income ≤ 40%

Example Dataset: New table N:P (Sales:300k, Years:3, Assets:350k, CR1:4, CR2:6, DTI:0.3). Q1: Above formula = “Approved”.

Advanced Techniques

Array Formulas with IF

Legacy Array Formula (Ctrl+Shift+Enter):

{=SUM(IF(A1:A100>10, B1:B100, 0))}

Dynamic Array Formula (Excel 365):

=SUM(IF(A1:A100>10, B1:B100, 0))

Example Dataset: Sales data. =SUM(IF(B2:B6>200,B2:B6,0)) sums high sales = 750.

IF with FILTER Function (Excel 365)

=FILTER(A1:C100, (B1:B100>1000)*(C1:C100="Active"))

Example Dataset: Add status column. Filters active high-sales rows.

SWITCH Function Alternative

Instead of multiple nested IFs:

=SWITCH(A1, "A", "Excellent", "B", "Good", "C", "Fair", "D", "Poor", "Invalid Grade")

Example Dataset: Grades in R2:R6. S2: =SWITCH(R2,"A","Excellent","B","Good",TRUE,"Invalid").

Conditional Aggregation

=SUMIF(Status_Range, "Active", Sales_Range)
=AVERAGEIFS(Scores, Department, "Sales", Performance, ">80")
=COUNTIFS(Region, "North", Quarter, "Q1", Revenue, ">50000")

Example Dataset: Add dept/status. =SUMIF(G2:G6,"Yes",B2:B6) sums active.

Error Handling & Troubleshooting

Common Errors and Solutions

VALUE! Error

Cause: Wrong data type in calculation Example: =A1+B1 where A1 contains text Solution: Use ISNUMBER() or VALUE() functions

=IF(ISNUMBER(A1)*ISNUMBER(B1), A1+B1, "Cannot Calculate")

Example Dataset: If B2 text “abc”, formula flags “Cannot Calculate”.

REF! Error

Cause: Invalid cell reference Solution: Check for deleted rows/columns, fix references

0! Error

Cause: Division by zero Solution: Check denominator before dividing

=IF(B1=0, "Cannot Divide", A1/B1)

Example Dataset: Divide by stock=0: Flags message.

A Error

Cause: Lookup function can’t find value Solution: Use IFERROR or IFNA

=IFERROR(VLOOKUP(A1,Table,2,FALSE), "Not Found")

Example Dataset: Missing product lookup: “Not Found”.

Error Prevention Strategies

=IFERROR(your_formula, "Error Occurred")
=IFNA(your_lookup_formula, "No Match Found")
=IF(ISERROR(your_formula), "Check Input", your_formula)

Example Dataset: Wrap VLOOKUP: Handles missing gracefully.

Performance Optimization

Efficient Formula Design

Avoid Volatile Functions When Possible

Volatile Functions (recalculate frequently): NOW(), TODAY(), RAND(), OFFSET() Alternative Approaches: Use static values when dynamic updating isn’t needed

Example Dataset: Use fixed date instead of TODAY() for historical sales.

Minimize Array Calculations

Instead of:

=SUM(IF(A1:A1000="X", B1:B1000*C1:C1000, 0))

Use:

=SUMPRODUCT((A1:A1000="X")*B1:B1000*C1:C1000)

Example Dataset: Large sales array: SUMPRODUCT faster for filtered products.

Optimize Lookup Functions

VLOOKUP with Exact Match:

=VLOOKUP(A1, Table, 2, FALSE)

INDEX/MATCH Alternative (more flexible):

=INDEX(Return_Column, MATCH(A1, Lookup_Column, 0))

Example Dataset: INDEX/MATCH on product-price table, avoids left-column limit.

Large Dataset Strategies

  • Use structured tables for better performance
  • Limit calculation ranges to necessary data
  • Consider PowerQuery for complex transformations
  • Implement staged calculations for complex logic

Example Dataset: Convert sales range to Table: Formulas auto-extend.

Best Practices

Formula Construction

  1. Start Simple: Build complexity gradually

  2. Use Named Ranges: Makes formulas more readable

    =IF(Sales_Amount >= Quota, "Met", "Not Met")

  3. Document Complex Logic: Add comments to cells

  4. Test Edge Cases: Empty cells, zero values, text in number fields

Example Dataset: Name B2:B6 “Sales”. Formula: =IF(SUM(Sales)>1000,"Met","Not").

Logical Function Guidelines

  • AND(): When ALL conditions must be satisfied
  • OR(): When ANY condition can satisfy the requirement
  • Nested Combinations: For complex business rules with multiple condition groups
  • Consider Alternatives: IFS(), SWITCH() for multiple conditions

Code Organization

// Good: Clear, readable structure
=IF(AND(
    Revenue >= Target_Revenue,
    Profit_Margin >= 0.15,
    Customer_Satisfaction >= 4.0
), "Excellent", "Needs Improvement")
 
// Avoid: Hard to read, maintain
=IF(AND(A1>=B1,C1>=0.15,D1>=4),"Excellent","Needs Improvement")

Example Dataset: Named ranges for revenue etc., apply to sales data.

Maintenance and Updates

  • Use consistent naming conventions
  • Document assumptions and business rules
  • Regular testing with new data
  • Version control for complex workbooks

Keyboard Shortcuts for Efficiency

  • F4: Toggle reference types (1, AA1, A1)
  • Ctrl+Shift+Enter: Create array formula
  • F9: Evaluate selected portion of formula
  • Ctrl+`: Show/hide formulas

References

Do you need any clarity? References

Backlinks