Platform Banner

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
5230 views

Available in Microsoft365

Description

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.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

Vu 2600 fois

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 pivot tables, and take advantage of all that Excel has to offer!

ALL COURSES