1. Aggregate and Arithmetic operations
We've already covered how to use basic arithmetic. In this video, we'll discuss the order of operations and learn about aggregate functions to take our analysis a step further.
2. Order of operations: problem
Let's say you have the following math problem. What part do you calculate first? Do you go left to right? Do you subtract before multiplying? If you calculate it in the wrong order, you get the wrong answer.
Here's where an important concept called the order of operations comes in.
We will use the acronym PEDMAS to help us remember the order of operations. It stands for Parentheses, Exponents, Division, Multiplication, Addition, and Subtraction. It's important to note that in the cases of the latter four, you'll work from left to right.
3. Order of operations: solution
Now that we know the order to run our calculations, let's solve it!
We first start with the calculation inside of the parenthesis. In this case, one divided by two would give us zero point five.
Next, we'll work on the exponents, in this case, two, to the power of two, which would return four.
Next, we'll multiply from left to right; this would be twenty multiplied by two first. Then it would be zero point five multiplied by ten multiplied by four. Now we have the last two pieces we need to finish our calculation.
We can subtract twenty from forty and have our final answer, twenty.
4. Order of operations in Excel
Excel follows the PEDMAS approach to its order of operations. However, it contains several other layers when handling formula syntax, including reference operators, negation, percent, concatenation, and logical comparisons.
This course will primarily utilize the highlighted operations.
5. Functions in Excel
Functions are a key component of Excel. Functions are preset formulas that help perform mathematical, statistical, and logical operations. However, it doesn't stop there. A plethora of available functions can be found in the function library with various categories, including financial, logical, text, date & time, lookup & reference, math & trigonometry, and many more.
6. Overview of aggregate functions
If you're new to Excel or working with data in general, the term aggregate may be new to you.
An aggregate function is a mathematical calculation that summarizes a group of values into a single result.
You might know the most commonly used aggregate functions: sum, average, count, min, and max.
Most of these functions can be found in the Statistical section of the function library except SUM, which can be found under Math & Trig.
7. Aggregate functions
Let's put this to the test with a scenario. You're a professor at the University of DataCamp. You've recently had an exam for students and have a list of their individual test scores.
Having the individual records is useful, but what about when we need a summarized view of how students performed overall?
This is where aggregate functions come in handy, we'll walk through some examples with this dataset.
8. Aggregate functions: Average
We'll start with the average function, which we should start by defining. Average (also known as arithmetic mean) can express the central or typical value in a set of numbers.
It's calculated as the sum of all values divided by the number of records.
There are three ways to approach writing this formula.
The first option is to use cell references; in this case, we want to find the average of cells B2 to B11.
The second option is to use a whole column reference, in this case, any data in column B. However, the header is automatically excluded from the calculation.
Alternatively, we can reference the column if the data is in a formatted table. The column name here is test score, so our final output would be 74 point 2.
9. Aggregate functions: continued
Similar to our average function, we can use the same types of references for other aggregate functions, including min, max, count, and sum.
We can use the min function to get the minimum (or smallest) value in a column.
We can use the max function to get the maximum (or largest) value in a column.
If we want to determine how many rows of data exist in a dataset, we can use the count function, which gives us the number of records in a given column.
Finally, we'll look at the sum function, which can be useful for adding together values in a given column.
10. Let's practice!
Let's put your newfound knowledge into practice.