How to use the and and or functions in google sheets

Simplify complex calculations with a few simple steps

  • Pima College
  • Tweet
  • Share
  • Email
  • Tweet
  • Share
  • Email

In This Article

Jump to a Section

Google Sheets is a powerful spreadsheet program that performs complex computations on the data you enter into each cell. The application uses formulas and functions to do these tasks, so you don’t have to. A formula is an expression that you input to tell Google Sheets how to calculate the value of a cell. A function is a predefined formula that Google Sheets has created for you.

The instructions in this article apply to Google Sheets.

Why Use a Function?

The difference between a formula and a function is that you create formulas to perform a computation, and functions are pre-built formulas found in Google Sheets. Functions save time and reduce the chance of errors.

For example, to add a row of numbers using a formula, enter the following formula into a cell in Google Sheets:

Enter the following formula to add the same row of numbers using a function:

=SUM(A1:F1)

Using a function is efficient when working with a large number of items or for more complex computations.

Google Sheets Function Syntax

Each function has a syntax, which is the specific order in which the elements needed for the function to perform the desired calculation are entered.

Every function begins with the function name, followed by the arguments, which are separated by commas or a colon and enclosed in parentheses. The basic construction of a function is:

Function_Name(argument1,argument2)

Here’s an example:

SUM(A1,B1)

How to Use Google Sheets Functions

The fastest and easiest way to use a function is from the Functions menu.

Select the cell where you want to display the result of the calculation.

On the toolbar, select Functions, then choose a function. There are five basic functions, plus submenus that contain every possible function. The five basic functions are:

  • SUM: Adds the values in a range of cells
  • AVERAGE: Calculates the average of the values in a range of cells.
  • COUNT: Provides the number of values in a range of cells.
  • MAX: Provides the highest value in a range of cells.
  • MIN: Provides the lowest value in a range of cells.

Choose the cells to include in the range.

To choose individual cells, rather than consecutive cells, press and hold Ctrl and make your selections. To choose a continuous range of cells, press and hold Shift, then select the first and last cells in the range.

Press Enter.

The result appears in the selected cell.

How to Use Complex Functions in Google Sheets

Google Sheets includes dozens of functions that perform a wide variety of tasks. For example, to calculate the number of days or the number of working days (Monday through Friday) between two dates.

To find the right function, reference the complete list of Google Sheets functions. To narrow down the options, enter a search term in the Filter field and press Enter to see your choices. For example, to find the function to calculate the number of days, enter days as the search term. Two possible results are the DAYS and NETWORKDAYS functions.

Alternatively, go to the Google Sheets toolbar, select Functions, then choose a submenu at the bottom of the list.

Some functions require data to be input in a particular way. Here’s how to do it, using the NETWORKDAYS function as an example.

Select the cell where you want to show the number of working days between two dates.

Enter =NETWORKDAYS.

To use this function, you can start with a blank spreadsheet.

Two options are displayed: NETWORKDAYS and NETWORKDAYS.INTL. Select NETWORKDAYS.

The correct format used to enter the function is displayed. Review it, then select X to exit.

Enter the start and end dates of the date range using the same format as the formula. Pay close attention to punctuation placement.

Press Enter.

The number of workdays appears in the selected cell.

How to Use Functions With Text in Google Sheets

Google Sheets functions can be helpful with text as well. For example, the GOOGLETRANSLATE function translates selected text from a source language to another specified language.

Here’s how to do it, using the Spanish word hola as an example:

search menu

How to use the and and or functions in google sheets

Lesson 15: Working with Functions

Introduction

A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value, and minimum value for a range of cells. In order to use functions correctly, you'll need to understand the different parts of a function and how to create arguments to calculate values and cell references.

Watch the video below to learn how to create functions.

The parts of a function

Similar to entering a formula, the order in which you enter a function into a cell is important. Each function has a specific order—called syntax—that must be followed in order for the function to calculate properly. The basic syntax to create a formula with a function is to insert an equals sign (=), a function name (AVERAGE, for example, is the function name for finding an average), and an argument. Arguments contain the information you want the formula to calculate, such as a range of cell references.

How to use the and and or functions in google sheets

Working with arguments

Arguments can refer to both individual cells and cell ranges and must be enclosed within parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function.

For example, the function =AVERAGE(B1:B9) would calculate the average of the values in the cell range B1:B9. This function contains only one argument.

How to use the and and or functions in google sheets

Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3, C1:C2, E1) will add the values of all of the cells in the three arguments.

How to use the and and or functions in google sheets

Creating a function

Google Sheets has a variety of functions available. Here are some of the most common functions you'll use:

  • SUM: This function adds all of the values of the cells in the argument.
  • AVERAGE: This function determines the average of the values included in the argument. It calculates the sum of the cells and then divides that value by the number of cells in the argument.
  • COUNT: This function counts the number of cells with numerical data in the argument. This function is useful for quickly counting items in a cell range.
  • MAX: This function determines the highestcell value included in the argument.
  • MIN: This function determines the lowest cell value included in the argument.

To create a function using the Functions button:

The Functions button allows you to automatically return the results for a range of cells. The answer will display in the cell below the range.

How to use the and and or functions in google sheets

    Select the range of cells you want to include in the argument. In our example, we'll select D3:D12.

How to use the and and or functions in google sheets

How to use the and and or functions in google sheets

To create a function manually:

If you already know the function name, you can easily type it yourself. In the example below, which is a tally of cookie sales, we'll use the AVERAGE function to calculate the average number of units sold by each troop.

    Select the cell where the answer will appear. In our example, we'll select C10.

How to use the and and or functions in google sheets

How to use the and and or functions in google sheets

How to use the and and or functions in google sheets

How to use the and and or functions in google sheets

Google Sheets will not always tell you if your function contains an error, so it's up to you to check all of your functions. To learn how to do this, read our article on why you should Double-Check Your Formulas.

Google Sheets function list

If you have experience using spreadsheets and want to use Google Sheets to make more advanced calculations, you can explore the Google Sheets function list. It is a handy reference for hundreds of financial, statistical, and data analysis functions.

How to use the and and or functions in google sheets

If you are familiar with functions found in Microsoft Excel's Function Library, you will find that the Google Sheets function list has many of the same functions.

To access the function list:

Click the Functions button and select More functions. from the drop-down menu. The Google sheets function list will appear in a new browser tab.

If you're comfortable with basic functions, you may want to try a more advanced one like VLOOKUP. You can check out our article on How to Use Excel's VLOOKUP Function for more information. Like most functions, VLOOKUP works the same way in Excel and Google Sheets.

OR function is useful when you want to evaluate a set of conditions.

It will return TRUE when any of the conditions being checked are met, else it returns FALSE.

This Article Covers:

OR Function Syntax

OR ( logical_expression1 , [logical_expression2, …] )

  • logical_expression1 – The first condition that you want to check. This could be a cell reference that has the TRUE/FALSE value or an expression that returns logical values.
  • [logical_expression2].. – Additional conditions that you want to check.

Additional Notes:

  • OR function can be used with other formulas to be more efficient. For example, if you’re testing two conditions and your final result is ‘Pass’ if any of the two conditions is met. You can use a formula such as =IF(OR(A1=”Pass”,A2=”Pass”),”Pass”,”Fail”).
  • The arguments must either evaluate to logical values (TRUE/FALSE), or the arguments must be arrays/references of logical values.
  • Text and empty cells are ignored.
  • If the specified range contains no logical values, the OR function returns #VALUE! error.

OR Function in Google Sheets – Examples

Now let’s look at some examples where you can use the OR function in Google Sheets.

Example 1 – Test Multiple Conditions Using OR Function

Suppose you have a dataset as shown below and you want to find it any of the tests passed. If any of the tests pass, the formula should return PASS, else it should return FAIL.

The below formula can do this:

Note that this is an array formula as we are testing multiple cells at one go. You don’t need to write this formula this way. You can simply write the formula = OR ( B2:B4 = “Pass” ) and then use Control + Shift + Enter instead of just using Enter.

In the above formula, OR function evaluates the cells and returns TRUE if any of the tests is a ‘pass’, else it returns a false. If function then uses this result to return “Pass” or “Fail”.

Example 2 – Check Whether a Date is a Weekend Day Or Not

Suppose you have a dataset as shown below:

How to use the and and or functions in google sheets

You can use the OR function along with the WEEKDAY function to check if the day is a weekend or not.

Below formula would return TRUE if it’s a weekend day, else it will return FALSE.

In this formula, the weekday function returns 6 for a ‘Saturday’ and 7 for a ‘Sunday’. OR function then returns TRUE when the result of WEEKDAY function is 6 or 7.

Example 3 – Using Nested OR function

Suppose you have a dataset as shown below:

How to use the and and or functions in google sheets

In the above case, access is granted only if you are from either Marketing or Sales department or you have the A grade classification.

The below formula will give TRUE if access is to be granted and FALSE if the conditions are not met and access is not to be granted.

How to use the and and or functions in google sheets

Note that this is a case of nested OR functions where one OR function is used with in the other.

How to use the and and or functions in google sheets

There are hundreds of functions available for you to perform just about any kind of calculation. Functions are sorted into categories that you can browse.

Insert a Function

How to use the and and or functions in google sheets

Most functions require some kind of input or data to calculate, called arguments.

How to use the and and or functions in google sheets

The function is applied to the selected cell.

Get Help on Functions

If you’re not sure what category a certain function is found in, you can consult the Google Sheets function list page to find out.

How to use the and and or functions in google sheets

A new browser tab opens to the function list. You can scroll through this list to see all of the available functions, their syntax, and a description of what they do.

How to use the and and or functions in google sheets

Sheets displays information about the topic you entered.

How to use the and and or functions in google sheets

FREE Quick Reference

Free to distribute with our compliments; we hope you will consider our paid training.

You can use the following basic syntax to use the IF and OR functions together in Google Sheets to determine if some cell meets one of multiple criteria:

If the value in cell A2 is equal to “String” or if the value in cell B2 is greater than 10, then we return value1, otherwise we return value2.

Note that we can use as many logical comparisons as we’d like within the OR function.

The following examples show how to use this syntax in practice.

Example 1: Combine IF and OR Functions with String Comparisons

Suppose we have a column that contains the names of NBA teams and we’d like to determine if each team is based in Texas:

How to use the and and or functions in google sheets

Note that the only teams that are based in Texas are the Mavs, Rockets, and Spurs.

We can use the following formula with the IF and OR functions to determine if each team is based in Texas:

The following screenshot shows how to use this syntax in practice:

How to use the and and or functions in google sheets

If a given team is from Texas, we return a value of “Yes”, otherwise we return “No.”

Example 2: Combine IF and OR Functions with Numeric Comparisons

Suppose we have columns that contain the number of points and assists for various basketball players and we’d like to classify each player as “Good” or “Bad.”

How to use the and and or functions in google sheets

Let’s say that if a player has more than 20 points or more than 10 assists, we will classify them as “Good”, otherwise we’ll classify them as “Bad.”

We can use the following formula with the IF and OR functions to determine if each player should be classified as “Good” or Bad”:

The following screenshot shows how to use this syntax in practice:

How to use the and and or functions in google sheets

If a given player has more than 20 points or more than 10 assists, we classify them as “Good.”

Otherwise we classify them as “Bad.”

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

How to use the and and or functions in google sheets

The OR formula in Google Sheets accepts logical expressions as input arguments, evaluates them to either a logical TRUE or FALSE. If all of the input arguments evaluate to FALSE, then the formula returns FALSE as the end result. However, if any of the input arguments evaluate to TRUE, the formula returns TRUE as an output.

Syntax

OR(logical_expression1, [logical_expression2, …])

logical_expression1 – is an expression that evaluates to a logical TRUE or FALSE. This can be a direct expression or a reference to the cell that represents a logical expression.

[logical_expression2, …] – these are optional and additional expressions that return a logical TRUE or FALSE.

Usage: OR Formula in Google Sheets

Let us try various combinations of input arguments within the OR formula and examine the results. You may have already guessed that the first two variations of the formula with a single input argument, aren’t that useful to us.

How to use the and and or functions in google sheets

It is interesting to note that the OR formula works even when we provide numbers as the input arguments . What happens when the Google Sheets application encounters a number when it is expecting a logical TRUE or FALSE? It simply converts them to a logical TRUE or FALSE. A zero is converted to FALSE and a non-zero to TRUE. Doesn’t really matter if the numbers contain decimals. Please see the examples in the snapshot below.

How to use the and and or functions in google sheets

The OR formula also takes a range of cells as an input argument , as illustrated in the image below.

How to use the and and or functions in google sheets

While it is hard to deny the utility of the OR formula in the real world, probably it has a limited application as a standalone formula. However, when it is used in conjunction with other formulas like IF , we can see its magic coming to life. In the snapshot below, consider the first and third formulas. The second and fourth formulas are their alternatives. Notice how simple it gets when we use OR instead of multi-level IF formula nesting.

How to use the and and or functions in google sheets

OR formula

And there you go! Use the OR formula in Google Sheets to evaluate logical expressions.

If you’d like to learn more about the various formulas of Google Sheets, why not take a look at our blog post on the AND formula in Google Sheets.

You can do many cool things with custom functions in Google Sheets. Here’s how to use Google scripts to create a function.

Google Sheets has some useful features to handle numerical calculations, look-ups, and string manipulation. If your sheets are more advanced, you might find yourself needing to build complex formulas to get the job done.

If you need to go beyond the scope of what Google Sheets has built-in (like sorting columns in Google Sheets), creating a custom function is the solution. Custom functions are pieces of code that perform actions on your sheet. Once you write them you can give them a name and call them again and again, saving you time.

Let’s look at how to make a custom function in Google Sheets, using Google scripts.

Google Sheets Functions

Google Sheets has pretty powerful functions already built-in. An example of built-in functions you may have already used would be Sum or Average:

What if you wanted to perform a calculation that isn’t included in standard functions? Consider a scenario where you want to add sales tax to the price of an item. Since tax rates vary by location, you would need to build a function with a long list of nested logic. It would look something like this:

Now imagine if you had to add a dozen or more conditions to this statement for each state. It would get out of control!

A Google Sheets custom function can handle this task. You can put all the complicated code into a script, give it a name, and call the function. No bulky code in your Google Sheet, just a simple function like Sum.

Learning how to create custom functions opens up a brand new world of possibilities. So let’s begin.

Create a Google Sheets Custom Function

If you are new to scripting, fear not! It’s easy to use. This example will get you started and before long you’ll be writing your own scripts.

Custom functions for Google Sheets are written with JavaScript code. If you’re an expert in JavaScript you’ll feel right at home. If not, it’s a simple language that you can learn with a JavaScript cheat sheet.

Open the Script Editor

Open your Google Sheet and select Tools > Script Editor

Create Your Function

You will want to give your function a useful name. Something simple yet very clear indicating what the function will do.

The inputs you want to use go inside the parentheses as variables. This will be the cell value that you want to work with. If you have more than one cell value you can separate them with a comma.

To use this tax example, you can copy and paste this code into the script editor:

This is a function called tax that will calculate the tax rate on a price based on the location you input in the function. These are hypothetical tax percentages.

The script will take two cells. One assigned to input the other to location. It will run code to determine which state you would like to calculate for and return the tax amount.

I’ve only included two locations in this example to give you the idea. You can add more by adding additional lines with locations that you need. That would be good practice to add on once you’re finished.

Save Your Function

Select File > Save, give your project a name and click OK.

Use Your Custom Function

Once you create your function you can use it the same way you would use a built-in function. In the cell where you want your calculation to display, enter an equal sign followed by the name of your function.

For our tax example we are using two inputs. The location which will determine the tax rate and the price of the product that needs tax applied to it:

=tax(B2, A2) where B2 is the price of the product, and A2 is the tax location.

You can use AutoFill just like Excel to drag and drop your function to all your rows, just as you would a built-in function:

After you’ve created your first custom function, you might have several more that you’d like to add. It’s easy to add more code to your script. Follow these steps to create a new function the same way and add them underneath your existing code.

Here’s the result of the new script:

Reuse Your Functions

Once you put in the effort to create a custom function you can reuse it later. If you create a script to solve a common problem you can get some pretty significant time savings.

Even if you don’t need them all in future sheets you should know how to save them just in case you run into a similar problem down the road.

There are a couple of ways to reuse your functions:

  1. Save your functions in a blank sheet and use it as a template by using a copy of it for all future sheets.
  2. Copy your functions from one sheet to the next. This is tedious, but it will work. Open the script editor and copy all the code from one sheet, open the script editor in another sheet, and paste the code there.
  3. Save your sheet to the Google template gallery. Keep in mind this will make your document accessible by others. You will be able to limit this to members of your domain if you have a Google Apps for Work subscription. If you haven’t used the template gallery before, it’s worth checking out. There are a number of useful Google templates out there to make your life easier.

Document Your Google Script

Google Script supports the JSDoc format, which allows you to add comments to your formula to provide some helpful context.

You’ve seen these comments in standard functions. When you hover over a function as you write it, it tells you a little bit about what each piece does.

This isn’t required but it’s recommended.

You can do so many cool things with custom functions in Google Sheets. In fact, creating custom functions is one of the ways to use Google Scripts to make Google Sheets more powerful.

If you want to go down the road learning more about Google Sheets you should check out ways to find great Google Sheets templates. If you want to dig deeper into scripting with Google Sheets you’re going to want to master JavaScript. Learn what JavaScript is and the basics of declaring variables in JavaScript.

How to use the and and or functions in google sheets

It’s easy to add a set of numbers together — every spreadsheet user knows how to use the tried-and-true SUM function to find a total. But what if the sum you’re trying to find depends on some sort of condition? Suppose, for example, you have a set of numbers and you only want to add up the ones that are below a certain max value. Or perhaps you have your company’s sales tally and want to know only the sales from a particular region or sales from a certain time period.

That’s where Google Sheets’ SUMIF function comes in. You can use SUMIF to calculate a sum based on a condition. That condition can be built into the set of values themselves, or numbers that are related to a neighboring row or column. If that sounds complicated, the good news is that it’s easy to apply.

How to use the SUMIF function in Google Sheets

As the name of the function implies, SUMIF is conditional and checks for a status using the IF function before totaling your numbers. This is what the function looks like:

=SUMIF(range, criterion, [sum_range])

  • Range: The range is the set of cells that you want to test against some sort of criterion.
  • Criterion: This is what you want to use to test against the range. The SUMIF function is pretty versatile — you can use a number, text, or even a date as the criterion.
  • Sum_range: The sum_range is optional, and is what gives this function so much power. If you omit the sum_range, the function will sum the range. But you have the option of summing a different range depending on the result of the conditional test.

How to use SUMIF with a simple number condition

Even with the optional sum_range argument, the SUMIF function isn’t difficult to use, but the easiest way to see it in action is by adding a range of values based on a criterion in that range. For example, suppose you have a spreadsheet like this one, and you are interested in the sum of all the numbers that are 100 or higher.

  1. Type “=SUMIF” and press the Tab key. Google Sheet will automatically add the open parenthesis and wait for the range.
  2. Click and drag the mouse to select the column with the numbers you want to sum.
  3. Type a comma and then enter “>=100” (including the quotes).
  4. Press the Tab key. Google Sheets will add the closing parenthesis and you should see the result in the cell. We didn’t need to specify a sum_range because in this example it’s just the same as the range.

How to use SUMIF with a text condition

  1. Type “=SUMIF” and press the Tab key. Google Sheet will automatically add the open parenthesis.
  2. Click and drag the mouse to select the column with the names of the regions.
  3. Type a comma and then enter “East” (including the quotes).
  4. Type a comma and then select the column with the sales figures.
  5. Press the Tab key. Google Sheets will add the closing parenthesis and you should see the result in the cell.

How to use SUMIF with a date condition

  1. Type “=SUMIF” and press the Tab key. Google Sheet will automatically add the open parenthesis.
  2. Click and drag the mouse to select the column with the range of dates.
  3. Type a comma and then enter “DATE(2021, 1,15)” (do not include the quotation marks).
  4. Type a comma and then select the column with the sales figures.
  5. Press the Tab key. Google Sheets will add the closing parenthesis and you should see the result in the cell.

In this example, we added together all the numbers that occurred on January 15, 2021, but you can also sum the numbers that happened before that date or after that date.

To sum everything on or before that date, enter the criterion like this: “<="&DATE(2021, 1,15). In this case, the less than and equals to symbols are "concatenated" to the date with the ampersand (&) symbol, and the less than and equals to symbols are in quotes.

Likewise, to sum everything after that date, enter the criterion like this: “>”&DATE(2021, 1,15).

How to use SUMIF with a wildcard

  1. Type “=SUMIF” and press the Tab key. Google Sheet will automatically add the open parenthesis.
  2. Click and drag the mouse to select the column with the names of the products.
  3. Type a comma and then enter “*apple*” (include the quotation marks).
  4. Type a comma and then select the column with the sales figures.
  5. Press the Tab key. Google Sheets will add the closing parenthesis and you should see the result in the cell.

In this example, we are looking for the word “apple” to appear anywhere in the cell, so we put an asterisk both before and after the word — this way it included apple at the end of the cell, like “Green Apples,” and apple at the start of the cell, like “Apple Butter.” If you prefer, you could write just “*apple” to only include cells in which apple appears at the end of the cell.

Tips for using SUMIF in Google Sheets

Once you’ve used the SUMIF function a few times, you’ll probably find that it’s pretty straightforward, both with and without the optional argument. But here are a few tips to keep in mind to get the most out of SUMIF:

How to use the and and or functions in google sheets

Dates are internally stored as sequential serial numbers in Google Sheets. This serial number represents the number of days elapsed since December 31, 1899.

You can use the DATEVALUE function to convert any date input to a number that represents the date. For instance, both the functions DATEVALUE(“Jan 1”) and DATEVALUE(“01-Jan-2021”) return the same number (44197) though the inputs have vastly different formats.

The function TODAY() returns the current date while the function NOW() returns the current date and time. Both these functions do not require any arguments and they update when any cell in the Google Sheet is changed.

The function NOW() + 2 returns the current date and time plus two days while NOW() – 9/24 returns the date and time 9 hours ago since 1 = 24 hours.

The functions YEAR() , MONTH() and DAY() can be used extract the year, month and day of the date that is passed as an argument.

The DAYS() function calculates the number of days between two dates. Internally, it calculates the DATEVALUE of the first date and the DATEVALUE of the second date and subtracts the two numbers.

If you want to calculate the number of months between two dates, you can use the DATEDIF() function with the third argument set to M . For instance, the function =DATEDIF(“Jan 1, 1951”, TODAY(), “M”) returns the number of months between January 1951 and today.

The YEARFRAC() function calculates the number of years that has passed between two dates.

Tip: You may use these date functions in Google Sheets with Array Formulas to schedule emails with Gmail Mail Merge.

Use the EDATE() function to calculate a date that is a specified number of months before or after a specified date. For instance, EDATE(TODAY(), -1) returns the date that is one month before the current date.

The EOMONTH() function helps you calculate the last day of the given month. For instance, EOMONTH(TODAY(), -1) returns the last day of the previous month. Add 1 to the result, =EOMONTH(TODAY(),-1)+1 , and you’ll get the first day of the current month.

The WEEKDAY() function returns the day of the week corresponding to a date with Sunday representing 1, the first day of the week. Set the second argument to 2 and days of the week will be numbered starting with Monday.

The WORKDAY() function calculates the date that is a specified number of days before or after a specified date, excluding weekends. For instance, WORKDAY(TODAY(), -7) returns the date that is 7 working days before the current date.

Likewise, the NETWORKDAYS() function calculates the number of working days between two dates provided as arguments. Combine this with EOMONTH to calculate the number of working days that are left till the end of the the current month =NETWORKDAYS(TODAY(), EOMONTH(TODAY(),0))

Google Sheets Date Formulas for Common Scenarios

Task Working Formula
Add number of days to a date =A1 + 5
Get a day that is 6 months prior to a date =EDATE(A1, -5)
Add number of years to a date =DATE(YEAR(A1) + 5, MONTH(A1), DAY(A1))
Difference in days between two dates =DAYS(A1, A2)
Total working days between two dates =NETWORKDAYS(A1, A2)
Get a date that is 10 working days from now =WORKDAY(TODAY(), 10)
Get the total number of months between two dates =DATEIF(A1, A2, “M”)
Get the difference in years between two dates =DATEIF(A1, A2, “Y”)
Get the number of days in the current month =EOMONTH(TODAY(), 0) – (EOMONTH(TODAY(), -1) + 1)
Print the day of the week =TEXT(TODAY(), “ddddd”)
Calculate the age in years =ROUNDDOWN(YEARFRAC(A1, TODAY(), 1))
Days until your next birthday =DAYS(DATE(YEAR(A1)+DATEDIF(A1,TODAY(),”Y”)+1, MONTH(A1),DAY(A1), TODAY())
Months and days between two dates =DATEDIF(A1,A2,”YM”)&” months, “&DATEDIF(A1,A2,”MD”)&” days”

You can copy this Google Sheet to get all the working formulas mentioned in this tutorial.

How to use the and and or functions in google sheets

This post is inspired by Patrick McKenzie’s reminder that sometimes you don’t need a database:

So if you’re building out a quick CRUD app for e.g. internal use, Google Docs as a backend (consumed via JSON) is *surprisingly* powerful.

— Patrick McKenzie (@patio11) July 5, 2014

In this tutorial, we’ll use Anton Burnashev’s excellent gspread Python package to read, write, and delete data from a Google Spreadsheet with just a few lines of code.

Google Drive API and Service Accounts

At the risk of being Captain Obvious, you’re going to need a spreadsheet if you want to follow along with this post. If you don’t have one on hand that’s full of juicy data, might I suggest you make a copy of this spreadsheet with contact information for all United States legislators ? (Side note: Ian Webster uses this data in conjunction with Twilio to make it easy for citizens to call congress ).

How to use the and and or functions in google sheets

To programmatically access your spreadsheet, you’ll need to create a service account and OAuth2 credentials from the Google API Console . If you’ve been traumatized by OAuth2 development before, don’t worry; service accounts are way easier to use.

Follow along with the steps and GIF below. You’ll be in and out of the console in 60 seconds (much like Nic Cage in your favorite Nic Cage movie).

  1. Go to the Google APIs Console.
  2. Create a new project.
  3. Click Enable API. Search for and enable the Google Drive API.
  4. Create credentials for a Web Server to access Application Data.
  5. Name the service account and grant it a Project Role of Editor.
  6. Download the JSON file.
  7. Copy the JSON file to your code directory and rename it to client_secret . json

There is one last required step to authorize your app, and it’s easy to miss!

Find the client_email inside client_secret . json . Back in your spreadsheet, click the Share button in the top right, and paste the client email into the People field to give it edit rights. Hit Send.

If you skip this step, you’ll get a gspread . exceptions . SpreadsheetNotFound error when you try to access the spreadsheet from Python.

We’re done with the boring part! Now onto the code.

Read Data from a Spreadsheet with Python

With credentials in place (you did copy them to your code directory, right?) accessing a Google Spreadsheet in Python requires just two packages:

How to use the and and or functions in google sheets

How to Use PRODUCT Function in Google Sheets

The PRODUCT function in Google Sheets is used to return the result of multiplying a series of numbers together.

Table of Contents

The PRODUCT function is a simple function that acts as a multiplication function in Google Sheets.

While you can use the usual asterisk or * sign, the PRODUCT function has other practical uses.

Let’s take an example.

As a catering service in our want to be able to find out how many meals you should prepare for school event happening in your city.

You need to know the total number of schools, classes per school, students per class, and meals per student you should prepare.

How should we go about that?

Fortunately, the PRODUCT function just needs an input value, which could be as many inputs as you like.

We will discuss the real examples and actual values to see how to use the PRODUCT function in Google Sheets.

The Anatomy of the PRODUCT Function

The syntax (the way we write) the PRODUCT function is simple:

Let’s break this down to understand the syntax of the PRODUCT function and each term:

  • = the equal sign is how we begin any function in Google Sheets.
  • PRODUCT is our function. We need to add the factor attribute for it to work properly.
  • factor1, [factor2. ] is the only attribute of this function. It is the cells that act as factors in our calculation.

The advantage that PRODUCT has over using the * or asterisk is that you can multiply over a range or a table in a quick and efficient manner, instead of inputting the values or cell addresses that you need to multiply one by one.

The advantage that PRODUCT has over using the MULTIPLY function is that MULTIPLY only works with two factors. In PRODUCT , it is listed that you can have up to 30 factors, but if you indeed need more, PRODUCT supports an arbitrary number of arguments.

Each factor can be a specified number or a range. If there are blanks within the specified range, they are ignored and won’t affect the end product.

A Real Example of Using PRODUCT Function

Let’s look at this example below to see how to use the PRODUCT function in Google Sheets.

Finding the Product of More Than 2 Factors in Google Sheets

In this simple problem, we want to find out how many meals we need to make as caterers for some school parties.

How to use the and and or functions in google sheets

The function with a cell reference is:

As a result, we will get 600.

This simple problem can be practiced. Use the link below to use our spreadsheet sample:

How to Use PRODUCT Function in Google Sheets

In this section, we will show you a step-by-step process on how to use the PRODUCT function in Google Sheets.

Finding the Product of More Than 2 Factors in Google Sheets

  1. To begin, click on the cell where you want to show your result to make it the active cell. In this guide, we will choose C9, where I want to see the result of our calculation.

How to use the and and or functions in google sheets

  1. Next, type the equal sign ‘=’ to start off the function. After that, followed by the name of the function which is ‘product’ (or PRODUCT, which also works).

How to use the and and or functions in google sheets

  1. You can now see the auto-suggest box will pop-up with the name of the functions that start with PRODUCT . Select the PRODUCT function by clicking on it. Make sure to choose the correct function.

How to use the and and or functions in google sheets

  1. After the opening bracket ‘(‘, you will have to add the factor1 attribute. Note that you can put in a range, or choose the cell addresses you want to add. Note that you also get a preview of the answer you need as you add factors or add to the range of numbers.

How to use the and and or functions in google sheets

  • You could also add the factors you need one-by-one if the numbers you need to multiple are not arranged in a column/row range.

How to use the and and or functions in google sheets

  1. Close the function with the closing bracket.

How to use the and and or functions in google sheets

  1. Finally, hit the Enter key to close the whole formula and it will output the result. You should now have the proper number of your desired answer.

How to use the and and or functions in google sheets

That’s it, you’re done! You can now use the PRODUCT function together with other Google Sheets formulas and create even more powerful and amazing formulas.

How to use the and and or functions in google sheets

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you’ll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

The CONCATENATE function in Google Sheets joins together multiple chunks of data. This function is helpful when managing large sets of information that each need a similar treatment.

For example, you might use the CONCATENATE function if the spreadsheet has one column for a first name and another for a last name, but you want them joined together to form a single cell with both names. You could this manually by typing each name, or you can use CONCATENATE to automate it.

How to use the and and or functions in google sheets

Many other examples of the CONCATENATE function could be given, so we’ll look at a few below.

A Simple Example

At its simplest form, the CONCATENATE function pieces together two sets of data without any other options. That’s possible with this simple form:

How to use the and and or functions in google sheets

Of course, in this example, we’re assuming the first name is in cell A1 and the second in cell B1. You can adapt this to your own spreadsheet by replacing those references with your own.

Pressing Enter with this particular example would produce MaryTruman. As you can see, the first name is butted right up against the last name. The CONCATENATE function did its job in this scenario, but there are other options you can include in it to expand its capabilities, like to add a space or data from other cells.

Using a Space in the CONCATENATE Formula

Knowing how to use spaces with CONCATENATE is important because datasets often aren’t set up exactly how you want them to be. Like in our example above, we want the name to look presentable by adding a space between the two cells.

Spaces are included in this Google Sheets function using double quotes:

How to use the and and or functions in google sheets

If you can’t see here, there’s a space within those quotes. The idea behind using the quotes is that you’re entering data manually and not choosing spreadsheet data.

In other words, A1 and B1 are clearly part of the spreadsheet already, so you’re referencing them by entering them as they are (the cell letter plus the cell number). However, to include your own data within the formula, you need to surround it in quotes.

Adding Text to a CONCATENATE Formula

The CONCATENATE function can do more than just join a couple cells and put a space between them. Below is an example of how to use CONCATENATE to form an entire sentence using cell data.

In this example of the CONCATENATE function, we’re stringing together the county and its rank number, but instead of leaving it at that, we’re using spaces and our own manually-entered data to create a full sentence:

How to use the and and or functions in google sheets

To make the formula work like regular English, don’t forget to put spaces where necessary. You can’t add a space right after a cell reference (like C2 above), but you can when using double quotes. As you can see above, we used a space multiple times in our quotes to make the sentence read normally.

Applying the CONCATENATE Formula Elsewhere

Lastly, the only real use of the CONCATENATE function is when dealing with enough data that time is being saved versus entering the data manually. So, all you need to do to make the formula work with the other cells is drag it downward.

Click the cell once so that it’s highlighted. You should see a small box on the bottom right-hand corner of the cell, like this:

How to use the and and or functions in google sheets

Click and hold that box while dragging it downward to apply it to the dataset. Stop dragging once you’ve reached the last item you want the formula to be applied to. You can always drag it again from there should you need to include more cells later.

How to use the and and or functions in google sheets

Interestingly, Google Sheets has a similar function called SPLIT. However, instead of joining the cells, it splits one cell into multiple cells depending on which character you choose to mark as the split-off point.

Founder of Help Desk Geek and managing editor. He began blogging in 2007 and quit his job in 2010 to blog full-time. He has over 15 years of industry experience in IT and holds several technical certifications. Read Aseem’s Full Bio

Since I am not a software engineer, I rely on plugins to solve a lot of my server side problems. Plugins will make things a lot easier for you but they aren’t always as robust as we’d like them to be. Sometimes getting your data the way you need it can present a challenge as well. So how does a front-end developer fix this problem?

Let’s say your client wants to export some pricing data from one site and import into another, and their old site formats data in separate cells:

Example:

Small Qty. | Small Price | Med Qty. | Med Price | Large Qty. | Large Price.

1-11 | $3.00 | 12-20 | $2.65 | 21+ | $1.34

But you need your data formatted differently for this new site you’re moving to. They require you to format your data in 1 cell.

Example:

(qty + ‘:’ + price + ‘|’ + qty + ‘:’ + price + ‘|’ + qty + ‘:’ + price + ‘| )

Google Spreadsheets makes it really easy to format your data however you’d like by using custom JavaScript functions.

Step 1: create a spreadsheet / or upload existing

How to use the and and or functions in google sheets

As you can see from the image above, I have my cells formatted from the previous site with what I need my final outcome to be. I repeat the same data a few rows down with an empty Final Outcome field because that’s where we’ll be putting our custom JavaScript function.

Step 2: Create new script file

By selecting Tools > Script Manager in the menu bar at the top you’ll bring up a screen that looks like this. Select New to start a new file.

Step 3: Writing your JavaScript

How to use the and and or functions in google sheets

Now that you have created this new script file, Google starts you off with a dummy function to show you where to put your code. Here is where we’ll write whatever JavaScript we need to format our data the way we need to. We specify in our function how many different arguments we’ll be taking in and return our final value once formatted. Once you’ve written your JavaScript, remember to save it. Google will automatically use the scripts you save in your open spreadsheet so there is no need to import anywhere.

Step 4: Using the code you’ve written

How to use the and and or functions in google sheets

Now that we have our custom JavaScript function saved we can use it on the data fields we have by using the function the way we would with any of the built-in functions Google has to offer. Once you’re finished, tab out of your current tab to submit the function and you’re cells will be formatted the way you need it to be.

Formatting data this way can save a lot of time for developers or even non-developers who know a bit of JavaScript. Google makes its script editor very easy to use and because it’s JavaScript there is no learning curve for us front-end developers that already know it. And, since all of the tools used are hosted by Google, there is no need to purchase any extra software or plugins to format your data for you.

googlesheets4 provides an R interface to Google Sheets via the Sheets API v4. It is a reboot of an earlier package called googlesheets.

Why 4? Why googlesheets4? Did I miss googlesheets1 through 3? No. The idea is to name the package after the corresponding version of the Sheets API. In hindsight, the original googlesheets should have been googlesheets3.

Installation

You can install the released version of googlesheets4 from CRAN with:

And the development version from GitHub with:

Cheatsheet

You can see how to read data with googlesheets4 in the data import cheatsheet, which also covers similar functionality in the related packages readr and readxl.

How to use the and and or functions in google sheets

googlesheets4 will, by default, help you interact with Sheets as an authenticated Google user. If you don’t plan to write Sheets or to read private Sheets, use gs4_deauth() to indicate there is no need for a token. See the article googlesheets4 auth for more.

For this overview, we’ve logged into Google as a specific user in a hidden chunk.

Attach googlesheets4

The main “read” function of the googlesheets4 package goes by two names, because we want it to make sense in two contexts:

  • read_sheet() evokes other table-reading functions, like readr::read_csv() and readxl::read_excel() . The sheet in this case refers to a Google (spread)Sheet.
  • range_read() is the right name according to the naming convention used throughout the googlesheets4 package.

read_sheet() and range_read() are synonyms and you can use either one. Here we’ll use read_sheet() .

googlesheets4 is pipe-friendly (and reexports %>% ), but works just fine without the pipe.

  • a URL
  • a Sheet ID
  • a dribble produced by the googledrive package, which can lookup by file name

These all achieve the same thing:

Note: the only reason we can read a sheet named “gapminder” (the last example) is because the account we’re logged in as has a Sheet named “gapminder”.

See the article Find and Identify Sheets for more about specifying the Sheet you want to address. See the article Read Sheets for more about reading from specific sheets or ranges, setting column type, and getting low-level cell data.

Write

gs4_create() creates a brand new Google Sheet and can optionally send some initial data.

sheet_write() (over)writes a whole data frame into a (work)sheet within a (spread)Sheet.

sheet_append() , range_write() , range_flood() , and range_clear() are more specialized writing functions. See the article Write Sheets for more about writing to Sheets.

Where to learn more

Get started is a more extensive general introduction to googlesheets4.

Browse the articles index to find articles that cover various topics in more depth.

See the function index for an organized, exhaustive listing.

Contributing

If you’d like to contribute to the development of googlesheets4, please read these guidelines.

Please note that the googlesheets4 project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.

Privacy

Context

googlesheets4 draws on and complements / emulates other packages in the tidyverse:

Google has announced that Google Sheets is getting the ability to intelligently suggest formulas and functions for your spreadsheet, based on the data you’re trying to analyze. For example, typing “=” into a cell below a list of numbers will pop up a box that lets you automatically add the numbers together, find their average, and more.

From my admittedly simple tests, it seems to be a pretty smart system. For example, with one column of data, it suggested that I could be looking for either the sum or average of the numbers. After I chose the sum and moved down to the next cell, it only suggested finding the average for the same range of numbers, not including the sum I’d just calculated. It’s a simple thing, keeping track of what’s data and what’s analysis, but it’s easy to imagine a version of this that gets tripped up on that.

Google says that to create these suggestions, it trained a machine learning model using anonymized data from certain spreadsheets. The model doesn’t just take how often certain formulas are used into account, though — it also looks at the context within sheets.

For example, as you can see in the GIF above, I had one row labeled as “Total,” and the system only suggested the sum formula. After I removed the “Total” label, it suggested both sum and average. Google told The Verge that it can also identify headers and look at how data is grouped to get an idea of what suggestions to make.

Spreadsheet programs have long tried to make their users’ lives easier with autocomplete features. For example, both Sheets and Excel feature a sort of series continuation feature, where it’ll try to detect what you’re doing in a selected range and then continuing it. For example, if I’ve got a list that goes 2, 4, 6, I can tell Sheets to continue that for a bunch of cells, and it’ll auto-populate the next cells with 8, 10, 12, etc. While these existing systems are definitely timesavers, it’d be a stretch to call them “intelligent” — you don’t have to use them much before you find something that they misinterpret.

It works with the basics, but falls down with curveballs (or Fibonacci sequences). Still a great life hack, though.

While Google’s intelligent function and formula suggestions aren’t as impressive as, say, Github’s Copilot tool that autocompletes code, they both represent tech’s ability to smooth over the mundane time sucks that take up more time than we’d probably realize. Is it particularly difficult to, say, type out “SUM” then select the range I want? No. Is it great to have the program just do it for me, as it can with email replies and Python functions, letting me get on with whatever it is that I need to use my human brain for? Absolutely.

According to Google’s blog, the feature started rolling out to Workspace, G Suite, and personal Google accounts on Wednesday and will take up to 15 days to show up for everyone. The feature can also be turned off if you’re not fond of the pop-ups. Google also says that the rollout will provide “users with visibility into whether previously compiled formulas need further verification.”

In a Google Docs spreadsheet, I’m looking for something like =EVAL(A1) where A1 is set to “=1+2” .

I found out that in MS Excel there is an EVALUATE() function (which seems a bit tricky to use properly). But I could not find anything similar in Google Docs.

I also searched through the function list, but could not find anything helpful.

How to use the and and or functions in google sheets

9 Answers 9

No, there’s no equivalent to Excel’s EVALUATE() in Google Sheets.

There’s long history behind this one, see this old post for instance.

If you’re just interested in simple math (as shown in your question), that can be done easily with a custom function.

B1 display value:

Example 2

To “EVALUATE” a formula like =VLOOKUP(2,A1:B3,2) , at this time we need to use the “advanced” parameters. See the following example:

C1 display value:

Code.gs

formula1.gs

socialcalcconstants.gs

socialcalc-3.gs

How to use the and and or functions in google sheets

I know this an old post. I’m just wondering, why nobody suggested:

This will give you the result of the formula in myCell (3 in your example).

If you want to write the result to the cell (instead of the formula), you could use:

If you want to evaluate simple math(like A1: “(1+2)*9/3”), you can use query:

How to use the and and or functions in google sheets

Copy and paste the formulas:

Maybe you can copy and paste the formulas you need from “jQuery.sheet”. Moved to:

Looks to be all “open source”

Wont fix the issue

Also: The issue “Enable scripts to use standard spreadsheet functions” is marked as “Wont fix”, see https://code.google.com/p/google-apps-script-issues/issues/detail?id=26

Ethercalc there is a google like opensource spreadsheet called Ethercalc

Date and time functions in Google Sheets have the same usage and syntax as in Excel. Learning the different applications of date and time formulas will be very helpful in performing related calculations that we might encounter while working with Google Sheets.

Current date and time

The current date and time can be inserted through the functions TODAY and NOW. TODAY function returns the current date while the NOW function returns the current date and time. However, the NOW function can be customized to show only the date, or the time.

The below image shows the different ways to show the current date , current time or both date and time using the functions TODAY, NOW and TEXT.

How to use the and and or functions in google sheetsFigure 1. Output: Current date and time

Time functions

The time functions are TIME , HOUR , MINUTE and SECOND . TIME function generates a certain time given the specific data for hour, minute and second portion of the time. In cell D2, we are able to insert the time 1:20:16 AM by using the TIME formula

=TIME(1,20,16)

Where 1 is the hour, 20 is the minute and 16 is the second portion.

Conversely, we can determine the hour, minute and second portion of the time 1:20:16 AM by using the functions HOUR, MINUTE and SECOND, respectively.

The below image shows the formula and results when using time functions.

How to use the and and or functions in google sheetsFigure 2. Output: Time Functions

Add or subtract time

In order to add or subtract time , we have to apply the basic mathematical operation addition or subtraction. These formulas are rather straightforward and does not involve the use of any function.

To add time, we enter the first value, the plus “+” sign, then the second value. On the other hand, in order to subtract time, we only have to change the sign from a plus sign to a minus “-” .

How to use the and and or functions in google sheetsFigure 3. Output: Add or subtract time

Timesheet

Timesheets are widely used to record and calculate the number of hours worked. With spreadsheets, the task of maintaining a timesheet has become easier and more accurate.

In order to create a timesheet, we have to make a spreadsheet with a list of days, a column for Time IN, Time OUT, and hours worked. The formula for hours worked is

= Time_OUTTime_INunpaid_hours

Unpaid hours could refer to lunch breaks or under time.

The below image is an example of a timesheet from Monday to Friday, with one unpaid hour every day for lunch break. The timesheet formula is given by

=D5-C5-1/24

where 1/24 corresponds to one hour in a day.

How to use the and and or functions in google sheetsFigure 4. Output: Google Sheets timesheet

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

This tutorial will introduce you to the concept of a range in Google Sheets. You’ll learn about what ranges are and how to use them. You’ll also learn about named ranges and the benefits of using them.

What is a range?

A range represents a single cell or a group of adjacent cells in your spreadsheet. Every time you work with data in a spreadsheet, you’re likely using one or more ranges.

The screenshot below shows 5 different ranges in Sheet3 of your spreadsheet.

How to reference a range in a Google Sheets formula?

To reference a single cell in a formula, use the name of the sheet followed by an exclamation mark, the column and finally the row.

A cell that is in Sheet1 at the intersection of column C and row 5 will have the following reference:

Sheet1!C5 . This type of reference is known as A1 notation.

To reference a range composed of a group of adjacent cells, we’ll need to specify the two cells that are at corners of any diagonal within the range. Typically, the cells that are at the top left and bottom right corner are the ones that are specified.

🛈 If the group of cells is fully contained within a single row or column then the top left and bottom right cells are just the first and last cells in the group.

The screenshot below displays multiple ranges (the ones that have been colored) and in each case the top left and bottom right cells have been filled with a darker color.

To reference a group of cells in a formula, use the name of the sheet followed by an exclamation mark, the column of the top left cell, its row, a colon, the row of the bottom right cell and finally its column.

For example, the below references correspond to the ranges highlighted in the above screenshot.

Range colored green: Sheet3!B3:D10

Range colored blue: Sheet3!F3:F8

Range colored: purple: Sheet!B13:D13

Range colored orange: Sheet3!H5:I11

You can also define ranges that reference entire rows or columns:

All rows in one column: Sheet3!B:B (use the column name twice and omit the row numbers)

All rows in multiple adjacent columns: Sheet3!B:D (use the names of the first and last column in the range and omit the row numbers)

All columns in a single row: Sheet3!2:2 (use the row number twice and omit the column names)

All columns in multiple adjacent rows: Sheet3!2:10 (use the numbers of the first and last row in the range and omit the column names)

How to use a range in a Google Sheets function?

To use a range in a function, just use the range’s reference. For example, in order to calculate the sum of values in the range Sheet4!D2:E6 , use the formula =SUM(Sheet4!D2:E6) . To sum values in just a single cell, say Sheet4!B2 , use =SUM(Sheet4!B2) .

Named ranges in Google Sheets

In Google Sheets, you can assign a name to a range. Once you do this, you can use the name of a range instead of its reference in formulas and scripts.

There are several ways to create a named range:

1. Select Data —> Named ranges and enter the name and reference.

Your browser does not support HTML5 video. Here is a link to the video instead.

2. Select a range in the spreadsheet, right click and select Define named range to give it a name.

Your browser does not support HTML5 video. Here is a link to the video instead.

3. Create a named range by using Google Apps Script.

You can also create named ranges using Google Apps Script. The code below shows you an example of how to do that.

Once you create a named range, you can use it in formulas and scripts by using its name. So, instead of Sheet2!A1:C6 , you can use the range’s name like this: StudentGrades .

Using named ranges has several benefits:

Formulas and scripts will become more readable because the name of the range will help users understand the type of data contained in it. For e.g., StudentGrades is a lot more descriptive than Sheet2!A1:C6 .

When you make changes to a named range, all the formulas and scripts will immediately begin using the updated range.

Conclusion

This tutorial covered the basics of working with ranges in Google Sheets. You learned how to:

I’m having a bit of a struggle. I’m creating a spreadsheet which uses plus signs ( + ) regularly. I want a semi-permanent fix for + ‘s turning into addition formulae.

How to use the and and or functions in google sheets

7 Answers 7

The easiest workaround is to enter an apostrophe ‘ as the first character, right before the + .

Another approach is to enter the contents as a string formula like =”+5 blah” .

An initial plus sign is very much needed for some types of data, e.g.- international phone numbers, so it is unfortunate that even setting the format to plain text does not help here.

How to use the and and or functions in google sheets

Do a Find & Replace for = with ‘ and check “Also search within formulas.”

In your Google Sheets select the area and go to FORMAT —–> NUMBER —–> PLAIN TEXT. Now the formulas will not work anymore!

Just add a space before the + sign. Seemed to work for me

Depending on your application, the following solution could be useful:

Highlight the cell(s) that you would like to appear with a + . Go to the “123” Formatting, choose “More Formats,” then go to “Custom Number Format. “

Type “+”@ into the Custom Format box. This specifies to add a + before the entry no matter if it is a positive, negative, zero, or text entry.

Now, just type in your plain information, and it will appear with a + before it. The problem is that when dealing with formulas, the data will still be shown without the positive sign.

How to use the and and or functions in google sheets

How to use the and and or functions in google sheets

The way to do this is by adding an apostrophe (‘) before the + symbol, as written in Silver Ringvee’s answer. However, you can automate it using Google Apps Script.

Here is a simple Apps-Script script I created to solve the problem. Whenever you edit a cell, it searches through the sheet, and places apostrophe (‘) before the text, i.e. you can simply add +hello, without worrying about adding ‘ in the beginning. The script will do i automatically for you.

Usage-

Open the sheet, go to Tools->Script editor . In the editor that opens, paste the following code. Then add triggers to the script so that the script runs automatically whenever you edit a cell.

In the editor go to Resources -> Current project’s triggers . In the box that opens select Add a new trigger In column Run select main function, in Events select From spreadsheet , then select On edit for the last column.

Google aims to integrate the most popular Google Workspace productivity apps in the Gmail app for a more uniform user experience. This move will end the need to keep multiple tabs open for different tasks. With Google Chat you can communicate directly with people, create temporary group conversations, or start more formal group chats called Spaces. The latter ones can be used for department communication, project management, event planning, or any other elaborate operation. You can access all these features from within Gmail, without having to keep a separate Chat tab open. To enable Google Chat in Gmail, on the web, go to Settings and then navigate to the Chat and Meet tab. Select Google Chat and click Save Changes. To enable Google Chat in Gmail, on mobile, go to Settings, tap on your work or school account and then switch on the Chat toggle. Now you can access the Google Chat features from within the Gmail app on web and mobile. On the web, you can find chats and spaces in the left side panel but if you prefer a bigger window, go to Settings > Chat and Meet and then select Right Side of the inbox. On mobile, once you’ve enabled Chat, you will see two new tabs at the bottom of the screen for chats and spaces. Now you can save time and stay productive by using only the Gmail app.

Google aims to integrate the most popular Google Workspace productivity apps in the Gmail app for a.

Accept or Reject all Suggestions in Google Docs

Google Docs enables Google Workspace users to collaborate on documents in real-time and from any device. Suggestions mode is a Google Docs feature that allows collaborators to make suggested edits, which can then be approved or rejected by others. When someone adds suggestions to a document, you will see them displayed on the right side of the page, just like in the example below. You can then accept or reject them individually… but what if there was a way to resolve all suggestions with just a couple of clicks? Fortunately, we know the right trick to do this. To accept or reject all suggestions in Google Docs: From the menu bar, go to Tools and then click on Review suggested edits. A pop up will show up in the right corner of the page with the options to accept or reject all suggestions. You can also preview the edits before making a decision. Choose an option and allow the document to update. This may take a few moments based on the number of suggestions. If you have accidentally clicked the wrong button, simply press Ctrl+ Z on your keyboard to undo the changes (Cmd ⌘ + Z on Mac). This tip will save lots of time next time you have to resolve suggestions in a document.

Google Docs enables Google Workspace users to collaborate on documents in real-time and from any.

How to pre-fill a Google Form

Google Forms is great tool for surveys, quizzes or simple data collection. But did you know that you can share a pre-filled form with others? Here’s how to pre-fill your Google Form with default values: 1. Open the Form you want to pre-fill. 2. Click on the 3 dots on the top right to access the Form menu. 3. Select “Get pre-filled link” 4. Type in the default values in the right fields for example, here, we have set: – Number of licenses to “1” – License type to the 1st choice “GSuite user license” – Comments to “Thanks” 5. Click the Get link buttom form the bottom of the form. 6. Click “Copy link” from the notification pop-up. You will get a URL to the Form to fill including the values you set-up as the default ones. Here’s an example – you can see that some fields are already set. If you need to change the default values, you can log into your Google account and use the same link to edit the form. Tip from Shared Contacts for Gmail®

Google Forms is great tool for surveys, quizzes or simple data collection. But did you know that.

How to use the and and or functions in google sheets

Even without using functions, Google Sheets is still a remarkable tool for business or study. Knowing how to use functions like IF and THEN, however, expands its usefulness almost exponentially.

In this article, we’ll explain everything you need to know about the IF THEN statements in Google Sheets, and show examples of how to use them.

Adding Logic to a Spreadsheet

The IF THEN Statement operates like a simple logic question. The function checks whether a statement is true or not then returns a value depending on the answer. You can look at the function as ‘IF this is TRUE, THEN do this, otherwise do something else’. Keeping this in mind when you create IF THEN statements will simplify the coding into chunks of information that you can easily understand.

The syntax is pretty simple until you get in to nested IF statements. That is, an IF statement within an IF statement. Even then, the only complication isn’t that it’s hard to create a formula, but that it’s easy to misplace values, especially for longer IF THEN statements. If you don’t lose sight of the fact that you’re just making a long logic question though, it makes it easier to find any potential errors.

How to use the and and or functions in google sheets

The Syntax

The Google Sheets IF THEN Function can be used by using the following syntax: =IF(Logical Expression, value-if-true,value-if-false) where:

‘=’ indicates to Google Sheets that you’re using a function.

‘IF’ indicates that the values in the parenthesis will be tested to be true or false.

‘Logical Expression’ is the condition that is to be tested.

‘value-if-true’ is what will be returned if the logical expression is true.

=value-if-false’ is what will be returned if the logical expression is false.

For example, if you want to test whether cell A1 is greater than cell A2, then display the value GREATER if it is, and LESS THAN if it’s not, then you’ll write the formula as =IF(A1>A2,”GREATER”,”LESS THAN”).

You can also insert other functions into the statement, such as the SUM function. For example, if you want to see if the sum of cells A1 to A10, is over 1000, then you can write the formula as =IF(SUM(A1:A10)>1000,”GREATER”,”LESS THAN”).

How to use the and and or functions in google sheets

Nested IF THEN Statements

Nested IF THEN statements operate on the same logic as a normal IF THEN statement. It checks if a logical expression is true or not, then returns a value if it is. This time, however, instead of returning a value immediately if the expression is false, it checks if another logical expression is true or not. This process repeats until you get to the end of a formula. A simple example would be the determination of a grade. If your grade is 90 and above you get an A, if you’re 70 to 89, you get a B, 50 to 69 will get you a C, 35 to 49 will get you a D, and any less will get you an F.

If you were to write this into a nested IF THEN statement then you’d get ‘=IF(B1>89,”A”,if(B1>69,”B”,if(B1>49,”C”,if(B1>34,”D”,”F”))))’. Google Sheets will check each condition one by one and if it finds a condition that’s true it will stop. The Nested IF function will try to find a true condition. If it does, it will no longer check the other conditions.

This is important to remember if you want different results for particular values. In the example above, if you had written the formula to check if the grade was higher than 35 first before checking any other value, then any grade higher than 35, whether it’s 36 or 99, will stop there. It will no longer check to see if it meets the other conditions, and will return the value-if-true that you entered. Keep this in mind when making your formula.

How to use the and and or functions in google sheets

Using the AND / OR Statement with IF THEN Statements

If you want to check the truth value of more than one logical expression before returning a result, you can utilize the AND / OR function on your IF THEN Statement. The AND function returns True if all the parameters you enter are true, and the OR function will return true if at least one of the parameters is true.

The Syntax of AND Statements go by =AND(Logical Expression1,Logical Expression2, Logical Expression3…), while OR shares the similar syntax =OR(Logical Expression1, logical expression2…). As using the SUM function, you omit the ‘=’ to avoid encountering errors.

For example, you wish to evaluate the values of two cells, A1 and A2 to see if their values are equal to or greater than 100. The formula for an IF statement would then be =IF(AND(A1>99,A2>99),”True”,”False”). This will only return true if both cells are 100 or more, and return false even if only one is less than 100. Using OR will return true if at least one is greater than 100.

How to use the and and or functions in google sheets

If you wish to further evaluate more cells, you can either expand upon the AND / OR function, or nest further IF THEN Statements. As long as you can simplify a condition to a true or false question, the uses for this are limited only by your imagination.

Simple but Extremely Versatile

The IF THEN function for Google Sheets makes it easier for users to automatically assign results for different values. Its limits are only bound on how well you can construct a logical condition. The utility of this simple, but extremely versatile function can’t be over emphasized.

Do you know of other ways to utilize the IF THEN Statement in Google Sheets? Share your thoughts in the comments section below.

How to use the and and or functions in google sheets

Import, export, email. Do it again. When it comes to data analysis, it’s easy to fall into routine. But no matter how much of a whiz you are at formulas or pivot tables, superb spreadsheet skills only take you so far if you’re working with multiple versions or outdated datasets.

On average, employees spend up to eight hours each week—an entire work day—searching for and consolidating information. What if businesses spent their time applying data insights instead of tracking them down?

We designed cloud-based tools, like Google Sheets, to make it easier to quickly organize and analyze information in one place. Here are five reasons why you should try using Sheets.

1. Your data always stays up to date.

Working in the cloud means your data can easily stay up to date because information is automatically saved as it’s typed. Multiple team members can collaborate in real-time from their phone, tablet or computer (online and offline) and create a single source of truth for projects, like quarterly budgets.

Need personalization? No problem. You can look at the same data as your coworkers without disrupting their view, sort information to be in a specific order or hide sheets that you don’t need to see. If you’re worried about others mucking up your data, you can protect cells by setting custom share settings. Plus, you don’t have to worry about version control. You can see changes or revert to previous versions in File > Version History or by clicking on “All Changes Saved in Drive” at the top of your Sheet.