Excel - More complex formulas Tutorial

In this video, you will learn about more complex formulas in Microsoft 365. The video covers advanced formula techniques and demonstrates how to create complex formulas in Excel using functions and operators.
This will help you enhance your data analysis skills and make more sophisticated calculations in Excel.

4:17
4899 views

Available in Microsoft365

Objectifs :

This video aims to teach viewers how to create complex formulas in Excel using multiple operators, cell references, and functions. It focuses on calculating sales commissions and weighted scores for students, emphasizing the importance of understanding the order of operations and absolute cell references.

Chapitres :

Calculating Sales Commission
In this section, we learn how to calculate the sales commission based on net revenue. The formula involves subtracting the cost of goods sold from the sales price to determine the net profit. This calculation is enclosed in parentheses to ensure it is executed first. The net profit is then multiplied by the commission percentage found in cell B4, which is 3%. For example, if the sales price is $4,000 and the cost of goods sold is $1,800, the net profit would be $2,200. Multiplying this by 3% results in a commission of $66.
Understanding Weighted Scores
Next, we explore how to determine weighted scores for students. Different tests contribute varying percentages to a student's final grade. For instance, Test 1 and Test 2 each account for 10%, while the midterm and final tests each account for 40%. To calculate the weighted score for a student named Bob, we multiply his Test 1 score by its weight. By pressing F4 after selecting the weight value in cell B3, we create an absolute cell reference, ensuring that the weight for Test 1 remains constant when copying the formula. This process is repeated for Bob's midterm score and other tests.
Copying Formulas
To copy the formula across cells, click on the cell containing the formula, then hover over the bottom right corner until a black plus sign appears. Click and drag to the right to apply the formula to adjacent cells. This method allows for quick calculations of weighted scores for multiple students.
Order of Operations
Understanding the order of operations is crucial when working with formulas. For example, in a formula involving multiple operations, we first evaluate the innermost parentheses. If we have a formula like (5 + 3) * (10 - 2), we first calculate 5 + 3, which equals 8, and then 10 - 2, which equals 8. The multiplication follows, resulting in 8 * 8 = 64. This section emphasizes the importance of following the correct order to achieve accurate results.
Using Functions and Nested Parentheses
The video concludes by demonstrating how to use functions and formulas with multiple operators, including nested parentheses. Functions and cell references are evaluated to produce resulting numbers. The parentheses following a function name serve as containers for the function's arguments, such as numbers or cell references. For example, in a formula like =SUM(A1:A5), the SUM function calculates the total of the values in cells A1 through A5. Understanding these concepts equips viewers with the skills to perform basic math operations in Excel effectively.

FAQ :

What is a sales commission and how is it calculated?

A sales commission is a payment made to sales personnel based on the sales they generate. It is typically calculated as a percentage of the net revenue, which is the sales price minus the cost of goods sold.

What does net revenue mean?

Net revenue refers to the total revenue from sales after deducting the cost of goods sold (COGS). It represents the actual income generated from sales.

How do I use absolute cell references in Excel?

To use absolute cell references in Excel, you can add a dollar sign before the column letter and row number (e.g., $B$3). This ensures that the reference remains constant when copying the formula to other cells.

What are weighted scores and why are they important?

Weighted scores are scores that are adjusted based on the importance of each test or assignment in determining a final grade. They are important because they reflect the varying significance of different assessments.

What is the order of operations in Excel formulas?

The order of operations in Excel formulas follows the rules of PEMDAS: Parentheses first, then Exponents, followed by Multiplication and Division (from left to right), and finally Addition and Subtraction (from left to right).

Can I use functions with multiple operators in Excel?

Yes, you can use functions with multiple operators in Excel. Functions and cell references are evaluated according to the order of operations, allowing for complex calculations.

Quelques cas d'usages :

Calculating Sales Commissions

In a sales department, managers can use the formula for calculating sales commissions based on net revenue to incentivize their sales team. By adjusting the commission percentage, they can motivate employees to increase sales while ensuring profitability.

Grading Student Performance

Teachers can apply weighted scores to calculate final grades for students based on different assessments. By assigning different weights to tests, they can ensure that more significant tests, like midterms and finals, have a greater impact on the final grade.

Budgeting and Financial Analysis

Financial analysts can use Excel formulas to calculate net revenue and sales commissions when analyzing business performance. This helps in making informed decisions about pricing strategies and cost management.

Project Management

Project managers can utilize weighted scores to evaluate team performance across various tasks. By assigning weights to different project milestones, they can assess overall progress and allocate resources effectively.

Glossaire :

Sales Commission

A payment made to sales personnel based on the sales they generate, typically calculated as a percentage of the net revenue.

Net Revenue

The total revenue from sales after deducting the cost of goods sold (COGS).

Cost of Goods Sold (COGS)

The direct costs attributable to the production of the goods sold by a company, including materials and labor.

Absolute Cell Reference

A cell reference that remains constant, even when the formula is copied to another cell. It is denoted by a dollar sign (e.g., $B$3).

Weighted Scores

Scores that are adjusted based on the importance or weight of each test or assignment in determining a final grade.

Order of Operations

The rules that determine the sequence in which calculations are performed in a mathematical expression, often remembered by the acronym PEMDAS (Parentheses, Exponents, Multiplication and Division, Addition and Subtraction).

Functions

Predefined formulas in Excel that perform calculations using specific values, called arguments, such as SUM, AVERAGE, etc.

00:00:05

operators, cell references and functions.

00:00:09

We're going to calculate the sales commission based off of the net revenue.

00:00:14

To do this, I take the sales price and subtract from it

00:00:19

the cost of goods sold.

00:00:21

This is in parentheses to ensure it occurs first.

00:00:25

I then multiply the net profit by the commission percentage in B4, 3%.

00:00:31

This returns the commission.

00:00:35

Let's take a look at how this works in a formula.

00:00:38

Because the cell references are in parentheses,

00:00:41

first the cost of goods sold is subtracted from the sales price.

00:00:46

This returns a net profit of $3200.

00:00:50

This is then multiplied by 3%, returning a commission of $96.

00:00:58

Now we're going to determine the weighted scores for students.

00:01:02

By weighted scores, I mean that different tests account

00:01:05

for a different percentage of the student's final grade.

00:01:09

Test 1 and 2 each account for 10%

00:01:14

and the midterm and final test each account for 40%.

00:01:21

To do this, I multiply the test 1 score for Bob by its weight.

00:01:27

I press F4 after I select the weight value in B3

00:01:31

so that when I copy the formula, the weight for test 1

00:01:34

will always remain cell B3.

00:01:38

This is referred to as absolute cell reference.

00:01:41

I then add Bob's midterm score multiplied by its weight

00:01:45

in B4 and press F4 again.

00:01:48

I follow the same steps for test 2 and the final test.

00:01:53

To copy the formula, I click the cell,

00:01:56

put the mouse pointer over the bottom right-hand corner of the cell

00:01:59

until I get a black plus sign, click the left mouse button

00:02:04

and drag the border to the right.

00:02:08

Here are the weighted scores for the students.

00:02:11

Let's take a look at what's happening in this formula for Bob's weighted score.

00:02:17

Each of the test scores and their weights are multiplied resulting in this formula.

00:02:23

Then the numbers are added together resulting in 74.5.

00:02:30

What's the value returned by this formula?

00:02:34

First, we do the innermost parentheses,

00:02:38

5-3 is 2, resulting in this formula.

00:02:44

Next, we do the remaining parentheses,

00:02:49

10*2 is 20 and 1-4 is -3,

00:02:55

resulting in this formula.

00:02:58

Now we do multiplication and division from left to right,

00:03:04

6*20 is 120,

00:03:09

divided by 3 is 40, resulting in this formula.

00:03:15

Lastly, we do addition and subtraction from left to right.

00:03:21

4+40 is 44,

00:03:24

-3 is 41.

00:03:29

You can use functions and formulas with multiple

00:03:32

operators including nested parentheses.

00:03:35

Functions and cell references are evaluated and treated as the resulting numbers.

00:03:41

The parentheses that follow a function name act only as

00:03:44

a container for the function's arguments such as numbers or cell references

00:03:50

and don't affect the order of operations in a formula.

00:03:55

Let's take a look at what's happening in this formula.

00:03:58

The functions and cell references are evaluated resulting in this formula.

00:04:04

The operations in parentheses occur first resulting in

00:04:07

a formula of 12-3 and this is 9.

00:04:13

Now you've got a pretty good idea about how to do basic math in Excel.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2492 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES