Google Sheets: Advanced Formulas and Functions

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

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