Comments on: How to use IF function in Excel: examples for text, numbers, dates, blanks

IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading →

Comments page 9. Total comments: 3008

  1. I'm having trouble using the IF logic statement- here it is =IF(C2=7592.00,1251.80,0. What Im trying to say is if the value in C2 is (7592.00) if true input 1251.80, if false input 0.
    C2 actually does not contain 7592.00. Its referencing and reading a value from another cell in a different sheet. ='DRP Implementation'!N127 Result is Im not getting the result I want 1251.80 if 7592.00 is present. Is it because C2 is a ref cell and not a discrete numerical value? How would you correct this?

    1. Hello Larry!
      First off, please check your Excel settings: Excel - File - Options - Advanced - Update links to other documents. Also, please go to Data->Edit Links and make sure the autoupdate for each link is enabled.
      Check the automatic calculation in your Excel. Go to File -> Options -> Formulas -> Calculation Options and switch to Automatic option there.
      Note! In this case, you'll need to hit Shift+F9 to recalculate the formulas in any workbook you're working with.
      Hope it'll help, otherwise please do not hesitate to contact me back.

  2. Hi! I am trying to create a complicated If Then statement and haven't had success. Could you help?
    If the text in a cell says "Participant #1", then I want it to tell me the name which is found in cell C2. But if the text says "Participant #2" then I want it to tell the name in cell D2. And so on through to Participant #4. If there is no text then leave it blank.

    =IF("Participant #1",E2, IF("Participant #2",F2, IF("Participant #3",G2, IF("Participant #4",H2,"")))
    This is what I had, but is saying I cannot coerce my formula into a boolean equation... what on earth?

    Thank you so much for help!

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

      =IF(B1="Participant #1",F2, IF(B1="Participant #2",G2, IF(B1="Participant #3",H2, IF(B1="Participant #4",I2,""))))

      Hope this is what you need.

      1. I ended up having to use this sequence, which I got from an excel whiz that I happened to connect with through a friend:

        =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2, "Participant #1", $E2 & " " & $C2), "Participant #2", $F2 & " " & $C2), "Participant #3", $G2 & " " & $C2), "Participant #4", $H2 & " " & $C2)

        K2 is the cell of the activity participants, E2 / F2, etc. are the Participant First Names, and C2 is the last name

  3. Hi,
    Wondering if someone can please help me with a formula?
    If column G contains the text "Yes", then I want column I to generate a sequential number, starting at 001. If G contains "No", then no number is to be generated and the cell will remain blank, and the next sequential number (002) will appear the next time column G contains "Yes"
    I understand the IF function, but am struggling with the numbering part.
    So far I have =IF(G:G="Yes","*","")- I need to fill in the * part!
    Thanks :)

    1. Hello Amy!
      Please try the following formula:

      =IF(G1="Yes",COUNTIF($G$1:G1,"Yes"),"")

      After that you can copy this formula down along the column.
      Please go to Format Cells, choose Number -> Custom Format and set
      00#
      Or instead of the number in the cell, you can write text and not use custom format:

      =IF(G1="Yes",TEXT(COUNTIF($G$1:G1,"Yes"),"00#"),"")

      I hope this will help

  4. hello,
    I wanted to put a date in one cell then it will populate in other cell. for example if I put a date in A1, whatever I put in A1 should populate in A3, A5, and A7. If I put a date in A3 then it will populate in A1, A5, and A7. If I put a date in A5 then it will populate in A1, A3, and A7. If I put a date in A7 then it will populate in A1, A3, and A5. Hope you can help me with this. Thank you

    1. Hello Winterstory!
      Please note that a cell may contain either a value or a formula. Since it is unknown which of your cells should be filled with data, which should be auto-populated by the formula, your task can be accomplished with the help of VBA only.

  5. I select a date from cell Q33 ( 04-May-2020), and in cell R33 add some duration to it from either P34, or Q34 and or R34 (Year, month and day respectively). In this case 0 years (P34), 0 months (Q34), 200 days (R34) which gives me 16-Nov-20 in R33. The date so arrived at in R33 should not be greater than year end date i.e, 31-Mar-21. If it exceeds it should return FALSE in R33, if not it should return the correct date.
    I tried following formula, but it does not work. Please help.

    =IF(DATE(YEAR(Q33)+P34,MONTH(Q33)+Q34,DAY(Q33)+R34)>(DATE(YEAR(Q33)+(Q33>DATE(YEAR(Q33),3,31)),3,31),"FALSE",(DATE(YEAR(Q33)+P34,MONTH(Q33)+Q34,DAY(Q33)+R34))

    Thanks in anticipation.

    1. Hello Mahesh!
      The formula you wrote does not work. I don’t quite understand why you have the end of the year - March 31. May 4 plus 200 days is November 20, and not November 16, as you have.
      I suggest using the formula

      =IF(DATE(YEAR(Q33)+P34, MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34 > R33,Q33,DATE(YEAR(Q33)+P34,MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34)

      where R33 is the end date of the year.

      1. Hello Alexander!
        This is used for calculating interest on investment starting and ending in same Fin. Year (FY).
        We have FY starting 1 April and ending 31 March e.g., 1-Apr-20 to 31-Mar-21.
        Thus tenure can be max 1 year or less than 12 months or less than 365 days or in months and days.
        Starting date (Q33) is always =TODAY() and cell is locked, but end date (R33) can not be more than 31-March of FY. Thus if tenure input (P34 Y, Q34 M, R34 D) given is such that end date goes beyond 31-March cell (R33) must return "FALSE".
        First part of formula has to add tenure to start date, > (THEN) part of formula has to find FY end date using start date, and (else) part has to either give end date or if more than FY end date return FALSE.
        Tried your suggestion, but it seeks >#REF! as follows: =IF(DATE(YEAR(Q33)+P34, MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34>#REF!,Q33,DATE(YEAR(Q33)+P34,MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34). This is because formula is being built in cell R33 so it cannot have input >R33
        Kindly suggest.
        Thanks.

        1. Hello Mahesh!
          I copied the formula from a blog in Excel and made sure that it works. Perhaps the error #REF! caused by incorrect copying. To make the formula fit your needs, use

          =IF((EDATE(Q33,Q34+P34*12)+R34) > DATE(2021,3,31),FALSE,EDATE(Q33,Q34+P34*12)+R34)

          1. Hello Alexander.
            This formula works.
            But, I would request you to help make year change automatic i.e., there should be no need to change (2021,3,31) with every FY change in >DATE(2021,3,31) e.g. if date next year is 09-May-2021 it should become >DATE(2022,3,31). Please suggest replacement of Date(2021,3,31) with a formula to achieve it.
            Thanks.

  6. Good day,
    If a cell has no amount - in need to make it zero.
    I use to use this formula and now it is not working:
    =if(d4"",0,D4)

  7. Hi, I am struggling to set the formula for the following:
    Column B needs to read May if the value in column C is between May 1 and May 31, 2020.

    1. Hi Candice,

      Assuming column C contains dates, you can extract month to column B with this simple formula:
      =TEXT(C1, "mmmm")

    2. Hello Candice!
      Please try the following formula:

      =VLOOKUP(MONTH(C1), {1,"January";2,"February";3,"March";4,"April";5,"May";6, "June";7,"July";8,"August";9,"September";10,"October";11, "November";12,"December"}, 2,0)

      I hope it’ll be helpful.

      1. Hi,

        Similar Question with the dates. I have a source data and I want to get the dates based on cut-off.
        1. Row 1 . May 23 - June 20, 2019. Payments made was June 21 (Column C). I would like my Column D to read July.
        2. Row 2. April 23 - May 22, 2020. Payments made was April 25 (Column C). I would like my Column D to read May.
        Can I use "IFS Function" for this? Thank you in advance.

        1. Hello Philip!
          I cannot recommend you which function to use - IF or IFS, since you did not say anything about the conditions under which the formula will determine the month in column D. If there is one condition, then you can use IF. But this is only an assumption. No information needed to answer.

  8. Hi,

    I am trying to do an IF then formula but having trouble coming up with it. I have 4 different columns that I mark with an X dependent on which patient meets which column. However I want an additional column (if,then column) that will calculate if I have put in X in any of the 4 columns in that row and to mark it by yes if there is an X or no if there is not. Can you assist with this?? Thank you!

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

      =IF(COUNTIF(A1:D1,"X") > 0,"X","")

      I hope it’ll be helpful.

  9. Hi I am trying to create a formula that allows me to quickly match the same data that appears in 2 columns

    so if text in column A has a match with text in column b no matter what row it is displayed in, display OK can you please help?

  10. I need an IF statement that determines the unfilled up cell.
    ex.
    if a number of cells like 05,P5,Q5,R5,S5,T5,U5,V5,W5 which belong to step 1, step 2 and soon are blank I need to specify which step is missing.

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

      =SORT(IF($O$5:$W$5="", COLUMN($O$5:$W$5)-15,""), ,1,TRUE)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  11. my question is that,
    my formula is accurate, but i want that if in my selected cell no any data in formula cell show that " - " character,
    mostly when your selected cell not have data , your formula cell show that " #value! "
    I want that show as like " - ", with out effected the formula,
    please help me in this task.

  12. I am trying to use =IF... If the text in C48 equals a picklist (five different products) I would like cell E48 to equal the dollar value associated with the picklist name.

      1. Thanks

  13. Hi I would like to create a formula that gives out 100% if "Text1" or "Text2" but a variable percentage that I put in on my own if "Text3" or "Text4". I have at this point reached this formula which does not seem to work: = IF(OR(A3="Text1";"Text2";);100%;"")
    Excel complains and says that my conditions for OR formula needs to be a number, but my conditions are in text so I dont know what to do, so maybe another forumla similiar to OR but you can use text. Maybe some sort of If and Else formula needs to be used here but I dont know how.

    1. Hello Edgar!
      Please try the following formula:

      = IF(OR(A3="Text1",A3="Text2"),100%,"")

      I hope it’ll be helpful.

  14. Hi Svetlana
    Please if I have a formula like this:G1=A1&B1&C1&D1&E1&F1 the value of the cells are in words not numbers.the formula actually gave me the my desired results but how can I give a demacation like " , " in between each words. Secondly, how can I make the results to centralized in the cell.
    Thanks.

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

      =A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1

      or

      =CONCATENATE(A1,"",B1,"",C1,"",D1,"",E1,"",F1)

      To center the text, use the menu Alignment - Center.
      I hope it’ll be helpful.

      1. How to I extract text before the Qty ? suppose I just want to extract "bought" from 1st line
        Bought 300 CODX @ 10.3
        Sold 300 CODX @ 10.5
        Sold Short 300 TWTR @ 24.7
        Bought to Cover 300 TWTR @ 24.857

  15. I am in if assistance. This is what I am attempting to do. I need to calculate the total amount (dollars) that meet delinquency days. For example, Column A is the amount, Column B is the days late, and return the total in other cells based of a time span. "0-29" in Cell A7, "30-59" in Cell A8, and "60-89" in cell A9

    1. Hello Thad!
      Please try the following formula:

      =SUMIFS(A1:A15,B1:B15,">0",B1:B15,"<30")
      =SUMIFS(A1:A15,B1:B15,">=30",B1:B15,"<=59")
      =SUMIFS(A1:A15,B1:B15,">=60",B1:B15,"<=89")
      I hope it’ll be helpful.

  16. I need a simple formula, but cannot seem to figure out the answer. I need an IF statement. If there is text in either C8 or C9 greater than 0, then ((c8+c9)/c5) then show -. So far I have =IF(C8>0,((C8+C9)/C5),"-") which works if I have text in c8, but I cannot figure out how to include c9. I may not always have text in C8 and still need the percentage to calculate if c9 has text too, Thanks

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

      =IF(OR(C8>0,C9>0),(C8+C9)/C5,0)

      1. Hi,
        If you have four categories like some, little, none and regular and you want them to prioritise them then how could you do that using IF function or VLOOKUP function?

  17. Say for eg: IF(A3>A2, "Increase", "Decrease") and i want to fill the background for increase with green and Decrease with red.....Can it be done using IF statement other than conditional formatting option

    1. Hello Shirish!
      If I understand your task right, you want to change the cell background using a formula, not applying Conditional Formatting.
      Unfortunately, an Excel formula cannot format the cell - it can only return a value. Your task can be solved either with Conditional Formatting or VBA.

  18. Can u please help me for following date formula
    =if(A2:A30 01-01-2018 to 31-01-2018,sum(B2:B30)
    how i write please?

    1. Hello Nayon!
      Please try the following formula:

      =SUMPRODUCT(--(A1:A10>=DATE(2018,1,1)), --(A1:A10<=DATE(2018,1,31)), B1:B10)

      Hope you’ll find this information helpful.

      1. Hi Alex,
        Really appreciate your explanations and I wasn't aware of the "--" notations and it is quite handy.

        Thanks for the great work the Ablebits Team does!

  19. why won't this work?
    =IF(A1="Departmental Manager",20000,IF(A1="Departmental Supervisor",18000,IF(A1="General Employee",15000,IF(A1="Cleaner",8000))))

    1. Hello André!
      Please describe your problem in more detail. The formula is bringing back the results of calculations. Thus, it is working. Perhaps, you would like it to work differently. What results would you like to get? How do they differ from the results which your formula is bringing back?

  20. Hi there. I am trying to create a formula based around this information. Let me know if it is possible.
    If D1 is less than 3 than or equal than the value should be 7. If D1 is greater than 3 but less than 7 than the value is 10. If D1 is greater than 7 than the value is 16.

    Thanks

    1. Hello Sarena!
      Please use the following formula

      =IF(D1>3,IF(D1<=7,10,16),7)

      If there is anything else I can help you with, please let me know.

      1. Thank you! I am just starting to work outside of basic formulas.

  21. I am coming with my problem directly. In a particular cell I enter something like this- 20AAACT2520C1ZT. Now the first two digits of this number i.e. '20' represents state code. I want the adjacent cell to automatically recognize the state code and display the name of the state. Is it possible to do so???

    1. Hello Debarun!
      You can extract the first two characters using this formula:
      =LEFT(A1,2)

      After that, you can use this code with the IF function:

      =IF(LEFT(A1,2)="20", [to show if YES], [to show if NO])

      Or use the VLOOKUP function for pulling the state name based on the code. You can find more info about VLOOKUP here: https://www.ablebits.com/office-addins-blog/excel-vlookup-tutorial/
      If there is anything else I can help you with, please let me know.

  22. HI.
    I trying to insert icon to replace formula return ex:IF(B2>=A2,"?",IF,"?" like this?
    If can, please draft the formula.

    1. Hello Ahmad!
      You can see symbols that can be inserted in an Excel cell via Insert –> Symbol. There you can also see a code of a symbol. This code can be used with the CHAR function. For example, =IF(A2>B2,CHAR(74),CHAR(76))
      Please remember that you should apply the Wingdings font to the cell where you insert the formula so the symbols display correctly.
      I hope it’ll be helpful.

  23. Hi Svetlana,

    could you advise any "If" combination to read a range of text and return a specific value?
    Example: there are 10 test results and there is one overall result. If 9 test result are "passed" and 1 is failed then the overall result to be marked "failed". If all 10 test results are "Passed", then the overall result to be marked "Passed".

    Thanks
    Shobert

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

      =IF(COUNTIF(A1:A1,"Failed")>0, "Failed","Passed")

      I hope it’ll be helpful.

  24. =iferror(if(c6="",1,match(c6,holidays,0)),0)
    Using the above code I can't find the answer. I used this program for the holidays on the helper shit, but the holidays are not being shown in attendance sheet. What do I need to do?

    1. Hello Nitin!
      For me to be able to help you better, please specify what the "holidays" named range should contain and what should be in cell C6.
      Thank you.

  25. =IF(B2-C2,"OK" “Wrong amount”) has a syntax error/is incorrect
    What is the correct formula.

    1. Hello Kedevin!
      If I understand your task correctly, the following formula should work for you:
      =IF(B2-C2>0,"OK","Wrong amount")

  26. =IFS(A1 = 5, "Excellent", A1 = 4, “Very Good”, “Good”) has a syntax error/is incorrect
    What is the correct formula.

  27. Hi,
    Please help me with a formula for the following:
    I have 2 columns: HR100 101001
    HR102 101001
    HR103 101001
    Based on the value in the 1st column I want to return the value from the second column. But see, some has the same number. So if I am using Vlookup, if you have the same value will return N/A. Please help me. Thanks

    1. Hello Georgiana!
      If there are some repetitive values in the column you're working with, you should sort it accending beforehand and set the Range_Lookup parameter as 1 or TRUE. Besides, it would be a good idea to place an unary operator (--) so that Excel considers it to be a number, not a text string. Having said that, the formula should look like the one below:
      =VLOOKUP(--F3, $B$1:$D$5, 2, 1)

      Alternatively, there is a combination of INDEX + MATCH functions that may be helpful as well. You may find more information on this topic here.

  28. Hi! I need this formula: =if((C1>0 and <30);A1;0). How to use formula in brackets to compare numbers. I need to be if C1 is between 0 and 30? Thank you!

    1. Hi Nadia,
      Try the formula below
      =IF(AND(C1>0,C1<30),A1,0)

      1. Great. Thank you! :D

  29. Hi I am trying to create a formula in excel for a loan officer. I want it so that commissions are automatically populated based on the type of "lead" it is.

    I have column 'a' filled with the lead type, including either "self-gen", "branch", or "corporate"
    I have column 'b' filled with the loan amount
    I want column 'c' to auto-populate with the commission.
    If a "self-gen" lead, then it would = cell 'b' * .015
    If a "branch" lead, then it would = cell 'b' * .006
    If a "corporate" lead, then is would = cell 'b' * .0035

    Thank you!

    1. Hi Lesley,
      Try the formula below.
      =IF(A1="SELF-GEN",B1*0.015,IF(A1="BRANCH",B1*0.006,IF(A1="CORPORATE",B1*0.0035,"")

  30. Hi,

    What function would I use to say "If there is a value in this range of cells, input "yes", otherwise input nothing"

    1. Hi Sony,

      You can use IF together with the COUNTA function that counts the number of cells in a range that are not empty. For example:

      =IF(COUNTA(A1:D1), "yes", "")

  31. Hi,
    I have two tables with different data. I want to create a new table, extracting information from both tables (some of the data in the first table may not be in the other table but I want to be shown all of them), because I want to find the difference from both tables.
    Thank you,

  32. Hi, i would like to ask you, if a cell has a value how can i ask to put an other value to the next cell which is empty?
    Thank you!

    1. Hi Antigoni,

      You'll need an if statement similar to below:

      =IF(A1="VALUE","TRUE","FALSE")

  33. Please help,
    what am I doing wrong? the cell just returns what is in AT2 regardles of what is in C9
    =IF($C$9="1",IF($C$9="2",IF($C$9="3",IF($C$9="4",IF($C$9="5",IF($C$9="6",IF($C$9="6",IF($C$9="8",IF($C$9="9",IF($C$9="10",BL2,),BJ2),BH2),BF2),BD2),BB2),AZ2),AX2),AV2),AT2)
    Thanks.

    1. never mind fixed it
      =IF($C$9=0,"",IF($C$9=1,AT2,IF($C$9=2,AV2,IF($C$9=3,AX2,IF($C$9=4,AZ2,IF($C$9=5,BB2,IF($C$9=6,BD2,IF($C$9=7,BF2,IF($C$9=8,BH2,IF($C$9=9,BJ2,IF($C$9=10,BL2)))))))))))

  34. i have a formula to work out if stock is short over or equal how do it add the equal =if (c2>b2;"over";"short")

    1. ---=IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ")))----

    2. Amended =IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ")

      1. -- =IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ") --

    3. =IF(C2=B2,"EQUAL",IF(C2>B2,"OVER",IF(C2<B2,"SHORT","")

  35. Hello, can you please help me with this?=IF(AM4=AM6,IF(AK4="MED""NWRK""CLL""RQO""OOS""WCMP",0,AK4-AO4-AP4-AQ4)-AL4)
    If there is data in AM6 I get the result I want. If MED, NWRK, CLL etc is entered in AK4 and there is no data in AM6 I get #value. I would like a blank cell. This was working when it was just"MED" and had not added the others.
    Thanks for your time.

    1. Hi, The concept you put up is conflicting, please narrate the job properly in order to provide you with the correct format.

  36. I need a function to determine if age greater than 23, and should return the text Yes if true and No if false.

    1. =IF(A1>23,"YES","NO")

  37. IF THIS CELL IS LESS THAN OR EQUAL TO 30 COPY THE VALUE OF THIS ROW,IF NOT,ZERO,

    1. Hi Ron,
      =IF(A1<=30,A1,0)
      The site may have issues showing the formula. If the above is broken, try the one below.
      =IF(OR(A1=30,A1<30),A1,0)

  38. Hello! I am having trouble writing a formula.
    I need the date(that is not final) in C22 to project 107 weeks out and then compare that new date to 12/31/2024 and pick the earlier of the two. This new date would automatically go to cell c32.

    I appreciate any help. Thank you

    1. Hi Mallorie,
      Please try the formula below.
      =IF(C21<C22,C21,C22)
      You might have to format the columns to be shown in the date format.

  39. =IF(OR(ISBLANK(N9),OR(ISBLANK(P9),ISBLANK(O9))),,(N9*(P9-O9))*100) I want to add a function so that it will only multiply by 100 if Row L9 to L123 says Debit or Credit. so if it does not show debit or credit it will put the number instead.

    1. Hi Stephen,
      =IF(OR(L9="DEBIT",L9="CREDIT"),(N9*(P9-O9))*100,(N9*(P9-O9)))
      Formula above only checks per row. You get a value per row
      =IF(OR(COUNTIF(L9:L123,"DEBIT"),COUNTIF(L9:L123,"CREDIT")),(N9*(P9-O9))*100,(N9*(P9-O9)))
      Formula above checks the whole range of L9:L123 if it contains debit or credit.

  40. Hello
    I need a formula for if() i have 9 cells in a row for montly sales example 2 4 6 7 0 3 5 8 3 {yes}
    I want to use =if to see if there is a 0 in the row to say yes or no

    1. Hi L,
      Try to formula below.
      =IF(COUNTIF(A1:A10,0),"YES","NO")

  41. Hi, Could you please help me? I need a function. B6 to B8 each have a drop down list and in C6 I need either "Section booked" if any of B6, B7 or B8 has any text. If all blank then "Select"
    It has been driving me crazy. Thank you so much

    1. Hi Barbora,
      Please see below.
      =IF(OR(B6"",B7"",B8""),"SECTION BOOKED","SELECT")

      1. Hi Barbora,
        Replace "greaterthanlessthan" with the operators
        =IF(OR(B6 greaterthanlessthan "",B7 greaterthanlessthan "",B8 greaterthanlessthan ""),"SECTION BOOKED","SELECT")

        1. Replace greater than with

  42. Excel 2017 Have 4 columns, need an if statement to add dollar amount in the 4th column. Three columns are designated as A, B, & C, 4th is Amount. If I put an X in Column A , needs to show $10 in column 4, if i put an x in column B, needs to show $5 in column 4, if I put an X in column C, needs to show $1 in column 4.
    The first one I can get to work, not the others. In column 4 I put this in:
    =IF(A1="X",10,"")

    1. Hi Harold,
      =IF(A1="X",10,IF(B1="X",5,IF(C1="X",1,""))

      Is that what you're looking for?

  43. Hello, trying to get the word "OFF" to register as a zero value in a formula.=IF(D6=D6,IF(B6="OFF",0)-C6) The problem I have is this results in a negative number. I need B6 to equal 0 ONLY when the word OFF is entered there otherwise use the number entered. Here is the next cell down on the table I am creating hope this explains the D6=D6,
    =IF(D7=D6,IF(B7="OFF",0)-C7) . Thanks

    1. SOLVED - =IF(D6=D6,IF(B6="OFF",0,B6)-C6) - Thanks anyway.

  44. Can anyone help

    =IF(A4="Month"&B4="US:W2 Salaried",173.33)

    why it is returning false statement

    1. Hi Ankur,

      =IF(AND(A4="Month",B4="US:W2 Salaried"),173.33,"")

      Was that what you were trying to do?

  45. Could you please help, I want to write if E5 is greater or smaller than 0 enter that amount in C6

    1. Hi Chris,
      The formula below should work.
      =IF(OR(E5>0,E5<0,),E5,"")

  46. Hello,
    Need one help. I have two values in cell A (Y or N) and i want to use IF statement but i am getting error. The statement is like this if the cell A contains N then it should return N else it should leave blank
    IF(A=N,"N","")

    1. Hi Madhosh,

      Be sure to always enclose text values in quotations marks and use appropriate cell references. For example:
      =IF(A2="N","N", "")

      This formula goes to any empty cell in row 2, and then you can drag it down to as many cells as needed.

  47. Hi Svetlana,

    I would like to return a value of "No" in B1 if A1 is either "0" or "na".
    Is this possible?

    Thanks!

    1. Hi Brett,

      Sure. Please use this formula for B1:

      =IF(A1="", "", IF(OR(A1=0, A1="na"), "No", ""))

  48. Hello, how do I make an if statement see the text in a cell and not a reference? In Cell F2 the value is "T19" I want to say, if F2 = T19 than "A19" with A19 being text not a reference.

    1. Hi Lee,
      =IF(F2="T19","A19","")
      Is that what you meant?

  49. HI, May u help me with this situation?i cant get the right formula for that 0 or negative figure is equal to 0
    0 or negative =0
    >0 -10% = 1
    >10% = 2

    1. Sorry, guys, our blog engine sometimes breaks formulas in ">" and "<" symbols.
      If my understanding of the task is correct, this formula should work a treat:
      =IF(G5>10, 2, IF(G5>0, 1, 0))

    2. i have this as my current =IF(G5>10,"2",IF(G5<=10,"1",IF(G5<0,)))

      1. =IF(G5>10,2,IF(AND(G50),1,IF(G5=0,0,"")))

        Sorry, the original formula is broken, not sure why it did not paste properly.

      2. =IF(G7>10,2,IF(AND(G70),1,IF(G7=0,0,"")))

        Hope that helps.

  50. what is the formula for interval,
    fill the cell outstanding if score 4.5 to 5
    fill the cell V.Good if the score between 3.5 to 4.4, and so on..
    i tried to make rounding but it doesn't work if the number not whole even after rounding

    1. This one should work. You can change the text results to whichever you think fits better.

      =IF(E5>=4.5,"OUTSTANDING",IF(AND(E5>=3.5,E5=2.5,E5=1.5,E5<2.5),"SATISFACTORY",IF(E5<1.5,"FAIL","")))))

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 :)