Comments on: Using logical functions in Excel: AND, OR, XOR and NOT

Last week we tapped into the insight of Excel logical operators that are used to compare data in different cells. Today, you will see how to extend the use of logical operators and construct more elaborate logical tests to perform more complex calculations and more powerful data analysis. Excel logical functions such as AND, OR, XOR and NOT will help you in doing this. Continue reading →

Comments page 2. Total comments: 299

  1. I WANT TO KNOW WHICH FUNCTION CAN BE USED TO GET GET THE VALUE OF THE SAME IRRESPECTIVE OF WHETHER ITS PLUS OR MINUS.
    EG IF A CELL SHOWS -12 OR 12 , I SHOULD GET THE ANSWER AS 12, SO I CAN USE IT IN OTHER WORKING .

  2. I'm stuck and hope someone can help. =IF(H11"",TEXT(H1,"mm/dd")&"–"&TEXT(H11,"MM/DD/Y"),"") returns the correct answer but I want to add that if H11 is blank then read another cell. Possible?

    1. Hello!
      If I understand the problem correctly, replace "" in the formula with the cell address. For example:

      =IF(H11 < > "",TEXT(H1,"mm/dd")&"–"&TEXT(H11,"MM/DD/Y"),G1)

      I hope my advice will help you solve your task.

  3. What if I want a formula to total how many students are new and belong to specific rep
    for example it would count all item in D3-D15 that equal humberto and also E3-E15 that equal red. So the total i need has to meet both criterias from both columns

  4. I am attempting to make this calculation work any ideas:

    1. Sorry about that. if cell G2 contains "*lab*" then divide I2/50 if not then divide I2/20. thanks

      1. Hi,
        Use the following formula:

        =IF(ISNUMBER(SEARCH("lab",G2,1)), I2/50 , I2/20)

        You can learn more about SEARCH function in Excel in this article on our blog.

        I hope this will help.

      2. I have tried the following but have not been able to get what I need: =TRUNC(IF(ISERROR(MATCH("*lab*",G2,)),I2/50,I2/20)) as well as =TRUNC(IF(G2="*lab*",I2/50,I2/20))

  5. Hello,

    Please advise a formula to automatically add a number for every 0.5:
    Example.
    0.5 - 2000
    1.0 - 2500
    1.5 - 3000
    2.0 - 3500

    Each additional 0.5 - 500

    How to insert this in IF/AND statement

    Regards

    1. In D1, type 0.5 - 2000
      Then in D2, type the formula
      =TEXT(LEFT(D1, FIND(" - ",D1)-1)+0.5,"0.0") & " - " & TEXT(MID(D1,FIND(" - ",D1)+3,LEN(D1))+500,"0000")

      Hopefully this would help you!

      If any suggestions or corrections, please let me know, Alexander Trifuntov (Ablebits.com Team)

  6. I have updated multiple If conditions but if the cell value is #N/A then it not replacing, please help some one.
    =IF(D2="Red","First",IF(D2="Green","Second",IF(D2="#N/A","Third","")))

    1. Hello!
      You cannot write an error to a formula condition.
      Use the formula

      =IF(D2="Red","First",IF(D2="Green","Second",IF(ISERROR(D2),"Third","")))

      I hope I answered your question.

  7. I want to know what to do if I want to multiply a value only if it's present.

    Like in a store , different type of scrolls are there and I want to multiply the scrolls value with 100 if it's present..what should be the columns and the function?

    Please help someone..

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(A1<>"",A1*100,"")

      I hope it’ll be helpful.

  8. Hi everyone!
    Can you check my answer for this one? I am not sure of my answer hehe. Thank you in advance for helping me! Sorry for using your time. Stay safe and wash your hands!

    Determine whether the total earning of Ms. Chan is not equal to Mr. Tan’s total earning.
    Formula: =AND(H3H7)
    Output: TRUE

    I am not sure if I will use AND,OR ,or NOT function. Can you give me a tip when to use them? Thank you so much in advance!

    1. its me again!
      I just want to edit my comment.

      it should be: =AND(H3H7)

      1. Hello!
        I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
        You only have one condition. Therefore, you don't need to use AND.

        =IF(H3 >= H7,TRUE,FALSE)

        I hope it’ll be helpful.

        1. May I suggest a simpler solution?

          =NOT(H3=H7)

        2. Hi Sir Alexander!
          Thank you for answering my question! Thank you so much for helping me and it helped a lot.
          I hope you stay safe amidst this pandemic that is happening throughout the world. Stay healthy and wash your hands!

          1. Can you help me in this one?
            How: determine if the number
            is zero, odd and even.
            By using the MOD(to get the remainder) with IF function
            Thank you in advance for helping me!!

      2. its me again!
        I can't put any symbols lol
        =AND(H3H7)

        if the symbols disappeared on the comment, that should be the symbol of less than and greater than. Thank you! Please help me

  9. I need some help. Is there a function I can use to solve my problem. I have a column, that has cells that contain text with commas...below are a few examples:
    Z28 = AREA, CONS, DEX
    Z29 = CTM, MSC, AREA, CONS, DEX
    Z30 = AREA, CONS, DEX,LHC

    I am trying to solve for:
    - if any cell in Z column contains any, all, or some of the following: AREA, CONS, DEX, CTM or MSC return value "Need 1"
    - if any cell in Z column contains LHC return value "Need 2"
    - If any cell contains LHC and any, all, or some of the following: AREA, CONS, DEX, CTM or MSC return value "Need 1 & 2"

    So for example:
    Z28 = AREA, CONS, DEX; value returned would be "Need 1"
    Z29 = CTM, MSC, AREA, CONS, DEX; value returned would be "Need 1"
    Z30 = AREA, CONS, DEX,LHC; value returned would be "Need 1 & 2"

    Does this make sense?

    1. Hello!
      You can use IF and AND formula:

      =IF(AND(SUM(IFERROR((FIND({"AREA","CONS","DEX","CTM","MSC"},Z28,1)),0))>0,IFERROR(FIND("LHC",Z28,1),0)>0),"Need 1&2", IF( AND(SUM(IFERROR((FIND({"AREA","CONS","DEX","CTM","MSC"},Z28,1)),0))>0,IFERROR(FIND("LHC",Z28,1),0)=0),"Need 1",
      IF(AND(SUM(IFERROR((FIND({"AREA","CONS","DEX","CTM","MSC"},Z28,1)),0))=0,IFERROR(FIND("LHC",Z28,1),0)>0),"Need 2","")))

      Simpler and shorter formula with CHOOSE function:

      =CHOOSE(IF(SUM(IFERROR((FIND({"AREA","CONS","DEX","CTM","MSC"},Z28,1)),0))>0,1,0)+IF(IFERROR(FIND("LHC",Z28,1),0)>0,2,0),"Need 1","Need 2","Need 1&2")

      I hope my advice will help you solve your task.

  10. Using excel formulae, find out the following
    1. How many have neither registered nor completed any of the 3 courses?
    2. How many have registered or trained in atleast 2 of the 3 courses?
    3. How many have not been trained in any of the 3 yet?

    Name SQL SAS Excel R - Registered for training (training not done yet)
    Prakash R T T - Trained
    Rahul R Blank - Neither
    Rajiv T
    Priya R T
    Amit T
    Karthik R
    Shobha R
    Prateek
    Payal R
    Prashant T
    Anil R
    Swaroop T
    Tejas R
    Raghav T
    Sanjeev T
    Madhu R
    Murali
    Aravind T
    Balaji R
    Mukundan T
    Shweta R
    Anusha T T
    Ravi R
    Rahul T
    Ramya
    Shriram R
    Sairam T T
    Srinivasan R
    Trisha R
    Paul T
    Mani R
    Venkat R
    Abhishek T
    Chanakya T R
    Daniel R
    Rishi R
    Vikram R T
    Prabhu R
    Priya T
    Mahesh R
    Raj
    Suresh R
    Arun R
    Jacob T
    Malini
    Mehul R
    Rajeev
    Sachin T R
    Praphul
    Diego R T

  11. Please assist me to correct this formula....
    =IF(OR(C5:O5=0),"Open","Close")

    1. Hello!
      You cannot use a range in the conditions of IF, OR, AND functions. Therefore, each cell must be written separately:

      =IF(OR(C5=0,D5=0,E5=0,..........),”Open”,”Close”)

      1. Hello Alex..

        Thanks for your reply..

        I got it when tried with the separate cell. Again thanks for reply.

  12. Please assist correct this formula

    =,IF(H17<=$D$17*74%,"Unacceptable",IF($D$17*74%<H17<$D$17*95%,"Below Expectations",IF($D$17*95%<H17<$D$17*100%,"Competent",IF($D$17*101%<H17=$D$17*110%,"Outstanding",""))))))

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(H17<=$D$17*74%,"Unacceptable", IF($D$17*74%<H17<$D$17*95%,"Below Expectations", IF($D$17*95%<H17<$D$17*100%,"Competent", IF($D$17*101%<H17=$D$17*110%,"Outstanding",""))))

      Unfortunately, you did not write what data is used and what result should be obtained.

  13. The very last one

    I have a row of an approx 200 empty cells and I want to assign a number (1) at a defined interval (10.26)
    The formula I'm working on is the following
    IF (10.26-(C1+R1) less equal to 0.49; 1, 0)
    which is working fine till I get to the cell 10
    then I would like to SUM 10.26 + 10.26 so when I get to the cell 21 the next formula will be like
    IF (20.52-(C21+R1) more equal to 0.49; 1, 0)
    My question is: there is a way to nest the two functions and make the SUM work dinamically
    maybe using a different formula altogether?

    1. I think I was able to nest the two functions somehow

      =--OR(OR(AND(10,26-(C1+R1)"less" 1*1;10,26-(C1+R1)"greater"0,49*1);
      10,26-(C1+R1)"less equal"0,49*0);
      OR(10,26-(C1+R1)"less equal"0,49*1;
      (AND(10,26-(C1+R1)"less"1*0;10,26-(C1+R1)"greater"0,49*0))))

      still I can't get around to make the sum (10.26+10.26) incremental

  14. how to calculate for 3 of more arguments?
    below is what I mean:
    Grater or equator 100 =excellent
    Between 50 to 90 =very good
    Less than 40 =fail

    1. Hello!
      The formula below will do the trick for you:

      =IF(A1>=100,"excellent",IF(AND(A1>=50,A1<=90),"very good",IF(A1<40,"fail","")))

      I hope this will help.

  15. I have a more complicated logic function than I can work out. I am assessing building capacity and need to identify a building as 'under capacity' (where room capacity is greater than customer numbers), 'extend hours' (where room capacity is less than customer numbers, but where opening hours are equal to or less than 37.5 hours per week) and 'exceeds capacity' (where room capacity is less than customer numbers, but where opening hours are more than 37.5 hours per week OR where the room capacity is identified as 'zero').

    Please help!

    1. Hello Phil!
      If I understand your task correctly, the following formula should work for you:

      =IF(F1 > F2,"under capacity",IF(F3 < 37.5,"extend hours","zero"))

      where
      F1 - room capacity
      F2 - customer numbers
      F3 - opening hours

  16. Need result as below
    1. If value is > or = 0 then result will be= 80,
    2. If value is <0 to -03 then result will be= 70,
    3. if value is -04 to -07 then result will be= 60
    4. if value is -08 to -15 then result will be= 50

    -Advance thanks

    1. Hello!
      There is no need to use the AND or OR function.
      The formula below will do the trick for you:

      =IF(A1>=0,80,IF(A1>-3,70, IF(A1>-7,60,IF(A1>-15,50,"" ) )) )

      1. Hi I have a similar problem to the one above but I need to make the values specific to individual suppliers
        e.g IF "ACE" is the supplier but I need to specify tariffs for different pipe sizes eg <20, 20-25,40-45,50-63 etc but different suppliers have different names, size ranges and tariffs how can I write this so the correct tariff is selected for each pipe size in each supplier?
        I would greatly appreciate some help with this!
        Thanks in advance

        1. Hello!
          You need to search by two criteria - the name of the supplier and the size of the pipe. Perhaps this article on searching by several criteria using the INDEX + MATCH functions will be useful
          I hope this will help, otherwise please do not hesitate to contact me anytime.

          1. Fantastic, thank you very much. I will look at the article just now. I may be back for more help....
            Kind regards
            Leigh

  17. Hy! Help me please with the following two tasks. Thank you very much in advance!
    1. Having a personal ID number starting 1 or 5 for boys and 2 or 6 for girls, I must write in a column if that person is a girl or a boy. I tried to combine IF and XOR functions but didn`t work for me.
    2. how to transform the numbers like 6,05 into text in this format: (six,05%)

    Congratulations for what you are doing in helping us!

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =CHOOSE(--LEFT(A1,1),"Boy","Girl", "","","Boy","Girl","","","")

      and

      =CHOOSE(--LEFT(A6,1),"one","two","three", "four","five","six","seven", "eight","nine")& MID(A6,SEARCH(",",A6,1),10)&"%"

      I hope this will help

  18. Help. I have a working formula like this, OR(D9="X", E9="55",D9="B"), in the data validation. How can I modify this in a way that when D9="B" then only "55" value is allowed in E9? meanwhile allowing any values on E9 when D9="X"?
    Thank you in advance.

    1. Hello Miles!
      If I understand your task correctly, the following formula should work for you:

      OR(AND(E9="55",D9="B"),D9="X")

      I hope it’ll be helpful.

  19. CURR. T. INV. VALUE VALUE CONVERTED IN AED
    USD 11,545.00 (CURRENCY(1)=VALUE IN AED) * INV VALUE = TOTAL CONVERSION VALUE
    EUR 1,199.00
    KWD 2,000.00
    AED 5,000.00
    QAT 12,000.00
    GBP 1,500.00

    USD 3.674
    EUR 3.99
    KWD 11.9
    AED 1
    QAT 1.01
    GBP 4.59

    Can anyone help me with the formula for the above.
    If I enter the currency whether it's Eur or USD the amount will be converted automatically and whatever the conversion is, it will be multiplied by Column 2 (Total Value) and the total conversion will be on column 3.

    Appreciate you help.

    1. Hello Jane!
      To show the rate for a necessary currency in cell C2, you can use this formula:

      =VLOOKUP(A2,$A$10:$B$15,2,0)

      $A$10:$B$15 is your table with the currency rate, A2 is a cell where the currency is specified (USD in your case).
      Please read more about the VLOOKUP function here.
      If you have any additional questions, I will be happy to answer them.

  20. =IF(AND(E3<6,OR(TIME(11,45,0)<F3<TIME(12,0,0),TIME(18,45,0)<F3<TIME(19,30,0))),"early bird", "")

    write a logical function to find out whether a customer is an early bird. (refer to the 'Early Bird' criteria stated above) In your function, you will have to reference Column E values (day of week) and Column F (time of order) to determine whether customers are early birds. Think about how you will reference the early bird criteria.
    E3= weekday number (1-7)
    f3 time 7:12pm
    Early Bird criteria - Customers who want to avail of an Early Bird discount must place their orders on weekdays between 11:45 a.m. - 12:00 p.m. (for lunch) or 6:45 p.m. - 7:30 p.m. (for dinner).

    can somebody help,

    1. Hello Neha!
      If I understand your task correctly, the following formulas should work for you:

      =IF(AND(E3 < 6,OR(AND(TIME(11,45,0) < F3, F3 < TIME(12,0,0)), AND(TIME(18,45,0) < F3,F3 < TIME(19,30,0)))), "early bird", "")

      Hope you’ll find this information helpful.

  21. Does RIGHT,LEN,FALSE belong to the logical category

    1. Hi!

      FALSE is categorized under Logical functions; RIGHT and LEN belong to the category of Text functions.

  22. If cell B2 = 43830 (which is December 31, 2019 and assuming that cell B2 does say December 31, 2019) and if (-5 + 2 +7)>0 which is also true, how do I write this to make it work in an IF/AND statement?

    Any help is Greatly appreciated!

    1. =if(and(b2=date(2019,12,31),(-5+2+7)>0),"value if true","value if false")

  23. Hi Team,

    I need assistance, currently the formula below, works Ok for Text "2019" value in particular column, We converting mmm,dd, yyyy to MM/dd/yyyy. Since the file now contains some "2020" value in text the formula does not convert. I need the Formula to work for Text "2019" or "2020" as well. Please shed some light!!!

    1. =(TEXT(SUBSTITUTE(report!B2,"2019,","2019"),"mm/dd/YYYY"))

  24. one day i'll understand

    1. same bro same ajwndoiwejfie

  25. Hey so im trying to pick up numbers in negative in the tabel

    Cell1 Cell2 (this is in a tabel)
    A 2
    D -1
    A -3
    A 1
    D 3
    E 2
    A -1

    If i want to only pick up the sum of all "A" that is negative numbers ignoring the positives and the other letters, anyone know how to do that? (the result should be -4 in this case)

    1. I have tried this one but dosent work.
      =SUM.IF(Cell1,"A",IF(Cell2<0,Cell2;0))

  26. hi,
    What formula could i use if i don't want a number to go over 7.5...If a number is lower, is displays the actual number but if it's higher it shows only 7.5
    THank you,

    1. HI..

      =IF(H19<7.5,H19,7.5)

  27. How to write a formula for this
    10 - 64 Reading below grade level
    65- 81 On Level
    82 - 100 Reading above grade level

    Thank you so much

    1. Hello, Arcita,
      Please try the following formula:

      =IF(A1<=64, "Reading below grade level", IF(A1<=81, "On Level", IF(A1<=100, "Reading above grade level")))

      You can learn more about Excel Nested IF in Excel in this article on our blog.

      Hope you'll find this information helpful.

  28. COMPLETE SUCCESS

    I used the following formulas to test and see if the date format translates to a number, which it did
    =LEFT(MONTH(D13),2)

    Then expanded it to check for output for two months - it worked
    =IF(LEFT(MONTH(D11),2)="5","May",IF(LEFT(MONTH(D11),2)="6","Jun",""))

    FORMULA WHICH WORKED (expanded it to 12 months, put Jan as 01)
    ------------------------------
    =IF(LEFT(MONTH(D10),2)="01","Jan",IF(LEFT(MONTH(D10),2)="2","Feb",IF(LEFT(MONTH(D10),2)="3","Mar",IF(LEFT(MONTH(D10),2)="4","Apr",IF(LEFT(MONTH(D10),2)="5","May",IF(LEFT(MONTH(D10),2)="6","Jun",IF(LEFT(MONTH(D10),2)="7","Jul",IF(LEFT(MONTH(D10),2)="8","Aug",IF(LEFT(MONTH(D10),2)="9","Sep",IF(LEFT(MONTH(D10),2)="10","Oct",IF(LEFT(MONTH(D10),2)="11","Nov",IF(LEFT(MONTH(D10),2)="12","Dec",""))))))))))))

    1. Used the LEFT(MONTH(CELL_REF),2) to verify the translation of the date to number, THEN expanded it to the following, it worked.

      =IF(LEFT(MONTH(D10),2)="01","Jan",IF(LEFT(MONTH(D10),2)="2","Feb",IF(LEFT(MONTH(D10),2)="3","Mar",IF(LEFT(MONTH(D10),2)="4","Apr",IF(LEFT(MONTH(D10),2)="5","May",IF(LEFT(MONTH(D10),2)="6","Jun",IF(LEFT(MONTH(D10),2)="7","Jul",IF(LEFT(MONTH(D10),2)="8","Aug",IF(LEFT(MONTH(D10),2)="9","Sep",IF(LEFT(MONTH(D10),2)="10","Oct",IF(LEFT(MONTH(D10),2)="11","Nov",IF(LEFT(MONTH(D10),2)="12","Dec",""))))))))))))

      1. Sunny:
        Alright! That's a lot of nested IF's but you figured it out. Congratulations on a good use of the MONTH function.
        Thank you for sharing. I'm sure others will benefit from your work.

  29. If have to add the print the the value in C1.
    I need add the student marks for the following condition A means if student Absent in subject.A1=Theory Marks,B1=Practical Marks,C1=Final marks Means A1+B1=C1
    case-1
    A1=5 B1=10 C1=15
    Case-2
    A1=5 B1="A" C1=05
    Case-2
    A1="A" B1=10 C1=10
    Case-2
    A1="A" B1="A" C1=0

    any body please help to write formula for that.
    Kindly give me formula for that

    1. Anurag:
      If you can simply put a "0" in A or B when student is absent then the formula is very simple. In C1 just enter =A1+B1.
      If you must enter an "A" in a cell when student is absent then put this formula in C1: =IFERROR(IF(A1="A",(0+B1),IF(B1="A",(0+A1),(A1+B1))),"Student Was Absent for Both Subjects")
      If A1 and B1 both hold "A" the IFERROR returns the message, "Student Was Absent for Both Subjects".
      You can change the cell addresses and the message to suit your needs.

  30. Hi :). I have two possible outcomes for my formula - either cell 'J' (if cell E is greater or equal to cell G and less than or equal to cell H) or cell 'K' (if cell E is less than cell G or greater than cell H). here is my formula: =IF(E3>=G3*(AND(E3<=H3)),J3),IF(OR(E3H3))),K3) which returns #VALUE

    Please help :(.

    1. Carl:
      Text and numbers don't mix very well in Excel formulas, so make sure your numbers are actually formatted as numbers.

  31. Hello. how do I write for formula for:
    if K70>=33 A, if less than 33 but more than equal 28 B, if less than 28 C

    ?
    thank you

    1. macaduta,

      what i did understand was
      if there is a value >= to 33 is "A"
      then if there is a value between 28-32 is "B"
      and if there is a value 27),AND(K7032,"A",K70<28,"C")

      1. IFS(AND(AND(K70>27),AND(K7032,"A",K70<28,"C")

    2. Macaduta:
      The logic in your statement needs to be clearer.
      It should read like "If K70 is greater than or equal to 30 then A, etc.
      What is "A", "B" and or "C". Values held in other cells? Partial cell addresses?
      The second condition needs to be clarified. Is this an AND or an OR relation to the first statement?
      After Excel checks these conditions what is it supposed to do? What if none of the conditions are true?

  32. I sell a product that has different names ( up to 700) is there a formula that I can use that can count how many times I have sold a product with one name by comparing the sales data against the list of names ?

    1. Maria:
      Enter SUMPRODUCT in the search field here on AbleBits. I think you'll find the answer to your question.

  33. Is there some form of if statement that has more than 2 logical parts.
    For instance, instead of outputting TRUE or FAlSE, or the equivalent. IT could output any number of different responses.
    To copy your demonstration system;
    IF([logical1], [logical2], … [logical{n}], …, [Response1], [Response2], Response{n})

    1. Sam:
      There are several techniques that work the way you're asking about. Their use depends on the required logic of the situation.
      Nested IF statements are one. They look like this:
      =IF(Q2,"Paid",IF(S2>=TODAY(),"Not due","Overdue"))
      IF AND statements look like this:
      =IF((AND(D1="eng", B11="bank1")), IBAN1
      IF OR statements look like this:
      =IF( OR( A120), "Less than 10 or more than 20")
      Then there are COUNTIF, SUMIF, IFS and others most of which are explained here in the various ABLEBITS articles. Just enter one of the above titles in the Search box and begin to learn about IF statements.

  34. please help me for below I need formula for below things

    1 TO 15000 500
    15001 TO 100000 1000
    100001 TO 250000 2500
    250001 TO 1000000 5000
    10000001 AND MORE -0.60%

    1. Ramanan:
      I think this is what you want.
      Where the value you want to check is in cell H16
      =IF(H16>1000000,(H16*-0.06),IF(H16>250000,5000,IF(H16>100000,2500,IF(H16>15000,1000,IF(H16>1,500)))))

  35. hi
    i need a formula for. .

    if year 2015 then get interest 8%
    &
    if year 2016 then get interest 7.9% if year 2017 then get interest 7.8%

    1. Your question is unclear, but I'm thinking you should somehow separate the data by year and then apply the interest. Otherwise, how will Excel know what year it is?

  36. HOW TO MERGE BOTH FORMULA IN A CELL

    =IF(E53=12,"BENF-CPI",IF(E53=1,IF(OR(F53="DN12",F53="DB12"),"CPI-NSDL POA",IF(OR(F53="DN22",F53="DB21"),"CPI-CDSL POA"))))

    =IF(E53=11,IF(AND(J53="02-12047200-00100398",AG53="02-12047200-00100383"),"IST",IF(J53=AG53,"IST","P2P")))

    1. Create a whats app group for Excel where we can ask any Questions and reply to all that will be great.

  37. =IF(AND(D63>=4000,D63<=7000),"1400","2200")

    I Have Used This Formula In excel Sheet, But In This If The Valuve Is Less Then 4000, So Then Then the Folmula Value Show Is "0"

    Can U Guide What The Formula.

    1. Hello, Farooq,

      If I understand your task correctly, you should use the nested IF functions and your formula should look as follows:

      =IF(D63>7000,2200,IF(D63>=4000,1400,0))

      Hope this is what you need.

  38. How do I use an AND function To return a value other than True or False,For example: If cell A2 is greater than 20 and B2 is greater than 10 it should return Pass not true. How can I do that?

    1. Hello, Omar,

      Please try the following formula:

      =IF(AND(A2>20,B2>10),"Pass","")

      Hope this will help you.

  39. I want a cell to check another cell for the list below and add the appropriate number if it is found. Here is list of items:

    TS=2.50
    SL=2.50
    W SERV=3.00
    G SERV=2.50
    S SERV=5.00

    Please help and thank you

    1. I figured it out:

      =IF(O2="TS","2.5", IF(O2="SL","2.5",IF(O2="W SERV","3","?")))

      Thanks

  40. I want formula for date.
    I have to find out the month of increment from joining date of an employee on excel sheet.
    Such as if date of joining is up to 15th the date of increment will be same month and if date of joining is after 15th then date of increment will be next month.
    Example, Date of joining of any employee is 12/03/2017 then increment month will be March, whereas date of joining is 16/03/2017 then increment month will be April.

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =TEXT(IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),A1),"mmmm")

      Hope this will help.

  41. Guy's please help to fixed the formula in below condition. I have applied formula =(IF(O2<10,"<10",IF(O2<20,"10-20 Min",IF(O2<30,"20-30 Min",IF(O2<60,"30-60 Min",IF(O2=120,">120 Min"," "))))))) but result is not proper.
    Time Condition
    2:11:59 120 Mins

    1. Hello,
      If I understand your task correctly, please try the following formula:

      =IF((VALUE(O2)*24*60)<10,"<10",IF((VALUE(O2)*24*60)<20,"10-20 Min",IF((VALUE(O2)*24*60)<30,"20-30 Min",IF((VALUE(O2)*24*60)<60,"30-60 Min",IF((VALUE(O2)*24*60)<120,"60-120 Min",IF((VALUE(O2)*24*60)>=120,">120 Min"," "))))))

      Hope it will help you.

  42. Hi, i need a formula to calculate a range table of age like 15-34, 35-44, 45-49 and they have to match with 2 different class, with 2 different amounts link to each class, if the amount in column A is <=34 and column B is "2" the amount should be the amount in cell G2 or if Column B is "1" it should be the amount in cell G3. hope you can assist.

    1. Hello,

      If I understand your task correctly, you need 2 different formulas for cells G2 and G3:

      For cell G2:
      =IF(B1=2,IF(AND(A1>=15,A1<=34),"15-34",IF(AND(A1>34,A1<=44),"35-44",IF(AND(A1>44,A1<=49),"45-49",""))),"")

      For cell G3:
      =IF(B1=1,IF(AND(A1>=15,A1<=34),"15-34",IF(AND(A1>34,A1<=44),"35-44",IF(AND(A1>44,A1<=49),"45-49",""))),"")

      Hope this will help you!

  43. I have three colms as
    A1 (City), B1 (Quantity), C1 (Amount)

    If Colm A1 is Karachi, I want then quantity (B1) should multiply with 15 in Amount colm (C1)

    If Colm A1 is Lahore, I want then quantity (B1) should multiply with 15 in Amount colm (C1)

    1. Hello, Azam,

      Please try the following formula:

      =IF(A1="Karachi", B1*15,IF(A1="Lahore", B1*15,""))

      Hope it will help you.

  44. I need a formula to calculate a variable sales commision. if I sell 8 or less, I get $x. if I sell 9-15 units, ill get $y. with a variability of 5 different pay rates for commission.
    Thanks

    1. Hello, Patrick,

      Please try the following formula:

      =IFS(A1<=8,"$x",A1<=15,"$y",A1<=20,"$z",A1<=25,"$v",A1<=30,"$w")

      Hope it will help you.

  45. I have data like this

    123415678
    432128765
    001218090

    These numbers represents ID numbers If the 5th number is 1 then the ID belongs to a male if its 2 then the ID belongs to a female

    1. Hello ,

      Go the next column, and use this formula (=Left(A1,5))
      in Column C , put this formula ( =Right(C1,1)

      Codes Left Coding Right Coding Sex
      123415678 """=LEFT(A2,5) """=RIGHT(B2,1) ''''=IF(C2="2","Female","Male")
      432128765 43212 2 Female
      301218090 30121 1 Male
      214510025 21451 1 Male
      301228090 30122 2 Female
      301228090 30122 2 Female
      301218090 30121 1 Male

    2. Hello ,

      Go the next column, and use this formula (=Left(A1,5))
      in Column C , put this formula ( =Right(C1,1)

      Codes Left Coding Right Coding
      123415678 """=LEFT(A2,5) """=RIGHT(B2,1)
      432128765 43212 2
      301218090 30121 1
      214510025 21451 1
      301228090 30122 2
      301228090 30122 2
      301218090 30121 1

  46. Hi,
    please solve this question.
    If Mr.A has 125 coins then he will get "1 Apple" and if Mr.A has 170 coins then he will get "1 Apple" for 125 coins and on balance 45 coins, he will get "1 Lemon" on every 25 coins.

    Please convert the same in to logical formula in excel.

    1. LET CELL A1 have the coins MR. A HAS, THEN copy THE following EXPRESSION
      =CONCATENATE("IF MR.A HAS 125 COINS, THE HE WILL GET 1 APPLE, AND IF MR. A HAS"," ",+A1," ","COINS"," ","THEN HE WILL GET"," ",ROUNDDOWN(+A1/125,0)," ","APPLE(S)"," ","AND ON BALANCE"," ",A1-(ROUNDDOWN(+A1/125,0)*125)," ","COINS, HE WILL GET"," ",ROUNDDOWN(+(A1-(+ROUNDDOWN(+A1/125,0)*125))/45,0)," ","LEMON ON EVERY 25 COINS")

    2. =IF(G14="Mr.A",(IFS(H14=125,"1 Apple",H14=170,"1 Apple, 1 Lemon"))," ")

      Hope this will help you

  47. =IF(AND(V17="B",V18<="470"),"127","0"),IF(AND(V17="BC",V18<="700"),"112","127"),IF(AND(V17="C",V18<="420"),"0","112")

    I AM USING THIS FORMULA BUT THE RESULT IS " #VALUE! "
    PLEASE GIVE ME SALUTATION.

    1. Muhammad, Possible Solution
      1) To make this easier, separate the formulas and evaluate each one separately. I noticed that there is overlapping logic. If formula #1 is False you get a -0- and if formula #3 is True you get a -0-, and the same thing with 127 and 112 across all 3 formulas. How would you know which values gave you the answer? Also, what if the input values do not match your expected answers, do you evaluate them? And your attempt to nest the IF statements is wrong.
      Nesting as below will work but you will still only get 112, 127 or -0-.
      =IF(AND(B1="B",B2<="470"),"127",IF(AND(B1="BC",B2<="700"),"112","0"))
      Hope this gives you some insight.

  48. Hi
    I have 9 sheets in the same workbook variously populated with 0 and 1. In a separate sheet I want to fill a cell with "1" if 1 occurs in the same cell in any of the 9 sheets, or "0" if it doesn't occur at all. I have tried using the following:

    =IF(OR('Training NetworkB'!C5="1",KnowledgeSharingBtxt!C5="1",'Encouragement NetworkB'!C5="1",'Organisation NetworkB'!C5="1",'Monitoring NetworkB'!C5="1",'Networking NetB'!C5="1",'Labour sharing NetworkB'!C5="1",'Conflict NetworkB'!C5="1",'Phys_Finan Capital NetB'!C5="1"),"1","0")

    It returns a 0 in all cells, even if a 1 occurs. Can you help at all?
    Many thanks in advance

    1. Hi,
      It looks to me like you have a space after all your 1's that are in your speech marks. If the cells on the other spreadsheets just contain a "1" and not a "1 " then the formula won't recognise the 1 because it will be looking for a 1 and a space.

    2. Hi,
      It looks to me like you have a space after all your 1's that are in your speech marks. If the cells on the other spreadsheets just contain a "1" and not a "1 " then the formula won't recognise the 1 because it will be looking for a 1 and a space.

  49. hi
    i need a formula for. .

    if found 0152 then get 18
    &
    if found 6305 then get 5

    1. Hi

      Thank you for contacting us.

      Please try the following formula:

      =IF(A1 = 152, 18, IF(A1=6305, 5, " "))

      I hope this helps. Please let me know if you have any other questions or difficulties.

      1. Hi Mary ,
        can you help me in macro how to record macro ?

        1. Hi Amit,

          First you have to save as your Excel sheet to .xlsm. after this step you have to go view ribbon and choose Macro option. There you can find macro recording.

          Start recording and work in the same sheet without using mouse, it works perfectly. Because if you use mouse Macro may not write program as per your clicks.

          Once you stop recording you just give an non-calculated report which you did earlier and run macro from same macro option.

          Regards,

          Mohan

  50. Hi Svetlana
    I need a formula for comparison of numbers with text
    E.g.
    If x is less than or equal to 1600 them yes or if x is not available then "not avl"
    But if X is more than 1600 then wow but if x is not avl then not avl

    1. =IF(OR(B6="",B6<=1),"Not Available",IF(B6<=1600,"Yes","Wow"))

    2. =IF(C18>1600,"Wow",IF(C18>0,"yes",Not Available))

      1. =IF(C18>1600,"Wow",IF(C18>0,"yes","Not Available"))

        1. Hi Please use this.
          =IF(OR(C24="",),"Not Available",IF(C24>=1600,"Yes","Wow"))

    3. '=IF(+C6=0,"not avl",IF(+C61600,"wow")))

    4. Hi 201, please use below Formula

      =IFERROR(IF(VLOOKUP(D1,A1:B13,2,0)>1600,"Wow",IF(VLOOKUP(D1,$A$1:$B$13,2,0)<=1600,"Yes","0")),"Not Avl")

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)