Google Sheets: Advanced Formulas and Functions

Course details

Many beginning and intermediate Google Sheets users are familiar with basic functions and formulas, but have no experience with the more advanced calculations the program offers. In this course, Curt Frye walks through the intermediate and advanced functions for summarizing data, performing statistics, analyzing financial data, and more. Learn how to use references and named ranges; apply mathematical functions to multiply, round, and count values; identify outliers and rank values; calculate investments and loan payments; determine dates and times; look up values based on multiple criteria; and summarize arrays of data.

Learning objectives
Entering formulas
Creating, editing, and deleting named ranges
Using mathematical functions such as SUM and AVERAGE
Summarizing data
Analyzing financial data
Working with dates and times
Looking up values
Multiplying arrays

Concepts

0. Introduction

01 - Welcome
02 - Using the exercise files

1. Creating and Managing Formulas

03 - Enter and edit a formula
04 - Use relative and absolute references
05 - Create a named range for use in a formula
06 - Edit and delete a named range
07 - Show formulas in a worksheet
08 - Replace formulas with their results

2. Using Mathematical Functions

09 - Round values up or down
10 - Create random numbers
11 - Multiply a set of numbers
12 - Find the sum and average of numbers
13 - Count types of values
14 - Count values conditionally
15 - Calculate permutations and combinations
16 - Find integer and remainder components of numbers and operations
17 - Convert values from one measure to another

3. Summarizing Data Using Statistical Functions

18 - Find the nth largest or smallest value
19 - Identify the smallest and largest values
20 - Rank values in a range
21 - Calculate distribution statistics
22 - Calculate values in the normal distribution
23 - Represent data using a mean of zero and standard deviation of one

4. Analyzing Data Using Financial Functions

24 - Determine loan payments
25 - Determine the principal and interest components of loan payments
26 - Calculate cumulative principal and interest paid
27 - Calculate the present value of an investment
28 - Calculate the future value of an investment
29 - Calculate the effect of interest
30 - Calculate the incremental effect of inflation
31 - Calculate the net present value (NPV) of an investment
32 - Calculate the internal rate of return (IRR) of an investment

5. Working with Dates and Times in Formulas

33 - Find the parts of a date
34 - Find the parts of a time
35 - Calculate duration
36 - Calculate total number of workdays between two dates
37 - Calculate and end date given a number of working days
38 - Find the end date and end of month
39 - Enter the current date and time
40 - Discover the day of the week
41 - Find the week number for a given date

6. Performing Lookup and Linking Tasks Using Formulas

42 - Count columns and rows in a range
43 - Select a value given certain inputs
44 - Look up a value in a range
45 - Perform advanced lookup operations
46 - Look up values based on two criteria
47 - Create a hyperlink in a cell
48 - Refer to a cell a specified distance from another cell

7. Summarizing Arrays of Data

49 - Multiply two arrays
50 - Calculate sum of squares and other measures
51 - Forecast values using linear regression
52 - Find the frequency of occurrences within an array
53 - Transpose an array

Conclusion

54 - Additional resources