MS Excel - Excel Formulas

In Microsoft Excel, formulas are the backbone of data analysis, helping you perform calculations, analyze trends, and automate tasks. A formula in Excel always starts with an equals sign (=) and can include operators, cell references, functions, and constants. 

1. Basics of Excel Formulas

• Syntax:
  =operand1 operator operand2
  Example: =A1 + B1

• Start with “=”: Every formula begins with an equals sign.

• Use Cell References: Instead of hardcoding numbers, reference cells to make formulas dynamic.

• Order of Operations: Follows BODMAS:
  Brackets → Orders → Division → Multiplication → Addition → Subtraction.

2. Types of Excel Formulas

A. Arithmetic Formulas

Used for basic mathematical operations:

• Addition: =A1 + B1

• Subtraction: =A1 - B1

• Multiplication: =A1 * B1

• Division: =A1 / B1

• Power/Exponent: =A1 ^ 2

• Percentage: =A1 * 10%

B. Text Formulas

Manipulate and format text values:

• Concatenate / Join Text:
  =A1 & " " & B1 → Joins first and last names.
  Or use: =CONCAT(A1, " ", B1)

• Change Case:

  • Uppercase → =UPPER(A1)

  • Lowercase → =LOWER(A1)

  • Proper Case → =PROPER(A1)

• Extract Text:

  • Left → =LEFT(A1, 5) → First 5 characters.

  • Right → =RIGHT(A1, 3) → Last 3 characters.

  • Mid → =MID(A1, 3, 4) → From 3rd character, take 4 letters.

• Remove Extra Spaces:
  =TRIM(A1)

• Find Length:
  =LEN(A1)

C. Date and Time Formulas

Work with dates and time easily:

• Today’s Date: =TODAY()

• Current Date & Time: =NOW()

• Add/Subtract Days: =A1 + 7

• Difference Between Dates: =DAYS(A2, A1)

• Extract Year, Month, Day:

  • Year → =YEAR(A1)

  • Month → =MONTH(A1)

  • Day → =DAY(A1)

• Current Time Only: =TEXT(NOW(),"hh:mm:ss")

D. Logical Formulas

Used for decision-making:

• IF Function:
  =IF(A1>50,"Pass","Fail")

• AND Function:
  =AND(A1>50, B1>40) → Returns TRUE if both are true.

• OR Function:
  =OR(A1>50, B1>40) → Returns TRUE if any one is true.

• IF with AND/OR:
  =IF(AND(A1>50,B1>40),"Pass","Fail")

E. Lookup and Reference Formulas

Search for data within a table or range:

• VLOOKUP:
  =VLOOKUP(101, A2:D20, 2, FALSE)
  Looks for employee ID 101 in the first column and returns value from the 2nd column.

• HLOOKUP: Similar to VLOOKUP but searches horizontally.

• INDEX + MATCH:
  =INDEX(B2:B10, MATCH(105, A2:A10, 0))
  More flexible than VLOOKUP.

• XLOOKUP (Excel 365/2021):
  =XLOOKUP(105, A2:A10, B2:B10, "Not Found")

F. Statistical Formulas

Used for analysis:

• Sum: =SUM(A1:A10)

• Average: =AVERAGE(A1:A10)

• Minimum: =MIN(A1:A10)

• Maximum: =MAX(A1:A10)

• Count Numbers: =COUNT(A1:A10)

• Count All (including text): =COUNTA(A1:A10)

• Conditional Sum: =SUMIF(A1:A10,">50")

• Conditional Count: =COUNTIF(A1:A10,"Pass")

G. Financial Formulas

• PMT → Calculate loan payments:
  =PMT(interest_rate, periods, loan_amount)

• FV → Future value of investment:
  =FV(rate, periods, payment)

3. Excel Formula Tips

• Absolute vs Relative References:

  • Relative → =A1+B1 (changes when copied)

  • Absolute → =$A$1+$B$1 (fixed reference)

• Use Formula Auditing: Go to Formulas → Evaluate Formula to debug.

• Error Handling:

  • =IFERROR(A1/B1,"Error") → Avoids showing #DIV/0!.