2.3 Formulas, Functions & Calculations

One of the most important features of Microsoft Excel is its ability to perform calculations automatically. Formulas and functions allow users to calculate totals, averages, percentages, minimum and maximum values, counts, and many other results.

What You Will Learn

• Understand Excel formulas.
• Use mathematical operators in formulas.
• Use cell references in calculations.
• Understand basic Excel functions.
• Calculate totals and averages.
• Find minimum and maximum values.
• Count numerical data.
• Calculate percentages.
• Copy formulas to other cells.

What is a Formula?

A formula is an expression used to perform a calculation in Excel. Formulas normally begin with an equals sign (=).

For example, if cell A1 contains 100 and cell B1 contains 50, the formula =A1+B1 calculates the total of the two values.

Basic Mathematical Operators

• + for addition.
• – for subtraction.
• * for multiplication.
• / for division.
• ^ for exponentiation.

Using Cell References

Instead of entering numbers directly into a formula, Excel formulas can use cell references. This allows the result to update automatically when the source values change.

For example, =B2*C2 multiplies the value in B2 by the value in C2.

SUM Function

The SUM function adds values together. For example, =SUM(B2:B10) adds all the values from B2 through B10.

AVERAGE Function

The AVERAGE function calculates the arithmetic average of selected numerical values. For example, =AVERAGE(C2:C10) calculates the average of the values in the specified range.

MIN Function

The MIN function returns the smallest value from a selected range. For example, =MIN(D2:D10) identifies the lowest value in the range.

MAX Function

The MAX function returns the largest value from a selected range. For example, =MAX(D2:D10) identifies the highest value in the range.

COUNT Function

The COUNT function counts cells containing numerical values. For example, =COUNT(B2:B20) counts the cells in the specified range that contain numbers.

Percentage Calculations

Excel can be used to calculate percentages using formulas. For example, if a student scores 80 out of 100, the percentage can be calculated using a formula such as =80/100 and then formatting the result as a percentage.

Copying Formulas

Formulas can often be copied to other cells using the fill handle. Excel automatically adjusts relative cell references when the formula is copied to a different location.

Practical Activity

Create a student marks worksheet with the following columns:

• Student Name
• English
• Mathematics
• Science
• Total
• Average
• Percentage

1. Enter information for at least five students.
2. Use a SUM formula to calculate each student’s total marks.
3. Use the AVERAGE function to calculate the average marks.
4. Calculate the percentage for each student.
5. Use the MAX function to find the highest total.
6. Use the MIN function to find the lowest total.
7. Use the COUNT function to count the number of students with numerical marks.
8. Format the percentage column appropriately.

Calculation Example

Suppose a student has the following marks:

• English: 75
• Mathematics: 82
• Science: 78

The total can be calculated using a SUM formula, while the average can be calculated using the AVERAGE function. The percentage can then be calculated based on the maximum possible marks.

Learning Outcome

After completing this section, learners will be able to create formulas, use cell references, apply common Excel functions, calculate totals and averages, determine minimum and maximum values, count numerical data, and perform basic percentage calculations.