HomeGoogle DocsSpreadsheetHow to Use IF Function in Google Sheets - Advanced Tips

How to Use IF Function in Google Sheets – Advanced Tips

Published on

Much has been talked about the popular IF Function in Google Sheets here on Info Inspired. Should I write a separate tutorial for it again? This question was in my mind for a quite long time. The reason, I have already written lots of tutorials that touched this logical function, but in the real sense, there is no specific tutorial on it.

I have already made the comparison of IFS and Nested IF, IF with Google Sheets AND, OR functions in combined form and many such variations. But the thing is that I don’t have here a specific tutorial for IF function in Google Sheets. I wish to cover that void spot with this tutorial.

What is the use of Google Doc Spreadsheets IF function?

With the help of IF function, you can test value in a cell or in an Array. That is the one and only use of it. IF function can bring much more dynamism to your formulas in Google Spreadsheets. Other similar functions are SUMIF and SUMIFS.

What is Logical Test in IF Function?

As already told you above, the IF function can test value in a cell or range. But it’s a logical test.

Let us see how can we logical test a value in a cell using Google Sheets IF function.

Suppose Cell A1 has a value 50. With IF function, you can apply different logical tests on that value as below. I am using different comparison operators here.

IF Formula 1:

=IF(A1=50,TRUE,FALSE)

Its meaning is whether the value in A1 is equal to 50 or not. It’s a logical test, right? If the value is 50, the formula would return TRUE else FALSE.

IF Formula 2:

=IF(A1<>50,TRUE,FALSE)

Here the meaning is whether the value in A1 is not equal to 50.

IF Formula 3:

=IF(A1<50,TRUE,FALSE)

Here the meaning is whether the value in A1 is less than 50.

IF Formula 4:

=IF(A1>50,TRUE,FALSE)

Here the formula tests whether the value in A1 is greater than 50.

IF Formula 5:

=IF(A1<=50,TRUE,FALSE)

Here with the help of the above formula, we can test whether the value in cell A1 is less than or equal to 50.

IF Formula 6:

=IF(A>=50,TRUE,FALSE)

Here we can test whether the cell A1 value is greater than or equal to 50.

With IF you can test the value as above and if found a match, it returns true, else false. Similarly, you can test text string or date using IF. Also, you can replace the TRUE and false with any values or other formulas.

IF Function Syntax and Explanation

Now let us move to the syntax and arguments.

Syntax:

IF(logical_expression, value_if_true, value_if_false)

Now you can clearly understand the arguments in the function.

Logical Expression:  In the above example we put one value in Cell A1 and tested it with different operators like less than, greater than etc. You can say “A1<50” as an example to logical_expression in IF function.

Value If True: Once tested the function can return whether the answer is true or false. The “value_if_true” argument deals with the true part.

Value If False:  The “value_if_false” argument deals with the false part.

The last two arguments, you can understand from the below examples.

How to Use IF Function in Google Sheets?

Now it’s time to go through few examples of IF function to understand it fully. I’ve applied different color patterns to make you understand all the arguments in the syntax.

Here is the syntax once again.

IF(logical_expression, value_if_true, value_if_false)

The formulas:

Google Sheets IF Function examples

Use this spreadsheet copy and change the values under Column A to understand the function properly.

Conclusion

After learning the proper use of Google Sheets IF, don’t just limit the use of it to that extent only. Move to the advanced use of Google Doc’s IF Function. Our Nested IF tutorial can help you a lot in this direction.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

8 COMMENTS

  1. 1. How can I copy-paste a formula with just the same one cell by not changing the formula if pasted?

    2. How to create an aging deducting the completion?

  2. When A1 is populated B1 should be auto-populated with today’s date. When I open the google spreadsheet tomorrow, B1 should still have today’s date.

    Is there a formula for this? I’m using a tablet to create Google Spreadsheet. Thanks!

  3. Hi!

    Congrats for this amazing site!

    I’m really a noob and would really appreciate you to help me with the issue below.

    1. There’s a column with different texts.
    2. I need to categorize every text with different specific tags in another column.
    3. For instance: if A2 contains “WordA” then write “A” in B2; if A2 contains “WordB” then write “B” in B2, and so on (up to 10 different tags).

    Is this possible?

    Thanks in advance!

    • Welcome!

      I am glad to hear that you liked this site.

      Regarding the problem, you can use IF, IFS or CHOOSE. I prefer the function CHOOSE here. Try this formula in cell B2.

      =switch(A2,"WordA","A","WordB","B","WordC","C")

      You can easily add more conditions to it.

      For an entire column range A2:A, just apply the below formula in B2.

      =iferror(switch(A2:A,"WordA","A","WordB","B","WordC","C"))

      Thanks!

    • Hi Sangita,

      Welcome to Info Inspired!

      Here is a sample formula.

      =query(iferror(GOOGLEFINANCE(“NSE:HDFCBANK”,”volume”,DATE(2018,4,1), DATE(2018,4,10), “DAILY”)),”Select Avg (Col2)”,1)

      I think this formula can find the average volume of the provided ticker. You can change the ticker and date duration.

      Note:
      1. Please re-type the double quotes in formula.
      2. Learn Googlefinance function.
      Thanks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here