1. Aggregate and arithmetic operations
We've already covered how to use basic arithmetic. In this video, we'll learn about aggregate functions to take our analysis a step further.
2. 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.
3. 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.
4. 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.
5. 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.
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.
6. 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.
7. Let's practice!
Let's put your newfound knowledge into practice.