Built-in functions
1. Built-in functions
Hi! I'm James, and I'll be your host as we explore data analysis in spreadsheets!2. Google Sheets for data analysis
Spreadsheets allow us to perform many of the key tasks in the data analysis process, such as: cleaning and manipulating data, performing statistical analyses, and creating visualizations for reporting - all with very little or no code.3. The data analysis process
Before we dive into the rich functionality spreadsheets has to offer, let's discuss the data analysis process in a bit more detail. Data analysis is the process of extracting meaningful insights from data, and although each project will have its own goals, most follow the same framework. First, we formulate the problem to solve or question to answer, such as: are customers aged 21 to 30 more likely to churn than customers aged 41 to 50?4. The data analysis process
Next, we need to collect and store the necessary data, which could require the use of a database.5. The data analysis process
Once collected, we need to explore our data to familiarize ourselves with its contents. At this stage, we may find biases in the collected data or discover missing or erroneous entries. We'll cover methods of exploring data in Chapter 1.6. The data analysis process
Once we understand the data, it must be cleaned and prepared for analysis. If our question requires the ages of customers, but we only collected dates of birth, we must convert them at this stage. A well-cleaned and prepared dataset will make analyzing the data much easier. We'll cover techniques for cleaning and preparing data in Chapter 2.7. The data analysis process
Now onto the actual analysis! Here's where we solve the problem we formulated. The analysis usually involves performing calculations and often a statistical analysis. In Chapter 3, we'll perform calculations to extract insights from our data.8. The data analysis process
Finally, we present our results to key stakeholders, which often includes high-level findings from the analysis and supporting visualizations. In this course, we'll focus on the exploring, preparing, and analyzing stages.9. Built-in functions
One of the most powerful spreadsheets tools are built-in functions, which are pre-written calculations available to use in formulas. Built-in functions are like having a personal chef: rather than creating a meal from scratch by combining ingredients using appliances and utensils,10. Built-in functions
we can pass the ingredients to our personal chef, who will quickly whip up a tasty dish. Similarly, built-in functions can be used to perform calculations without the hassle of writing complex formulas from scratch.11. Built-in functions
These values are passed as arguments to the function, which are like the function's ingredients. The function will perform the calculation and return the result.12. Anatomy of a built-in function
To understand how to use built-in functions, let's look at the ROUND function, which rounds a value to a specified number of decimal places. We start our formula and type the function name, followed by parentheses, which house the arguments. ROUND takes two arguments: the value to round and the number of decimal places to round to. value is a required argument, which means that the function doesn't work unless we pass it something. places is an optional argument, which we can tell from the square brackets. Optional arguments don't have to be specified, as the function uses a default value, in this case, zero; but a different value can be specified. Returning to our personal chef, by default, their recipe may include adding onions, but we can specify when handing over the ingredients that onions should be left out.13. ROUND() examples
Let's use ROUND on some currency exchange rates.14. ROUND() examples
Passing ROUND the cell reference B3 followed by two, will round the value in B3 to two decimal places,15. ROUND() examples
which returns point-77.16. ROUND() examples
If we pass ROUND B4, and specify no second argument,17. ROUND() examples
the function uses the default value of zero, and rounds B4 to the nearest whole number, one.18. Let's practice!
Time for some practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.