Formulas and Functions in Excel – Excel Tutorial 4

Formulas and Functions in Excel (Excel Tutorial 4)

Formulas and functions are the heart and soul of Excel’s power. They allow you to automate calculations, manipulate data, and unlock valuable insights from your spreadsheets. This section will equip you with the knowledge to become a formula pro!

What are Formulas?

A formula in Excel is an expression that performs a calculation on the data in your spreadsheet. Formulas always begin with an equal sign (=) followed by instructions that tell Excel what to calculate. These instructions can involve cell references, numbers, operators (like +, -, *, /), and built-in functions.

Here’s an example of a simple formula:

=A1 + B1

This formula adds the values in cells A1 and B1 and displays the result in the cell where you entered the formula.

Building Simple Formulas

Let’s explore some commonly used basic formulas in Excel:

• SUM: This function adds up a range of cells. For example, =SUM(A1:A10) calculates the total of values in cells A1 through A10.
• AVERAGE: Calculates the average of a range of cells. For example, =AVERAGE(B2:B15) finds the average of the values in cells B2 to B15.
• COUNT: Counts the number of cells in a range that contain numbers. For example, =COUNT(C1:C20) counts the number of cells in range C1:C20 that have numerical values.
• MIN and MAX: These functions find the minimum and maximum values in a range, respectively. For example, =MIN(D3:D22) finds the smallest value in cells D3 to D22, and =MAX(E4:E25) finds the largest value in cells E4 to E25.

These are just a few basic formulas to get you started. Excel offers a vast library of functions for various purposes, and we’ll explore some more advanced ones later.

Using Cell References in Formulas

Cell references are crucial for building dynamic formulas. A cell reference refers to the address (e.g., A1, B3) of a cell containing the data you want to use in your formula. This allows you to write formulas that automatically update when the values in the referenced cells change.

Here’s an example:

Imagine you have a table calculating product sales in columns A (Product Name), B (Quantity Sold), and C (Unit Price). You can use a formula in column D (Total Price) to automatically calculate the total price for each product by multiplying the quantity sold by the unit price.

In cell D2, you can enter the formula: =B2*C2. This formula multiplies the value in cell B2 (Quantity Sold) by the value in cell C2 (Unit Price) and displays the result (Total Price) in cell D2.

Since the formula uses cell references, if you change the quantity sold in cell B2 or the unit price in cell C2, the total price in cell D2 will automatically recalculate.

Logical Functions (IF, AND, OR)

Logical functions allow you to make decisions based on conditions within your data. Here are three common logical functions:

• IF: The IF function checks a condition and returns a specific value if the condition is true, and a different value if it’s false.

Example:

In your sales table, you might want to offer a discount for orders above a certain amount. You can use an IF function in a new column (e.g., E) to display “Discount” if the total price in column D (cell D2 in our example) is greater than $100, and “No Discount” otherwise. The formula in cell E2 would look like:

=IF(D2>100,"Discount","No Discount")

• AND: The AND function checks if multiple conditions are true simultaneously. The function only returns TRUE if all specified conditions are met.

Example:

Building upon the previous example, you might want to offer the discount only for orders above $100 placed by customers in a specific region (let’s say “West”). You can combine the IF function with the AND function to achieve this. The formula in cell E2 would be:

=IF(AND(D2>100,A2="West"),"Discount","No Discount")

In this formula, the discount is only applied if both conditions (total price > $100 and customer region = “West”) are true.

• OR: The OR function checks if at least one of