Google Sheets: Advanced Formulas and Functions

Go to class
Write Review

Free Online Course: Google Sheets: Advanced Formulas and Functions provided by LinkedIn Learning is a comprehensive online course, which lasts for 3-4 hours worth of material. The course is taught in English and is free of charge. Upon completion of the course, you can receive an e-certificate from LinkedIn Learning. Google Sheets: Advanced Formulas and Functions is taught by Curt Frye.

Overview
  • Learn how to summarize, analyze, and calculate data using the advanced formulas and functions available in Google Sheets.

Syllabus
  • Introduction

    • Welcome
    • Using the exercise files
    1. 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
    2. 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
    3. 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
    4. 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
    5. 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
    6. 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
    7. 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