Mastering MS Excel: Formulas and Functions
**1. Introduction to Formulas and Functions:
Formula: An equation that performs calculations.
Function: A predefined formula.
2. Commonly Used Functions:
SUM: Adds up all the numbers in a range.
=SUM(A1:A5)
AVERAGE: Calculates the average of numbers.
=AVERAGE(B1:B10)
COUNT: Counts the number of cells that contain numbers.
=COUNT(C1:C8)
IF: Makes logical comparisons and returns values.
=IF(D1>10, “Yes”, “No”)
3. Absolute vs. Relative References:
Relative Reference: Adjusts when copied to other cells.
Absolute Reference: Stays constant when copied.
=$A$1 (Absolute)
=A1 (Relative)
4. Nested Functions:
Combine multiple functions within one formula.
=IF(SUM(A1:A5)>10, “High”, “Low”)
5. VLOOKUP Function:
Searches for a value in the first column of a range and returns a value in the same row from another column.
=VLOOKUP(E1, A1:B10, 2, FALSE)
6. HLOOKUP Function:
Similar to VLOOKUP but searches for the value in the first row.
=HLOOKUP(E1, A1:B10, 2, FALSE)
7. INDEX and MATCH Functions:
INDEX: Returns the value of a cell in a specified row and column.
=INDEX(A1:B10, 3, 2)
MATCH: Searches for a specified value in a range and returns its relative position.
=MATCH(“Apples”, C1:C10, 0)
8. IFERROR Function:
Handles errors by specifying the value to return if an error occurs.
=IFERROR(D1/B1, “Error in calculation”)
