Data Analyst Full Course for Beginners | Part 2: Formulas and Functions - I

Hello, everyone! How are you all? Today, we are diving into the second part of our series on Data Analysis. If you haven’t seen the first part yet, along with the roadmap I shared earlier, I strongly recommend watching them first to get a holistic understanding. The links will be in the description. Let’s get started with today’s session, which will focus on Formulas and Functions in spreadsheets.

Before we proceed to complex topics like VLOOKUP and HLOOKUP in the next session, we’ll solidify our understanding of basic operators and their usage. I’ve also prepared a downloadable practice sheet to help you follow along.

What Are Formulas?

A formula is an expression used in spreadsheet cells to perform calculations or operations. Every formula starts with an = sign, followed by the desired operation. Formulas can include references to other cells, operators, or functions to generate results.

Basic Operators in Formulas:

  1. Exponential (^) – Use Shift + 6 to denote power.

  2. Multiplication (*) – Represented as *.

  3. Division (/) – Represented as /.

  4. Addition (+) – Represented as +.

  5. Subtraction (-) – Represented as -.

  6. Concatenation (&) – Joins two text values into one.

  7. Comparison Operators:

    • Equal to (=)

    • Less than (<)

    • Greater than (>)

Operator Precedence (BODMAS Rule):

Like mathematical operations, formulas follow the BODMAS rule:

  1. Brackets

  2. Orders (Exponential)

  3. Division and Multiplication

  4. Addition and Subtraction

Example:

If you type =2 + 3 * 4, the multiplication happens first, so the result is 14 (not 20).

Cell Referencing in Formulas

Instead of hardcoding values, you can reference other cells to make your formulas dynamic and reusable.

Example:

  1. Manual Calculation:

    • Typing =2 + 3 gives 5.

  2. Cell Referencing:

    • If A1 has 2 and A2 has 3, typing =A1 + A2 in A3 will also give 5.

    • If you update A1 or A2, A3 will automatically recalculate the result.

Arithmetic Operations Examples

OperationFormulaResult
Addition=A1 + A2Sum
Subtraction=A1 - A2Difference
Multiplication=A1 * A2Product
Division=A1 / A2Quotient
Exponential=A1 ^ A2Power

Functions in Spreadsheets

A function is a predefined formula that simplifies specific calculations. Functions save time and are more efficient than manual calculations.

Why Use Functions?

  1. Save time with built-in operations.

  2. Reduce the chances of errors.

  3. Provide versatility for large datasets.

Common Types of Functions:

  1. Aggregate Functions: Perform calculations like SUM, AVERAGE, MAX, MIN, COUNT.

  2. Logical Functions: Return results based on conditions (IF, AND, OR, NOT).

  3. Text Functions: Manipulate text values.

  4. Date & Time Functions: Handle date and time operations.

Examples of Aggregate Functions:

  1. SUM: Adds values.

    • Formula: =SUM(A1:A10)

  2. AVERAGE: Finds the mean.

    • Formula: =AVERAGE(A1:A10)

  3. COUNT: Counts non-empty cells.

    • Formula: =COUNT(A1:A10)

  4. MAX: Finds the highest value.

    • Formula: =MAX(A1:A10)

  5. MIN: Finds the lowest value.

    • Formula: =MIN(A1:A10)

Logical Functions:

Logical functions allow you to perform checks and return results based on conditions.

Common Logical Functions:

  1. IF:

    • Syntax: =IF(condition, value_if_true, value_if_false)

    • Example: =IF(A1>10, "Yes", "No")

  2. AND:

    • Syntax: =AND(condition1, condition2)

    • Example: =AND(A1>10, B1<5)

  3. OR:

    • Syntax: =OR(condition1, condition2)

    • Example: =OR(A1>10, B1<5)

  4. NOT:

    • Syntax: =NOT(condition)

    • Example: =NOT(A1>10)

Assignment for Practice

To strengthen your understanding, try these tasks on the provided spreadsheet:

  1. Use SUM to calculate the total.

  2. Find the AVERAGE of a column.

  3. Use IF to check if values are greater than a threshold.

  4. Use AND/OR to test multiple conditions.

Download the practice sheet and share your results at careers@hjobs.in

Conclusion

In this session, we explored the basics of formulas and functions in spreadsheets, which form the backbone of data analysis. Mastering these will prepare you for more advanced topics like VLOOKUP and HLOOKUP, which we’ll cover in the next part. Don’t forget to practice and share your doubts in the comments or our community forum.

Download the practice sheet​

Download Now