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

Introduction

Welcome
Using the exercise files

Creating and Managing Formulas

Enter and edit a formula
Use relative and absolute references
Create a named range for use in a formula
Edit and delete a named range
Show formulas in a worksheet
Replace formulas with their results

Using Mathematical Functions

Round values up or down
Create random numbers
Multiply a set of numbers
Find the sum and average of numbers
Count types of values
Count values conditionally
Calculate permutations and combinations
Find integer and remainder components of numbers and operations
Convert values from one measure to another

Summarizing Data Using Statistical Functions

Find the nth largest or smallest value
Identify the smallest and largest values
Rank values in a range
Calculate distribution statistics
Calculate values in the normal distribution
Represent data using a mean of zero and standard deviation of one

Analyzing Data Using Financial Functions

Determine loan payments
Determine the principal and interest components of loan payments
Calculate cumulative principal and interest paid
Calculate the present value of an investment
Calculate the future value of an investment
Calculate the effect of interest
Calculate the incremental effect of inflation
Calculate the net present value (NPV) of an investment
Calculate the internal rate of return (IRR) of an investment

Working with Dates and Times in Formulas

Find the parts of a date
Find the parts of a time
Calculate duration
Calculate total number of workdays between two dates
Calculate and end date given a number of working days
Find the end date and end of month
Enter the current date and time
Discover the day of the week
Find the week number for a given date

Performing Lookup and Linking Tasks Using Formulas

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

Summarizing Arrays of Data

Multiply two arrays
Calculate sum of squares and other measures
Forecast values using linear regression
Find the frequency of occurrences within an array
Transpose an array

Conclusion

Additional resources