1. Other functions in Excel
Now that we've become familiar with aggregate functions, we'll now look at understanding what other functions Excel has to offer.
2. Function categories
Out of the box, Excel has 12 different function categories with hundreds of built-in functions that we can use. These categories include text, date & time, maths & trig, statistical, financial, logical, lookup reference, engineering, cube, information, web, and compatibility.
We will not cover all the various categories in this course, so all categories on the left are inside scope and all categories on the right are outside the scope of this course.
In the following slides, we'll walk through some examples of different functions; it's important to note that these functions can be used on cell references, table references, and static values.
3. Text
You'll likely use many functions inside the Text category, especially when preparing data. It contains many functions to help clean, format, and work with your data.
A set of functions that can be very useful for cleaning your data is the LEFT and RIGHT functions.
With the LEFT function, we return the first characters in a string based on a specified number of characters. Similarly, the RIGHT function follows the same premise, except it returns the last characters in a string.
With each function you can see how this works in practice with a static value.
4. Text
Another set of functions that can also be utilized is UPPER and LOWER. The purpose of these functions it so convert data with the type text into uppercase or lowercase, respectively.
5. Date & Time
For many of us, we'll regularly be working with data that requires some date or time interval. Out of the box, Excel has many options that can be used to create, convert, and format dates and times.
One of the most important dates functions to know is DATE. This function returns a calendar date based on input that contains year, month, and day. Let's say that you have three columns, each containing year, month, and day information - we can use the date formula to combine and format these into a new calendar date.
6. Date & Time
However, let's say we have the opposite issue: we do not want to have the information in a calendar format but want to return the day or even the year of the date. We can use functions such as DAY and YEAR which will return the corresponding part of the date you have specified.
7. Maths & Trig
Next, we'll be looking at some Maths & Trigonometry functions. One of the common use cases for working with Excel is working with quantitative data. These functions can be handy for all types of data preparation, formatting, and summarizing.
First, we'll look at the ROUND function. This straightforward function returns a number rounded to the specified number of digits. The example above shows that if we use the round function on minus 1 point 475 to 2 digits, we will get minus 1 point 48. There are multiple variations of ROUND, including ROUNDUP and ROUNDDOWN.
Another function that might come in useful is ABS. This returns the absolute value of a number without its sign. For example, the absolute value of -2 would be 2.
We also have the SUM function that we've shown you in a previous example.
8. Statistical
Finally, we'll briefly look at statistical functions.
You've already seen these in this course, with the functions AVERAGE, COUNT, MIN, and MAX. There are far more functions available in this category but we'll explore that in future courses.
9. Let's practice!
Time to put your knowledge to the test!