Combined Use of IF, AND, OR Logical Functions in Google Doc Spreadsheet

27
257

Learn how to use important logical functions IF, AND, OR in Google Doc Spreadsheet. Logical functions are very easy to use if you get proper examples. You can find examples of logical functions online. But when you use logical functions in a combined form it expands the usefulness of some logical functions. It is rare to find simple examples for combined use of logical functions.

Some of you may familiar with Microsoft Excel Logical Functions. If you know if, and, or like logical functions in Excel you can similarly use it in Google Doc Spreadsheet. There is no change in the Syntax. It is easy to use logical functions individually. But logical functions are powerful when you use it in combined form. In this easy to follow Google Doc Spreadsheet tutorial we can learn how to use logical functions in combined form.

What are Spreadsheet Logical Functions?

I am not telling you the exact definition of logical functions. My intention is to give you a clear idea what logical functions are. With logical functions you can test the value of one cell and decide the result.

Logical Functions IF, AND, OR

In this example we are going to learn logical functions IF, AND, OR.

IF : This logical function is used to find whether an argument is true or false. If true this function brings one result and false another result.

Example : In Cell A1 put the value 50. Type any of the below formula in cell B1.

a) IF(A1>50,”Pass”, “Failed”)

or

b) IF(A1>50,”A1*20″, “A1*10”)

In the first logical test if the value in A1 is above 50, the Cell B1 shows “Pass”, else shows “Failed”

In the second logical test if the value in A1 is above 50, the Cell B1 get multiplies with 20, else get multiplies with 10.

Hope you understand this.

AND : This logical function is used to expand the use of IF function.

=AND(TRUE, TRUE) All arguments are TRUE, result is TRUE

OR : Similarly above, this logical function is also used to expand the use of IF function.

=OR(TRUE,FALSE,TRUE) At least one argument is TRUE (TRUE)

I know you have doubt about AND, OR functions. You can learn it easily from the below example.

Learn IF, AND, OR logical functions easily from the below combined example.

How to use Logical Functions in Combined form in Excel Spreadsheet or Google Doc Spreadsheet?

You may have noticed that I’ve not detailed AND, OR logical functions above. It is because it is normally used to expand the usefulness of IF. So the below combined use of logical functions will clear your doubt about using these functions.

Similar to read! Simplified the Use of SUMIF function in Google Sheets

You may also interested in! How to use SUMIFS function in Google Sheets?

Download our eBook Now!

[Googel Sheets 20 proven tricks; that can boost your productivity – A must for all office goers!]

First type the contents as shown on the screenshot in a spread sheet. No matter whether you use Excel spreadsheet or Google Doc. You can omit Condition 1, Condition 2, Condition 3 because there we will apply logical functions later.

logic

Hope you have typed the above data.

Now the formula

  • =IF(OR(B3>49,C3>49,D3>49),”Passed”,”Failed”)
  • =IF(AND(B3>49,C3>49,D3>49),”Passed”,”Failed”)
  • =IF(OR(AND(B3>49,C3>49),AND(B3>49,D3>49),AND(C3>49,D3>49)),”Won”,”Failed”)

The first formula should be typed under condition 1, the second one under condition 2 and the final one under condition 3.

What these logical functions do?

1. Shows “Passed” if marks scored in any subject is >49

2. Shows “Passed” only if marks scored in all subjects is >49

3. Shows “Passed” if marks scored in any two subjects is >49

Go through the examples carefully several times. If you have any doubt drop it in comments below.

27 COMMENTS

  1. Helped me too. Thanks!
    In order to understand this, you first have to understand conditional checks and a bit of Boolean logic.

    I didn’t look too deeply, but do you have a primer on the available operators? I keep trying to do “!=” (not equal) in my IF conditions, and it..doesn’t work.
    e.g. =IF(B6!=0, A6/B6, “”)

    • “!” is only used if your referring to a cell that is on a different sheet. e.g. Sheet2!A1. Sheet2 = refers to the other sheet where the reference cell is locaed. A1 = is your reference cell. If your reference is on the same sheet, you should only apply the cell name itself.

      =IF(B6 = “0”, A6/B6, “”) — try this

    • It’s definitely a comma. Also one more thing. If anybody simply copy the function from my above post, it may not work. You may need to retype the double quotes symbol. Thanks.

  2. Hello,

    I need help with conditional formating of a google spreadsheet.
    I have in A1 List with “delivered” and “in work” and in A2 the delivery date.
    How can I adjust colors in that way to can have next:
    if delivery date >today – cell green
    if delivery date<today – cell red
    if delivery date=today – cell magenta
    This steps I allready resolved, but the problem is coming now:
    I want to make all row green even date is = than today if A1=”delivered”
    I tryed a lot f things, but always A2 keeps his color(green, red or magenta).

    Maby somebody can help me.

    Thank you!

    • Hi, It has nothing to do with conditional formatting.
      There is option in Conditional formatting
      Format>Conditional Formatting>Format cells if>date is after…
      Try it. Thanks.
      Sorry for the late reply to all. As I was busy with my job. Now on vacation.

  3. I am having a hard time with my conditional equation for google sheets. Here is my equation but it is not working. =IF(K:K=”NADINE”),AND IF(M:M =”X”),THAN (Q3+1).

    What I am trying to do is have google sheets check two columns for date and if they meet the criteria then calculate the number that matches the criteria. in another box. Can you help?

  4. Dear Prashant,
    Thank you for your help but there was a confusion when I tried to use the “if” as you explained ” IF(A1>50,”A1*20″, “A1*10”) : In the second logical test if the value in A1 is above 50, the Cell B1 get multiplies with 20, else get multiplies with 10.”
    There is no B cell in the example and need to use the formula without “” to mulitly else it will show up the text

    Here

  5. Hi, I am using a Google Sheets doc, where I need to make a spa menu formula. If D1 (the massage type) shows massage “a”, AND the hour cell D2 shows, 0.5 OR 1 OR 1.5 OR 2 (hours), then the price of the treatment will show in cell D3, in US dollars, as “$A”, “$B”, “$C”, or “$D” respectively. Is there a way to do this? It is worth noting I have about 30 treatments that need to be nested into 1 formula, in exactly this way…

    Thank you!

    • Welcome! You can do it simply by using the “IF” logical function. No need to go for the combined one. Just send me the sample database. I will try to solve it for you.

  6. Here is my issue — I thought this might solve it but I am not sure I follow
    I have multiple criteria. Here are the formulas
    =SUMIFS(F2:F, A2:A, “=January”, D2:D, “a la carte”)
    =SUMIFS(F2:F, A2:A, “=January”, D2:D, “=a la carte”)

    However – I now know that there is an additional option needed to land in the second cell “Broker”
    So the top would be in plain english “everything but a la carte OR broker”
    The second would be only those 2 [there are about 8 other options]

    Not sure how to tweak it — i’m a novice teaching this to myself

  7. Hi, Prashanth.
    I have a nested IF fomula where if a cell has specific values then the outcome is either 0 or 5 or 1 (see below). I have tried to use the IF(OR combination but in the examples given here, there are only two possible outcomes. I need three. How can this be achieved?
    =IF($A$8=0,0,
    IF($A$8=7,0,
    IF($A$8=10,0,
    IF($A$8=21,0,
    IF($A$8>10,5,1)))))

    • Hi,
      It’s not the correct way of using a nested IF. The above nested formula will return the following results;
      If Value in Cell A8=0,7,10 or 21, result will be 0
      If Value in Cell A8>10 and except 21, result will be 5
      If value in Cell A8<10 and not already specified above, the result will be 1
      Hope you understand what is the output of your formula.

  8. Hello,

    I have created the formula:
    =ARRAYFORMULA(IF(ROW(F:F)=1,”Profile”,if(B:B=””,””,if(AND(B2:B=”ST – Street”,C2:C=”No”,D2:D=”No”,E2:E=”Yes”),”ST ORDER ONLY INV”,”error”))))
    It is giving me the final “error” message I requested for everything, even though a few of my rows have the correct variables to result in the “st order only inv” message.
    If I point it to specific cell like so:
    =ARRAYFORMULA(IF(ROW(F:F)=1,”Profile”,if(B:B=””,””,if(AND(B2=”ST – Street”,C2=”No”,D2=”No”,E2=”Yes”),”ST ORDER ONLY INV”,”error”))))
    It gives me the correct “st order only inv” message.

    So to me that would show that it is currently looking at all my rows together as one big wad of information.
    So how would you tell it to look at each row by itself?

    Thanks for any help you could provide!

LEAVE A REPLY

Please enter your comment!
Please enter your name here