Google Sheets Functions

What Is Google Sheets Functions?

A function is a predefined formula in Google Sheets that performs calculations on the given data in a sequence. Google Sheets has many functions, which make calculation easier at a whole new level. Each has its own features and syntax, serving a particular purpose. Each function has a specific order called syntax. Only if it is entered using proper syntax do we get accurate results.

For example, to add the values in the range A1:A6, select the range of cells you want to add. In our example, we’ll select A1:A6. Now, enter the following formula in cell A7 =SUM(A1:A6) as the syntax of the function SUM is =SUM(value1,value2), where the values can be direct values, cell references, or a range. You get the sum as a result.

Google-Sheets-Functions-Definition

Components of Google Sheets Functions

As mentioned before, Google Sheets functions have their own syntax. Regardless, every function has a basic template type, and the order in which you enter everything into a cell is important. Each function’s syntax must be followed for it to calculate properly. The basic syntax to create a formula using a function is as follows:

  1. Insert an equal sign (=) and a function name.

=AVERAGE

  • Now, apply the open braces.

=AVERAGE(

  • Enter the arguments and close the braces.

Arguments contain the information you want the formula to calculate, such as a range of cell references.

=AVERAGE(B2:B5)

  • Press Enter and get the result.
Components-of-Google-Sheets-Functions

List of 10 Most Important Google Sheets

Google Sheets has several functions that work together to perform calculations or manipulate data.

#1 – Financial Functions

Financial functions in Google Sheets perform various types of financial calculations, such as calculating the amount for loan payments, mortgage, and interest rates. Here is a commonly used function.

PMT

The PMT function calculates the periodic payment needed to repay a loan or investment. It assumes constant payments and a constant interest rate.

Syntax:

PMT(rate, nper, pv, [fv], [type])

  • rate: The interest rate per period.
  • nper: The number of payment periods.
  • pv: The present value or principal amount.
  • [fv] (optional): The future value or the desired balance after the last payment. Default = 0.
  • [type] (optional): When the payments are due.

Example:

For a loan of $1,000 with an annual interest rate of 6% to be repaid over 5 years, we write PMT as

=PMT(6%/12, 6*12, -1000)

Example-1

FV

The FV in Google Sheets stands for ‘Future Value.’ It calculates the future value of investments using compound interest.

Syntax:

The FV can be calculated using the following formula.

=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

Here,

  • rate: the fixed rate of interest over time
  • number_of_periods: the number of periodic payments to be made
  • payment_amount: the amount of money that you pay for each period.
    • It may be weekly (52), monthly (12), or quarterly (4) multiplied by the time period.
  • [present_value]: (optional value) the current value of the investment.
  • [end_or_beginning]: [optional,  0 by default] 0 indicates that you are making the payments at the end of each period, while 1 indicates payment made at the beginning.

Example:

Suppose you make a payment of $1,000 yearly for 5 years with an annual interest rate of 4%. To find the future value, enter the following formula:

=FV(B1, B2, B3). Here, payment is negative as it’s cash outflow.

Example-2

Other financial functions include IRR, PV, NPV, etc.

#2 – Logical Functions

Logical functions in Google Sheets are for logical operations and are used to make decisions based on conditions. Here is a commonly used function.

IF

The IF function checks whether a specific condition is met and returns a value if true and another value if false.

Syntax:

IF(logical_expression, value_if_true, value_if_false)

  • logical_expression: The condition to evaluate.
  • value_if_true: The value returned if the condition is true.
  • value_if_false: The value returned if the condition is false.

Example:

 To check if a temperature is hot or cold, we use the following formula.

=IF(A1 >= 35, “Hot”, “Cold”)

Example-3

Other financial functions include AND, OR, NOT, IFERROR, etc.

#3 – Text Functions

Text functions in Google Sheets manipulate and analyze text strings. Here is a commonly used function.

TEXT

The TEXT function changes a number into text. Besides numbers, you can also change dates, time, currency, and percentages.

Syntax

The general syntax of the TEXT function is as follows:

=TEXT(number, format)

•            Number: It is a value, such as a date, number, or time, whose format you want to change

•            Format: The formatting to which you want to change the above number.

Example:

We have entered 20500 in cell A1. To convert these numbers to dollars, enter the formula shown below in cell B1.

=TEXT(A1,”$ 00,000”)

Example-3

SEARCH

The SEARCH Function locates a substring within a string and returns the position of its first occurrence.

Syntax

The syntax of the SEARCH Excel function is as follows:

=SEARCH(find_text, text, [start_position])

  • find_text – It is the sub-string which is to be searched for
  • text – Main string where the search is performed
  • start_position  – This argument is optional and indicates the position from where you want to search within the text. Default – 1.

Example:

Let us find the position of “t” in the string below.

=SEARCH(“t”, A6)

Example-5

Other text functions include

CONCATENATE: Joins multiple text strings into one.

TEXTJOIN: Joins multiple text strings with a delimiter between each, etc.

#4 – Date and Time Functions

Google Sheets has multiple functions that work with dates and times. They allow you to perform calculations, format dates, and work with time values.  Here’s a common date and time function in Google Sheets:

TODAY

TODAY is used to return the current date.

Syntax:

=TODAY() – when you enter this in a cell, it returns the current date.

Example:

Example-6

Other functions include:

  • NOW – returns the current date and time.
  • DATE – creates a date from year, month, and day values.
  • DATEVALUE – converts a date in text format to a date value, etc.

#5 – Lookup & Reference Functions

Google Sheets provides a range of lookup and reference functions to find and manage data in your spreadsheet. Here’s the most common lookup function used.

VLOOKUP (Vertical Lookup)

This function searches for a value in the first column of a range and returns the required result from a specified column in the same row.

Syntax:

=VLOOKUP(search_key, range, index, [is_sorted])

  • search_key: The value to search for
  • range: The range of cells where you search.
  • index: The column index (starting from 1) to return the required value.
  • [is_sorted] (optional): TRUE if the range is sorted in ascending order; FALSE if not.

Example:

Here, we have entered the name “Mary,” which is searched in the first column in the specified range, A1:B4.

=VLOOKUP(“Mary”, A1:D4, 3, FALSE)

Example-7

Other LOOKUP and REFERENCE functions include HLOOKUP, XLOOKUP, MATCH, INDEX, etc.

#6 – Math Functions

Google Sheets has a range of mathematical functions for performing mathematical calculations. These are the most commonly used functions to manipulate data. Let us look at some important ones.

SUM

SUM is used to add together a range of numbers.

Syntax:

SUM(number1, [number2, …])

Example:

Let us add some numbers, as shown below.

=SUM(2,3,A1)

Example-8

For subtraction, Google Sheets does not have a dedicated function. Instead, use the subtraction operator -.

Example: 

=C1 - C2

Example-8-1

ROUND

The ROUND function rounds a number to a specified number of decimal places.

Syntax:

ROUND(number, [places])

  • number: Number to round.
  • [places] (optional): Number of decimal places to round to.

Example:

Let us try to round the PI() value to 3 decimal places.

=ROUND(PI(), 3)

Example-9

Other mathematical functions include ABS, SQRT, CEILING, FLOOR, ROUNDUP, etc.

#7 – Statistical Functions

Statistical functions are very important in Google Sheets and are used for financial calculations and analysis. Let us look at some of the important statistical functions.

AVERAGE

This function returns the arithmetic mean of selected values.

Syntax

Its syntax is as follows:

AVERAGE(value1, [value2, …])

You can type in multiple values or ranges.

Example:

=AVERAGE(G1:G6) returns the average of all values in the range.

Example-10

MEDIAN

It returns the value of the arithmetic mean of the middle terms of a set of sorted values.

Syntax:

=MEDIAN(value1, [value2, …])

  • value1: The first value used when calculating the median value
  • value2, … Optional additional values

Example:

For the above example of AVERAGE, let us find the median.

Example-11

Other functions include MODE, MIN, MAX, GEOMEAN, COUNT, etc.

How To Access Google Sheets Functions Using the Functions button?

There are different ways to enter a function in Google sheets. Let us look at the first method.

Step 1: First, go to the cell where you would like to enter the function. Let us assume we are looking for the PMT function, which is under ‘Financial.’

From there, go to the function button located on the right of the Google toolbar.

How-To-Access-Google-Sheets-Functions-Using-the-Functions-button-Step-1

Step 2: Click on it. You will see some of the most basic functions along with different categories like “Database,” “Google,” “Statistical, ” etc.

How-To-Access-Google-Sheets-Functions-Using-the-Functions-button-Step-2

Step 3: Since we need PMT, click on “Financial.”

How-To-Access-Google-Sheets-Functions-Using-the-Functions-button-Step-3

Step 4: Click on the arrow to view the list of functions. Choose PMT.

How-To-Access-Google-Sheets-Functions-Using-the-Functions-button-Step-4-1

Step 5: You can see how the function is entered with braces in your required cell.

How-To-Access-Google-Sheets-Functions-Using-the-Functions-button-Step-5

Step 6: Enter the required parameters and press Enter to get the result.

How-To-Access-Google-Sheets-Functions-Using-the-Functions-button-Step-6

How to enter a Google Sheets Function on the sheet manually?

Let us try to enter the same function manually.

How-to-enter-a-Google-Sheets-Function-on-the-sheet-manually

Step 1: Place the cursor in cell B5 to find the PMT value.

Step 2: Enter the formula as follows: =PMT, then open braces. Remember, every formula or function will be executed only when you enter an = and begin it.

How-to-enter-a-Google-Sheets-Function-on-the-sheet-manually-Step-2

Step 3: Enter the arguments in the correct sequence provided by the syntax.

How-to-enter-a-Google-Sheets-Function-on-the-sheet-manually-Step-3

Step 4: Press Enter. You get the PMT value. In this way you can use any function in Google sheets. Remember to enter the arguments in the right sequence and of the right type to avoid errors.

How-to-enter-a-Google-Sheets-Function-on-the-sheet-manually-Step-4

Important Things to Note

  1. When you enter a function just below a range in the same column or to the right of a row range, Google Sheets automatically suggests the range for you to fill in, which is a quick alternative to tedious typing.
  2. An exciting function among Google’s many built-in functions is GoogleTranslate, which automatically translates values into another language.
  3. Google Sheets functions integrate with other Google services using functions like GOOGLEFINANCE, IMPORTDATA, IMPORTXML, etc.

Frequently Asked Questions (FAQs)

1. What are some interesting features of Google Sheets functions?

Google Sheets functions have some very useful and unique features, some of which are covered here.
• The GOOGLETRANSLATE function translates text where you specify the target languages to translate text from one language to another.
• The ARRAYFORMULA function performs operations on a range of cells simultaneously and can be given as a single formula. It is very helpful for large datasets.
• IMPORTDATA is used to import data from a given URL into your spreadsheet.
• QUERY is used to run SQL-like queries on your data range.

2. What are the uses of Google Sheets functions?

Google Sheets has several built-in functions that allow you to create a formula to perform calculations on data. These functions help users handle a large volume of data with ease for calculations and manipulations.  They are also useful for professionals who handle a large amount of data. These formulas are simple to remember, perform the desired calculations, check for accurate data entry, and increase productivity when working on large datasets.

3. What are the different classifications of Google Sheets functions?

Google Sheets functions are classified into various groups depending on their function. These include Database, Date, Logical, Statistical, Math, Filter, Text, Lookup, and so on. Each group contains an exhaustive list of functions.

Download Template

This article must help understand Google Sheets Functions with its features and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Google Sheets Functions. We learn how to use the different categories of functions and their syntax along with examples. You can learn more from the following articles.

Subtract Time in Google Sheets

SinH in Google Sheets

Norm.Dist Google Sheets Function

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *