Learning Objectives

By the end of this lesson, learners should be able to:

  • Create formulas in Excel.
  • Use common business functions.
  • Apply relative and absolute cell references.
  • Use logical functions for decision-making.
  • Build simple analytical calculations.

Understanding Formulas

A formula begins with an equal sign (=).

Examples

  • =B2*C2
  • =SUM(D2:D10)

Excel calculates the result automatically.

Common Business Functions

SUM

Adds numbers.

=SUM(D2:D10)

AVERAGE

Calculates the mean.

=AVERAGE(D2:D10)

MAX And MIN

Find highest and lowest values.

COUNT

Counts numeric cells.

COUNTA

Counts non-empty cells.

Relative And Absolute References

Relative Reference

Changes when copied.

Example: =B2*C2

Absolute Reference

Remains fixed.

Example: =$F$1

Useful for tax rates, exchange rates, and commission percentages.

Logical Functions

IF Function

=IF(E2>=10000,”Target Achieved”,”Below Target”)

Used for performance evaluation.

AND Function

Checks multiple conditions.

OR Function

Checks whether at least one condition is true.

Lookup Functions

XLOOKUP / VLOOKUP

Retrieves data from another table.

Example: Retrieve product price using product code.

Business Example: Sales Commission

Sales (USD)

Commission Rate

Commission

12,000

5%

600

8,000

3%

240

Formula: =A2*B2

Error Handling

Common errors:

  • #DIV/0!
  • #N/A
  • #VALUE!

Use IFERROR() to display user-friendly messages.

Practical Exercise

Create a worksheet that calculates:

  • Total sales,
  • Average sales,
  • Highest sales,
  • Lowest sales,
  • Commission,
  • Performance status.

Learning Materials / Reference Materials

  • Microsoft Excel Functions Reference.
  • ExcelJet Function Tutorials.
  • Walkenbach, J. Excel Formulas and Functions.

Lesson Summary

Excel formulas and functions automate calculations, reduce errors, and support efficient business analysis and decision-making.

PreviousNext