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 16. Total comments: 3008

  1. Hello Svetlana,

    I have a seemingly simple formula, not sure why it doesn't work on me!

    =IF(ISNUMBER(SEARCH(OR("May-16",AX3),OR("Apr-16",AX3),OR("Mar-16",AX3))),1,0)

    I need a 0 or a 1 if the value in AX3 equals Mat-16, Apr-16 or Mar-16.

    This doesn't work either =IF(OR(AX3="Mar-16",AX3="Apr-16", AX3="May-16"),1,0)

    Please help!
    Many thanks in advance

    1. Hello Anon!

      You cannot compare a date with the text string "Apr-16".
      Use the following formula instead: =IF(AND(YEAR(AX3)=2016,OR(MONTH(AX3)=3,MONTH(AX3)=4,MONTH(AX3)=5,)), 1, 0)

  2. if i type A1 in cell B1 then i want the values in A1 in C1, please ans

    1. Hello Ajay,

      You can enter the following formula in C1:
      =INDIRECT(B1)

  3. Hello!

    Can you help me to create this formula:
    1 to 4 = LOW
    5 to 9 = MEDIUM
    10 to 14 = HIGH

    Thanks in advance :)

    1. Hello Roy!
      You can use this formula: =IF(AND(A1>0,A1<=4),"LOV",IF(AND(A1<=9, A1>4), "MEDIUM", IF(AND(A1<=14, A1>9),"HIGH", "out of range")))

  4. Hi! Can you please help me with this?
    For example: I want a cell to display "done" if material down and machine down was met and if not, it will display "attention".
    =IF((AND(Z3="Material Down","Machine Down")),"Done","ATTENTION")

    1. Hello Dev Reyes!

      In the AND statement, you forgot to specify the cell that should contain "Machine Down". For example:
      AND(Z3="Material Down",Y3="Machine Down")

      And the whole formula would be:
      =IF((AND(Z3="Material Down",Y3="Machine Down")),"Done","ATTENTION")

  5. I am trying to create a formula that if text = (specific text) then add 1, and the total number of (specific text) fields there are in that column.

    I have a specific ticket type for an event in column F in this spreadsheet, and I am trying to create a formula at the bottom that will tell how many of each ticket type there is.

    1. Hello Jeanette!
      To count the number of cells with a "specific text" you can use the COUNTIF function. For example:
      =COUNTIF(B1:B8,"specific text")

  6. Hi Guys,

    Please help on the following:-
    Gross profit margin>=20% = 0.7%
    Gross profit margin>=25% = 1%
    Gross profit margin>=30% = 1.5%
    Gross profit margin>=35% = 2.0%
    Gross profit margin>=40% = 2.5%

    1. Hello Theng!
      You can enter the following formula: =IF(F1>=40,2.5,IF(F1>=35, 2, IF(F1>=30, 1.5, IF(F1>=25, 1, IF(F1>= 20, 0.7, 0)))))

      F1 contains Gross profit margin.

  7. I have a spreadsheet in which I the if has 2 requirements to be correct, and if so then it will be this number, but if its below it needs to equal zero, and if it is above it equals a fixed number. How do I get it to have 2 options if false?

    =IF(AND(F1>29999,F1<70001),(F1-30000)*0.02,40000*0.02)

    the 40000*0.02 is if its greater than 70001 but if it is less than 29999 then it needs to be 0

    any help?

    1. Hello Chase!
      You can use two nested IFs:
      =IF(F1>29999,IF(F1<70001,(F1-30000)*0.02,40000*0.02),0)

  8. Hey All

    I would appreciate any guidance on the following formula please:
    If column A =1 and column B = 2 return X otherwise return Y.

    Many thanks
    Alice

    1. =If(A1=1,If(B1=2,"X","y"),"y")

  9. Hello everybody!
    That seems so simple but I can't do it :(

    I would like to know the formula for this:

    If any text is found in cells T23 U23 V23 or Z23 this same text should be copied to this cell where the formula goes (C23).

    This text could be any of these four: Apple/Pears/Peaches/Grapes

    Thank you so much

    1. Hi Augusta,

      What if 2 or more cells (T23, U23, V23, Z23) contain different texts? Which one should be copied to cell C23?

  10. For a Hotel Maintenance Log. I have a column for recording room numbers and want to apply a condition to a certain room number, so that front desk staff know what to do differently for this one apartment. I tried:

    =IF(A2=1307, "call owner")

    but no luck. Grateful for any ideas - thanks

    1. No worries - I have solved it:

      =IF(A2=1307,"CALL OWNER","")

      This leaves the cell blank if it's not for 1307, so works perfectly.

  11. Hi

    I have a nested IF AND formula that looks at times [HH]:MM It works fine until I have time over 24hours. I have this section in my formula:

    IF(AND(N2>=TIME(24,0,1),N2<=TIME(48,00,00)),"Between 24-48hrs")

    It doesn't work though...any ideas??

    1. Not to worry..I have solved it by converting time to decimals and working the > or < off those figures rather than time!

  12. Criteria Cell1 and Cell2 having numbers which may be higher,lower or same.
    What will be the formula to get in Cell3 as reply "Higher", "Lower" or "Same" any 1 will be answer.

    1. =IF(AND(A1=A2),"SAME", IF(A1<A2,"LOWER","HIGHER"))

  13. I have two sheets,
    1st sheet "A" column contains order no. in sequence and and "E" column contains Name of customer

    in 2nd sheet if I put any order No. in "C" column "D" column should show respective customer name of particular order No. from sheet 1.

    For eg.
    in sheet 1
    A22=2200 E22=Dettol Incorporation

    if I put 2200 in sheet 2 in c55 than D55 should show as E22 in sheet 1 ie. Dettol Incorporation
    Thanks

    1. Dear Svetlana
      i will put order no.in sheet 2 in sheet 2 in c column
      than in d column should show company name of respective order No. from sheet 1
      please help

      thanks

      1. got it, i found solution,

        =VLOOKUP(C55,Sheet1!A1:E500,5,"FALSE")

        thanks

  14. Champs need ur help i am rookie with excel and cant fig out the following formulae to apply

    Cell A2 = if Cell A1 is less than Cell B1 then Value should be cell A1 Minus cell B1 else if cell A1 is greater than cell B2 then value should be A1 Minus B2 ,,, and if the value of A1 is between B1 and B2 then should just say "ok"

    Example A2 = where the answer will be
    A1 = 3.3 B1 =2 B2 =3 this case A2 should give an answer as 0.3 and the cell should go red or any other colour,, please somebody help out

    1. Dear Svetlana Cheusheva please reply waiting for the same, If you are busy it's okay take your time.

      and if cant be done please drop a message it cant be done so that i can drop this file of mine

      Have a good day

      1. Hello Manish,

        Assuming that the value in B2 is always greater than in B1, you can use this formula:

        =IF(AND(A1>=B1,A1<=B2),"OK", IF(A1<B1,A1-B1, IF(A1>B2,A1-B2,"")))

        To highlight A2 with color, select it and create a conditional formatting rule with this formula =ISNUMBER(A2)

        Here are the detailed steps to create formula-based conditional formatting rules.

        1. Dear Svetlana ,

          Thank you it works awesome ,,but i dont get a negative answer as in the answer is right but without a negative sign
          Example A2 = where the answer will be
          A1 = -3.3 B1 =2 B2 =3 this case A2 should give an answer as ( -0.3)

          Rest all is fine thank you for the help your ( Excel-lency)

          1. Dear Manish,

            In your original post, the first condition to check was as follows:

            "if Cell A1 is less than Cell B1 then Value should be cell A1 Minus cell B1".

            And it is exactly what the formula does: A1 (-3.3) is less than B1 (2), so the formula returns -5.3 (-3.3-2)

            If you expect a different result, then please list all of the conditions in the order they should be checked in the formula. (Nested IF's check conditions in the order they appear in a formula; if the first condition is met, other conditions are not checked).

  15. Hi, I need a formula for one cell:
    IF A2 has a value between 101-122, then text "below average"
    IF A2 has a value between 123-144, then text "average"
    IF A2 has a value between 145-200, then text "above average"
    IF A2 has a value between 201-250, then text "not acceptable"

    Thank you.

    1. Hi Maria,

      You can use this formula:

      =IF(A2>200,"not acceptable", IF(A2>144,"above average", IF(A2>122,"average", IF(A2>100,"below average",""))))

  16. ok I am trying to create a score chart. Where the same cell returns a different value based on what is in that cell. if someone keys a 0 it returns a 5, if a 1 or 2 it returns a 4, if a 3 or 4 it returns a 3. something along the lines of

    =if(Q5=0,5),if(Q5=1,4),if(Q5=2,4)...

    thank you

    1. oh I got it...

      =IF(Q38=0,5,IF(Q38=1,4,IF(Q38=2,4,IF(Q38=3,3,IF(Q38=4,3,IF(Q38=5,2,IF(Q38=6,2,IF(Q38>=7,1,""))))))))

  17. Hi,

    I wanted use the formula with more than one logical test. what should I do. how to do so.

    Let's say. If,a1="Thursday","OFF","Working:)

    Apart from Thursday I also wanted to use Friday. What can I use to do so.

    1. Hi Tanveer,

      If you want to display "OFF" for Thursday and Friday, you can use the following formula:

      =IF(OR(A1="Thursday",A1="Friday"), "OFF", "Working")

      If you are looking for something different, please clarify.

      1. How to pay you thanks,

        you were awesome.

        Yes that is what I meant and it is working.

        Thank you very much for your help.

        Have a good one.

  18. Please solve the below problem:
    Column A1 to A4 contains: 3 or 2a or 2b or 1.

    Formula: Column B1 to B4 require: IF(A1=3,100, IF(A1=2a,50, IF(A1=2b,25, IF(A1=1,0)))

    1. Hello Nandu,

      Your formula is correct except that text strings like "2a" should be enclosed in double quotes:

      =IF(A1=3, 100, IF(A1="2a", 50, IF(A1="2b", 25, IF(A1=1, 0, ""))))

  19. Hello I am trying to create a worksheet with employees names in column B (which I choose daily from a validation list) when I click on the names I want their internal rate to populate in column J and external rate to populate in column J automatically-I have these names & rates on a separate sheet and right now am using the drop down list to do it manually-there must be a better way... Can this be done?

    1. Sorry I meant internal rate in column G and externalrate in column J

  20. Hi, can someone help me with an IF formula as follows:

    If Cell C17 contains "PASS", and Cell C30 contains "PASS", then PASS should be returned, if not, FAIL should be returned.

    1. Hi May,

      What you need is an If formula with nested AND like this:
      =IF(AND(C17="PASS", C30="PASS"), "PASS", "FAIL")

  21. hi , I am trying to write a nested formula. the formula I am using is
    =if(c4="jose","good",or(c4="jesus","better",""))
    The problem that I am running into is, that it reads the first nested formula but it will not read the second. can you help me with this formula. or recommend another

    1. Hi Jesse,

      You don't need OR in nested IF's:

      =IF(C4="jose", "good", IF(C4="jesus","better",""))

      1. thanks

  22. Hi,

    I am using an IF statement to check if 2 cells match.

    However where 1 cell is blank (not yet populated) it will reflect as "same"

    Can I use isblank in combination to bring back a blank cell if both referenced cells aren't populated.

    =IF(AC118"",(AC118=P118,"SAME","CHANGE"), "")

    1. Hi!

      Try this formula:

      =IF(AND(AC118="", P118=""), "", IF(AC118=P118,"SAME","CHANGE"))

  23. =IF(N8:N19="Completed","Yes","No")

    i have tried this to test list of cells which are updated thru a data validation list its not working please anyone can help.

    1. The list of Data validation referes like this

      Completed , in progress , on hold , new

      these are the four validation can be selected now i want this above formula to work to tell me once are completed.

      =IF(N8:N19="Completed","Yes","No")

      i have tried this to test list of cells which are updated thru a data validation list its not working please anyone can help.

  24. I need formula, if cell a1 is (3), cell b1 should be insert text "good"

    1. hi Zvonko,

      =if(A1=3,"good","")

  25. sorry got working with

    =IF(ISBLANK(AE5), TODAY()-M5,AM5-M5)

    now just the colouring..?

    1. Select the column(s) you want to color excluding header rows, and create a conditional formatting rule with the below formula (which checks if a cell in column AE is blank):
      =$AE5=""

      The detailed steps to create a formula-based rule can be found here.

  26. Hi,

    Please help me for getting the formula for below

    If B1 have some date mentioned then A1 has to come delivered
    if B1 dont have date it sould come in transit

    1. Hello Bahubali,

      Try this one:

      =IF(B1="", "in transit", "delivered")

  27. hi,

    i have a issue in excel for using a formula. my problem is sum of range in a column.for example, a table contains data describe below:-
    sr.no. class student name marks
    1 sixth parmod 166
    2 sixth kamal 140
    3 sixth parmod 250
    4 seventh kamal 270
    5 seventh parmod 180
    i want the sum of parmod marks of six th class. how and which formula is used for it......

    please help
    i have to find parmod marks of class sixth

    1. i used it but something wrong.
      =if(b:b="sixth",if(c:c="parmod",sumif(c:c,"parmod",d:d)))

      1. Hello Parmod,

        To conditionally sum cells, use SUMIF or SUMIFS function to sum with one or several criteria, respectively.

        In your case, you can use this formula:
        =SUMIFS(D:D,C:C, "parmod",B:B, "sixth")

        Where column D contains marks, C - names, and B - classes.

  28. I have a data validation list column (in cell O2) with the following pull down options: Elimination, Substitution, Engineering, Administration, Culture, PPE. If Elimination is selected, I would like the adjacent cell to return the value 0.1. If Substitution is selected, the adjacent cell should have the value 0.3. Other values for remaining text are 0.5, 0.75, 0.8, and 0.9.

    If you can figure this out you will be my forever hero!

    1. Here i have one question..Dont know its possible with excel or not..If any one have idea than tell me..
      I have data validation list in cell D5 with the drop down options: Residential , Commercial..I have another drop down list in E5 with options Flat , Bunglow , Shop , Office ...If i select Residential in D5 then in cell E5 it shows only 2 option Flat , Bunglow..Dont show me another options like Shop , Office..

  29. Hello!

    I'm trying to make a formula based off a date column.
    Ideally, I would like to have a separate column populate a 'blank' or "Follow UP" based on whether or not the date is older than 15 days.

    I tried =IF(C1-TODAY()>15,"FOLLOW UP","") but obviously it did not work.

    Am I on the right track?

    1. Hello Claire,

      You were almost there! :)

      =IF(TODAY()-C1>15,"FOLLOW UP","")

  30. hi,

    if A2 is less than (negative) or equals to zero then it should come the same value as in A2,

    But if A2 is greater than zero then it should result A2 * 10% ( or any percent which i want to put)

    reply

    1. Hi Naresh,

      Here you go:

      =IF(A2<=0, A2, A2*10%)

  31. I need a formula whereby if the value of cell A2 is 199 or less, then "Low," if the value of cell A2 is from 200-399, then "Medium," or if the value of cell A2 is 400 or above, then "High."

    Would appreciate any help!

    1. Hello!

      Try this formula:

      =IF(A2<=199, "Low", IF(A2<=399, "Medium", "High"))

  32. Hi

    I want that if me or someone else fill in, for example in cell A2 the number 160161 it should be in cell A3 the time 05:30

    Also if i or someone else fill in a number that not exist from my numbers then a text for example "wrong number"

    Can i get help with a formula for that?

    IF=160131 in cell A2 then 05:30 in cell A3.
    And IF not none of the number then text wrong number

    Thanks in advance

    1. Hi Tommy,

      If you want to see value "05:30" when A2 contains number 160161, you can enter the following formula into A3:
      =IF(A2=160161,"05:30","wrong number")

      If you want to make sure the cell is formatted as time, please use the following formula instead:
      =IF(A2=160161,TIMEVALUE("05:30"),"wrong number")

      I hope this helps

  33. I'm trying to determine the monthly salary of our instructors. They get 30$/hr if they teach less than 120 hours/month, but receive 35$ for each additional hour after the 120 hr threshold.

    This is the formula I put in, but it wouldn't work:

    =IF(C2>120, (120*D2)+(C2-120)*35;C2*D2)

    Could you please help me out?

    thanks,
    melih

    1. Hello Melih,

      Your formula is correct except for a typo and one little detail. Depending on the locale you have in Excel, please make sure you use either commas or semicolons as separators, i.e. either
      =IF(C2>120,(120*D2)+(C2-120)*35,C2*D2)
      or
      =IF(C2>120;(120*D2)+(C2-120)*35;C2*D2)

      Assuming you always have the regular rate per hour in cell D2, you need to make it an absolute reference so that it doesn't change to D3, D4, etc. in other rows:
      =IF(C2>120,(120*$D$2)+(C2-120)*35,C2*$D$2)

  34. Dear Madam,

    i have a excel for branch reconcilation, same value repeated in my excel sheet but another sheet only shows two or three amount but it is same . how can i compare the sheet.

  35. OMG, Jenny I have a similar situation!!!

    I need to change Excel rows from one colour to another when a text name is changed is a specific column where each cell has one of 4 titles.

    So yes, is anyone able to help please...?

    With thanks in advance too :o)

      1. I'm sorry, but didn't find you reply very helpful, so found another web-site that was, and the simple answer is to:
        Highlight line you want to change that has the key word written in it (in my case it is the word Forecast)

        go into Conditional Format option

        choose New Rule

        in "Edit the Rule Description:" type the coordinates of the cell which has the key word (on my spreadsheet this was =$G87="Forecast")

        Click on the Format icon and set whatever format you'd like to have occur & ckick ok)

        Then when its showing as a Rule, amend the "Applies to" coordinates from what is in there (which will be the single line you originally highlighted) to all of the spreadsheet you want this rule to apply to (so for me this was =$G$50:$W$500 )

        Click on the "Apply" icon (lower right corner)

        And hey presto it works!

        All good wishes - Louise :o)

  36. Hello,

    I need a help to do a conditions using date in excel 2007:

    Query:

    I need to set a follow up with the date in excel. There are two status. Open and close.

    Conditions:

    1)If status "open" & date of today more than 3 days then "Remainder" and color changes (entire row should be highlighted).

    2) If status "Open" & date of today more than 5 days then "Exceeded" and another color changes (entire row should be highlighted).

    3) If status "closed" then "Completed".

    Can any one help me with this issue?

    Thank you in advance.

  37. Hi,

    My query is IF (A1>15000,"150",IF(A1>20001,"200")
    answer is not displaying by applying this formula
    what i required is IF A1 cell is greater than 15,000 answer should come 15. If A1 cell is greater than 20,000 answer should come 200

  38. HI ,
    I have excel sheets.
    Excel 1,Excel 2.

    Excel 1 having 10 fields
    Ecel 2 having 3 fields of Excel 1.

    Excel 1 fields
    no requester date status

    Excel 2 Fields
    requester date status

    Here,what my query is
    i want display in Excel 2 only status pending items with the related fields.

    Can you help me on this .
    Thanks in advance.

    1. Hi veeru,

      The simplest way is to filter sheet Excel 1 by the status column and copy the filtered values to sheet Excel 2.
      To filter the column please go to the Data ribbon tab, click Filter and then select the pending value in the status column.

      1. I WANT TO DO EXCEL SHEET CELL COLOR CHANGE LIKE IF I PUT "P" THEN THAT CELL WILL CHANGE IN GREEN . IF I WRITE A THEN IN RED. IF PUT HALF DAY THEN YELLOW

  39. Hello

    I am trying to look for a formula that will look in on cell, and if true retrieve data from another cell. the function that I am trying to use is
    =IF(B30="X","(E36)","")

    I am trying to retrieve the data in cell E36. I think i need to use the IF function Because if B30 has an x then I want it to retrieve the date in E36 and if not I need it to be blank

    thank you for help

  40. I have this formula
    =IF(ISNUMBER(SEARCH("ΜΑΚΑΡΟΝΙΑ";ΠΡΟΓΡΑΜΜΑ!E14)); "1";"")
    It works fine but I want to search text in two cells
    Thanks

    1. Hi GIANNIS,

      Please try to use the OR function in the first parameter:
      =IF(OR(ISNUMBER(SEARCH("ΜΑΚΑΡΟΝΙΑ";ΠΡΟΓΡΑΜΜΑ!E14)), ISNUMBER(SEARCH("ΜΑΚΑΡΟΝΙΑ";ΠΡΟΓΡΑΜΜΑ!D14))); "1";"")

  41. =IF(G5=13,"1600",IF(G5=20,"1700",IF(G5=25,"2400",IF(G5=40,"12000",IF(G5=50,"22000",IF(G5=80,"30000",IF(G5=100,"41000",IF(G5=150,"145000",IF(G5=200,''322000'')? why it can not working? please kindly to help me! Thank you inadvance.

    1. Hi Koung,

      Please use the following formula:
      =IF(G5=13, "1600", IF(G5=20, "1700", IF(G5=25, "2400", IF(G5=40, "12000", IF(G5=50, "22000", IF(G5=80, "30000", IF(G5=100, "41000", IF(G5=150, "145000", IF(G5=200, "322000")))))))))

  42. Can someone help me with this ...

    =IF(FIND(" (",D2),LEFT(D2,(FIND(" (",D2)-1)),E2)

    The True condition (finding a "(" in the string works) in row 2 but the False fails with a "#VALUE!" error in row 3

    D2 contains "Lafayette, LA (LFT)" and E2 contains "LA (LFT)" ... result should contain "Lafayette, LA" - it does!
    D3 contains "Neurnberg, Germany" and E2 contains "Germany" ... result should contain "Germany" - it fails!

    Thanks.

    1. Hi Suds,

      You should use the following formula:
      =IF(NOT(ISERROR(FIND(" (",D3))),LEFT(D3,(FIND(" (",D3)-1)),E3)

  43. Is there anyway to merge all this together in one field?

    If A1 is blank = Jack
    If A1 is not blank, and B1 is blank, and C1 is blank, and D1 is blank = Jill
    If A1 is not blank, and B1 is blank and D1 is blank = Sunny
    If A1 is not blank, and B1 is blank, and D1 is not blank, and E1 is not blank = Sunny
    If A1 is not blank and E1 is blank and B1 is not blank = Cookie
    If A1 is blank, B1 is not blank and D1 is not blank and E1 is blank = Mike
    If A1 is not blank and B1 is not blank, and C1 is not blank = Mike

    1. This is what I have but there is two problem. One i'm not able to add the last statement in and the second the results just shows up false. Any guidance would be appreciated.

      =IF(ISBLANK(A1),"Jack", IF(AND(ISBLANK(B1), ISBLANK(C1), ISBLANK(D1)), "Jill", IF(AND(ISBLANK(B1), ISBLANK(D1)), "Sunny", IF(ISBLANK(B1), "Sunny", IF(ISBLANK(E1), "cookie", IF(AND(ISBLANK(A1), ISBLANK(E1)),"Mike"))))))

      1. Oops my mistake. All my E1 are actually C1. Sorry.

        So here it is again.
        1. If A1 is blank = Jack
        IF(ISBLANK(A1),"Jack",

        2. If A1 is not blank, and B1 is blank, and C1 is blank, and D1 is blank = Jill
        IF(AND(ISBLANK(B1), ISBLANK(C1), ISBLANK(D1)), "Jill",

        3. If A1 is not blank, and B1 is blank and D1 is blank = Sunny
        IF(AND(ISBLANK(B1), ISBLANK(D1)), "Sunny",

        4. If A1 is not blank, and B1 is blank, and D1 is not blank, and E1 is not blank = Sunny
        IF(ISBLANK(B1), "Sunny",

        5. If A1 is not blank and C1 is blank and B1 is not blank = Cookie
        IF(ISBLANK(C1), "cookie",

        6. If A1 is blank, B1 is not blank and D1 is not blank and C1 is blank = Mike
        IF(AND(ISBLANK(A1), ISBLANK(C1)),"Mike",

        7. If A1 is not blank and B1 is not blank, and C1 is not blank = Mike
        ???

        Current code i'm stuck on without statement 7

        IF(ISBLANK(A1),"Jack", IF(AND(ISBLANK(B1), ISBLANK(C1), ISBLANK(D1)), "Jill", IF(AND(ISBLANK(B1), ISBLANK(D1)), "Sunny", IF(ISBLANK(B1), "Sunny", IF(ISBLANK(C1), "cookie", IF(AND(ISBLANK(A1), ISBLANK(C1)),"Mike"))))))

        1. Hi TinWin,

          Please use the following formula:
          =IF(ISBLANK(A1), "Jack", IF(AND(ISBLANK(B1), ISBLANK(C1), ISBLANK(D1)), "Jill", IF(AND(ISBLANK(B1), ISBLANK(D1)), "Sunny", IF(AND(ISBLANK(B1), NOT(ISBLANK(D1)), NOT(ISBLANK(E1))), "Sunny", IF(AND(ISBLANK(E1), NOT(ISBLANK(B1))), "Cookie", IF(AND(NOT(ISBLANK(B1)), NOT(ISBLANK(D1)), ISBLANK(E1)), "Mike", "Mike"))))))

  44. Hi,

    If anyone can help?

    I am trying to use if function to show the text "probable" is ok. It is a very simple formula but I am completely baffled that "probable" comes as a false value when it should be a true value. Anyone have a solution to this? Results below with formula;

    certain ok =IF(B3="certain","ok","not ok")
    probable not ok =IF(B4="probable","ok","not ok")
    possible ok =IF(B5="possible","ok","not ok")
    probable not ok =IF(B6="probable","ok","not ok")
    red ok =IF(B7="red","ok","not ok")
    chair ok =IF(B8="chair","ok","not ok")
    laptop ok =IF(B9="laptop","ok","not ok")
    maybe ok =IF(B10="maybe","ok","not ok")
    probability ok =IF(B11="probability","ok","not ok")
    probable not ok =IF(B12="probable","ok","not ok")

    Why is "probable" seen as false(not ok)?

    Looked everywhere can't find a solution

    1. Hi Mohammed,

      It seems you have spaces in your cells. Please try to trim the cell value before comparing.

      =IF(TRIM(B4)="probable","ok","not ok")

  45. i want to stop the TAT calculation after enter my final date, could you please let me know that formula for that, and i know how to use TAT calculation, only i want to know if i enter 3rd date it should be freeze.

    2. can you send me sample file

  46. If Cell A2 is greater than cell B2 then Cell C value is 1, If Cell A2 is smaller than cell B2 then Cell C value is 2, If Cell A2 is equal to cell B2 then Cell C value is 0. Can anyone let me know the function

    1. Hi Taufiq,

      Here you go:
      =IF(A2>B2, 1, IF(A2<B2, 2, 0))

      1. Dear Taufiq,

        Put your values in A2 & B2 , PASTE this formula in C2.

        this formula can be make shorther but for your understanding i have used 3 formulas

        =IF(A2>B2,1,IF(A2<B2,2,IF(A2=B2,0)))

  47. Dear Sir,
    I want to do stock ageing.Our item reference sample is 89p25. Here each year represent different parcel number for eg.(p25,p26,27). How to set formula in another sheet.Please advise.

    Thanks & Regards

  48. Hi,

    We Want Excel Sheet of "How can used "=if".So, Kindly Provide to me As soon as possible.

    Thanks & Regards.

  49. Hi,
    I have one question.
    Actually i just want to create a new cell if my "if Condition" become true and not to create any cell if condition become false.
    Can you please suggest is there any way to resolve with this issue ?

    Thanks in advance.

    1. You may have to learn VBA for this.

  50. B6 is 70% And D19 is 0 i need the result is Needs Improvement.

    Now I'm Trying with this one which is not getting the result.

    IF(AND(B6>=70%,D19=0,"Needs improvement".

    Please help me.

    1. =IF(AND(B6>=70%,D19=0),"Needs improvement","")

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