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 69. Total comments: 4830

  1. Hi - I'm trying to create a formula to do the following.

    If original date of hire equals last date of hire leave blank if not display the last date of hire.

    thank you!

  2. J4=N3 the result should be not pass because of J4. how can I get this formula to work regardless if C4 is a pass? sorry posted wrong before. thanks guys. much appreciated.

  3. If "C" column Budgeted figure is 40.83 & Actual in "D" column is 37.69 then the VAR in "E" column is -3.15 with 92% Achievement in "F" column and the weightage defined for 100% achievement is 35 Marks which is in "G" column.

    I am looking out the IF formula help that if "F" column is less then 79.99% then "0" marks, and if same is between 80% to 94.99% then half of the 100% weightage defined 35 marks that is 17.5, and if the scoring is between 95% to 100% then the Actual % of weightage defined like if 97% achieved then 97% of 35 marks that is 33.95 marks.

  4. Dear,
    I have an excel file in which I have mentioned digit from 0194 to 0300 (cash invoice) and 0070 to 0125 (credit invoice), in a single column.
    On the top of the excel file, I want to mention a cell with latest cash and credit invoice entered.

  5. I am needing a formula that will calculate match on percentages. Like if a person is contributing 4.5% to a fund, the company would match 100% on the first 3% and then .5% on the next 2%. So in the end if a person contributed 5%, then the company would match 4%. So, if a person was paid $1000.00, $50.00 would be their 5% deferral, the company would contribute 100% on the first 3% which would be $30.00 and then .5% on the next 2% which would be an additional $10.00. But if I could have that all in one operand and also to return what the percentage should be if it is an awkward number too.

  6. OK SO I HAVE THIS PART RIGHT =IMSUB(720,D4+J4)BUT I ALSO WANT IT TO DO IF D4 FOR IS EMPTY THEN SO IS I4

  7. I need to do a study for value between 100 and 110. I tried =if(100<A1<110,"Bad","Good"), but it doesn't work. Please help.

  8. Hi

    I would like to copy cell A1 to cell B1 (in another worksheet). What formula should I use so that if cell A1 is blank cell B1 stays blank as well without returning "0", "#REF, etc..

    Thanks!

  9. Dear All,

    Request formula for the following condition.

    A1 = TRUE , A2 = TRUE & A3 = FALSE

    in B1 if i choose AND (from List box)

    1.C1 should check All "A1, A2 & A3" cells contains TRUE value and return TRUE otherwise FALSE

    in B1 if i choose OR (from List box)

    2.C1 should check Either of "A1, A2 & A3" cells contains TRUE value and return TRUE otherwise FALSE

    Thanks in advance for your help.

  10. Hi,

    pl. help me by sharing the formula.

    in a cell there is a figure,i.e. Rs. 25,000,000, now I want to make it Rs. 250. Another example Rs. 2,500,000 would be Rs .25.

    can it be possible by FORMAT CELL option, as in cell, where I want to change the figure, already having different formula.

    thanks,
    S Khan

  11. bracate value (sagar) insert in new column or row
    like-
    PUNE(SAGAR) PUNE SAGAR

    please help me

    1. Assume that cell A1 contain this: "PUNE(SAGAR)" (exact match, no modifications, and not case sensitive).

      =IF(A1="PUNE(SAGAR)","PUNE SAGAR","")

      1. SAGAR(PUNE) SAGAR PUNE

        FORMULA NOT WORK
        I WANT TO CEPRATE CELL FOR SAGAR AND PUNE
        PLEASE HELP

  12. Respected Sir,
    You have sent a solution but when I enter this formula in the appropriate cell an error shows that your formula missing a parenthesis. Please sir see this again minutely!

    =IF(AND(OR(G9<33,G9="A"),OR(H9<33,H9="A"),OR(I9<28,I9="A"),OR(J9<28,J9="A"),OR(K9<33,K9="A"),OR(L9<17,L9="A"),"PASS","FAIL")

    Regards

    1. I writed the formula right on web. :)
      It is possible to make mistakes. :)

      =IF(AND(OR(G9<33,G9="A"),OR(H9<33,H9="A"),OR(I9<28,I9="A"),OR(J9<28,J9="A"),OR(K9<33,K9="A"),OR(L9<17,L9="A")),"PASS","FAIL")

  13. Hello everyone,

    I am preparing a file and I need the formula for:

    1st argument - if BI says yes and A1 says No, the value of C1 remains the same
    2nd argument - if AI says yes and B1 says No, the value of C1 remains the same
    3rd argument - if AI says No and B1 says No, the value of C1 remains the same
    4th argument - if A1 says Yes and B1 also says yes than the value of C1 is divided by 2

    Thanks
    Yash

    1. AI or A1?

      =IF(OR(AND(A1="yes",B1="no"),AND(A1="no",B1="yes"),AND(A1="no",B1="no")),C1,IF(AND(A1="yes",B1="yes"),C1/2,"N/A"))

  14. =IF(AND(G9<33 OR G9="A"),IF(AND(H9<33 OR H9="A"),IF(AND(I9<28 OR I9="A"),IF(AND(J9<28 OR J9="A")IF(AND(K9<33 OR K9="A"),IF(AND(L9<17 OR L9="A") "PASS", "FAIL")

    Please sir email me on my email address! Waiting your response anxiously!
    Please sir see this formula and let me guide where I am at wrong.
    The logic is that a student should be passed in all subjects as well as not be absent i.e. "A". I have done all my efforts but I am totally failed. Reply on my this email address (aamir.sohailnawaz@grw.pgc.edu)

    1. I writed the formula right on web. :)
      It is possible to make mistakes. :)

      =IF(AND(OR(G9<33,G9="A"),OR(H9<33,H9="A"),OR(I9<28,I9="A"),OR(J9<28,J9="A"),OR(K9<33,K9="A"),OR(L9<17,L9="A")),"PASS","FAIL")

    2. I sent you an e-mail, but I post here too, for helping others.

      =IF(AND(OR(G9<33,G9="A"),OR(H9<33,H9="A"),OR(I9<28,I9="A"),OR(J9<28,J9="A"),OR(K9<33,K9="A"),OR(L9<17,L9="A"),"PASS","FAIL")

      If you want another formula, let me know.

      1. Respected Sir,
        This formula is not working properly. It shows that an error message "your formula have a missing parenthesis. Please see it minutely.

        =IF(AND(OR(G9<33,G9="A"),OR(H9<33,H9="A"),OR(I9<28,I9="A"),OR(J9<28,J9="A"),OR(K9<33,K9="A"),OR(L9<17,L9="A"),"PASS","FAIL")

        Regards

  15. This is a formula "=H5*0.029+0.3" that I use to calculate a fee in a sheet, but I want to insert a variation or a modification to the formula that force to apply the formula just is the value of h5>0.

    Someone can help?

    1. =IF(H5>0,(H5*0.029+0.3),"")
      That's all! :)

  16. Help! What's wrong here
    =IF(AS30>48,3%)*OR(AS30>4235292217<23,0.5%)*OR(AS30<16,0%)

    1. All is wrong!
      Please send me a sample to help you.
      remindfwd[at]gmail[dot]com

  17. please help me on this

    if F15 is within 0.26-0.75 and H15 is above 0.20 the value returns to "retained"

    if F15 is within 0.26-0.75 and H15 is below 0.20 the value returns to "revised"

    if F15 is not within 0.26-0.75 and H15 is above 0.20 the value returns to "revised"

    if F15 is not within 0.26-0.75 and H15 is below 0.20 the value returns to "rejected"

    thank u in advance

    1. =IF(AND(0.26<F15<0.75,H15#0.2),"retained",IF(OR(AND(0.26<F15<0.75,H15<0.2),AND(0.26#F15#0.75,H15#0.2)),"revised",IF(AND(0.26#F15#0.75,H15<0.2),"rejected","N/A")))

      Replace # sign with "greater" sign.

    2. I tried to post my comment many times...don't work.
      I solved your formula, but still not show.

  18. Can anybody please quickly help on the same.

    1. IF(C5<4,(IF(D5<40%,(IF(E5=4,C5=40%,D5=4,E5=7,C5=70%,D5=7,E5=9,(IF(D5>=90%,(IF(E5>=9,"GOLD")))))

    There are four different set of "if condition". Need to combine the same to get the consolidated result

    1. Please explain more...and you can send me a sample.
      There i no sens in formula you share.
      remindfwd[at]gmail[dot]com

      You can also share a document in Microsoft OneDrive.
      onedrive[dot]live[dot]com

    2. Please explain more...and you can send me a sample.
      There i no sens in formula you share.
      remindfwd[at]gmail[dot]com

      You can also share a document in Microsoft OneDrive.

      https://onedrive.live.com

  19. HOW TO USE IF FUNCTION >10=0,<10=1 AND <2=2 IN SINGLE CELL

    1. Assume that your cell is A1 (cell with the value).

      =IF(A1>10,0,IF(2<A1<10,1,IF(A1<2,2,"N/A")))

      It is working?

  20. hi again.

    sir/mam ReMind

    the formula you gave me last time doesn't work.

    please give me the true formula.
    this is the problem.

    if F15 is within 0.26-0.75 and H15 is above 0.20 the value returns to "retained"

    if F15 is within 0.26-0.75 and H15 is below 0.20 the value returns to "revised"

    if F15 is not within 0.26-0.75 and H15 is above 0.20 the value returns to "revised"

    if F15 is not within 0.26-0.75 and H15 is below 0.20 the value returns to "rejected"

    I will be glad to your right response. thank u.

    1. I posted at every comment :)

      =IF(AND(0.26<F15<0.75,H15#0.2),"retained",IF(OR(AND(0.26<F15<0.75,H15<0.2),AND(0.26#F15#0.75,H15#0.2)),"revised",IF(AND(0.26#F15#0.75,H15<0.2),"rejected","N/A")))

      Replace # sign with "greater" sign.

  21. Can someone help? Please
    G2 = TODAY()
    F17=Date completed

    Formula Needed - Does the Year in the F17 Fall within (=) this year, if so - Look at the date in F17, is it less than 91 days past that date or >181? If it is not (=) to this year Look at G2, is it less than 91 days past that date or >181?

  22. Hi, I'm trying to figure out an Excel equation for the following conditions:
    If selected cell is less than 99, then the output is 0. If selected cell is greater than or equal to 99, then the output is 1. Does anyone know how to do this all in one equation?

    1. Hi!
      Assume that your cell is A1:

      =IF(A1>=99,1,0)

  23. =IF(C10>10,10,C12)

    What do you mean with "figure shown"?

  24. hi..

    need a formula..

    if the cell value more than 10, use 10...if less than, use the figure shown. (10 value is auto sum from the above cells)

    Please assist..thanks

    1. =IF(C10>10,10,C12)

      Assume that C10 contain your SUM, and C12 contain another desired value.

      What do you mean with "figure shown"?

  25. Hi,

    In excel One cell value contains 5 years,3 Months,20 days and another cell value contains 4 Years,3 Months,3 days, I wan total of these two cell value in third cell (9 Years,6 Months,23 Days) Please help me to find out the formula. Please.

  26. I want to do a formula for if columnn a >= column b then yes, if column a < column b then no, and if blank it won't count. Please help. I can't figure out the blank portion.

    1. =IF(OR(ISBLANK(A1),ISBLANK(B1))," ",IF(A1>=B1,"YES","NO"))

  27. Hi i wanted to know if i could use the IF forumla in the same ceel, e.g Cell e20 shows a percentage (54%) which has to be typed in. I need that to change so that if its below 20%, it shows 1. If between 20/40% it shows 0.75, if between 40/60% it shows 0.25 and if 60/80% it shows 0.

    Can this be done?

    1. NO it can't. :) Near that cell is ok.

      1. Thank you! Is it possible to do it in another cell but still have the reulsts showing if entered manually in that cell? (sorry if that doesnt make sense!)

        1. No. The problem is that you can't insert in the same cell a VALUE and a FORMULA.

          Perhaps you can use this:
          In cell F20

          =IF(E20<20%,1,IF(E20<40%,0.75,IF(E20<60%,0.25,IF(E20<80%,0))))

          1. Ahhh okay, I assumed you could lock cells. Thank you!

            I tried that forumla and it comes back with FALSE :( but thank you so much for your help!

            1. I don't know why.
              Please share or send me the file to correct that error.
              remindfwd[at]gmail[dot]com

  28. i have one query if you could help me i need to take the total amount for management and non management suppose in 1 column i keep the job band 1 to 12 employee wise this column including management and non management and another column i keep the salaries for all those employee so i need to segregate the salaries for management and non management by selecting job band for example band 1 to 5 salary should come under management and from 6 till 12 salary should come under non management. please help me to fix this formula.

    1. Please send me a sample. remindfwd[at]gmail[dot]com

  29. I need a formula that IF there is any data/text in the cell it will EQUAL another cell, BUT... I want the cell with the formula to display the data/text, NOT the result of the formula. I want to then auto sum the RESULTS at the bottom of the column. So if a person marks an "X" in the cell, I want it to = an adjacent cell, but I want to SEE the "X", not the number. Then I want to add the number results and get a total. Is this possible??

    1. You have 3 columns
      Name (or ...) Number (or value) Selection

      The formula I use is:

      =IF(C2="X",B2,0)+IF(C3="X",B3,0)+IF(C4="X",B4,0)+IF(C5="X",B5,0)+IF(C6="X",B6,0)

      In a single cell is get a total (SUM) of numbers who have selections "X".

      That is?

  30. i need one help , to fix the my JV sheet, i need and Debit ("D") if my one cell amount is more than +1 , if the cell value is 0, "D","C") formula. But its not working..

    Hope any one can help me to solve this issue,

    Thank You So Much
    Shameer

  31. True or False will work for above. Please help.

  32. Hello.
    G2 = TODAY()
    F17=Date completed

    Formula Needed - Does the Year in the F17 Fall within (=) this year, if so - Look at the date in F17, is it less than 91 days past that date or >181? If it is not (=) to this year Look at G2, is it less than 91 days past that date or >181?

  33. =IF(T16<0,"Fail","Pass",if(L15="",""))
    i need correct formula. if ceel is less then 0 then pass otherwise pass and cell is blank then blank

    1. =IF(T16<0,"Pass",IF(L15="","","Fail"))

      It is what you want?

  34. the interest= 5% of price if it is <100,000, 7% if price is between 100,000 and 200,000, 10% if else.
    how can i find the function of this?

  35. my question is.
    if a1, "dddd" = "sunday" , "restday", "workday"

    how can i do this in excel.

  36. I want to use one formula that will round up all figures except ones that end in ".00" That is, $12,000.00 would stay at $12,000 but $12,000.01 would round up to $12,001. Thanks for any help...

  37. Can you help me with the IF Formula for if the % score is below 60% then it should be "0" marks, and if it is between 60.5% to 80% then it should be half of the Marks defined like if it 20 Marks then automatically it should show "10" and if it is above 80.5% then the full marks that is "20" marks.

  38. How can I do this in one formula (or any alternative way)
    (expected output is in column C)
    Dash indicated columns (4 rows)
    Column A - Column B - Column C
    positive - positive - excellent
    positive - negative - good
    negative - positive - fair
    negative - negative - poor

  39. Hi
    I need the following:
    IF there is a date value in column D, then Matter Age (Column E) is D-E.

    IF, however, there is not a date value in D, then matter age(Column E) is B3(CELL)-Date Received (column C).

    I only want to count business days please, not weekends.

  40. DEAR ALL,
    HOW CAN I CONSTRCUT FORMULA FOR THE NEXT SET OF DATA
    1-20000 1.0
    20001-30000 0.95
    30001-60000 0.80
    60001-85000 0.60
    >85000 0.4
    EXAMPLE : FOR 45000
    =(10000*1.0+20000*0.85+(45100-30000))/45000

  41. I want to have a narrative for the following:
    0 = Not true
    1 = Occasionally
    2 = Often
    3 = Very often

    I'm trying to do a formula where, if I type in "1", it would pop up with "Occasionally" in the cell next to it. Basically, whatever number I type, I'd like the narrative to be next to it.

  42. If C10 is "P"then C11 should "N", If C10 is "A" then C11 should "Y", if C10 is "O" then C11 should be "O" how to use the formul for this.

  43. Hi, can anyone please help me with below scenario:

    IF A2 is Date(today's Date) then my B2 Should be "completed". and if my A2 is "-" then B2 should be "WIP".

    Please help

  44. Hello
    Can you help with the formula below.

    If J3, J4 J5 <=79.99 turn red

  45. Hello,

    Can you please help me with the following formula?

    Customer Loan Amount = J2
    Customer Interest Rate = K2
    Available Interest Rate = L2
    I would like to divide $125,000 by the customer loan amount to get value: X
    For example a customer with a 250000 loan:
    125000/250000 = .5%
    If the customer interest rate (K2) and available interest rate (L2) difference is greater than or equal to X, I would like it to return Yes and if the difference is not greater than .5 I would like it to return No
    The L2 amount would be changed daily, and applied to the entire column.
    The J2 amount has $ I do not know if that effects anything.
    Percentage % sometimes used sometimes not.
    If there is a blank in any of the applicable values I would like it to not apply the formula.
    Right now I have:
    =IF(K3-L3>=125000/J3,"YES","NO")
    Is this correct?
    Thank you for your help.

    Best Wishes,

    George

  46. So I am using the IF function.
    I want to do multiple IF's in 1 Cell.

    If A2 is No then Joe
    If B2 is No then Benn
    If C2 is No then Tom
    IF D2 is No then Lee
    If E2 is No then Mick
    If F2 is No then Anne
    If G2 is No then Lynne
    If H2 is No then Terri

    I need these in 1 formula so in J2 if all were No it would have that list of Names.

    Can anyone Help?!?!?!?!?

  47. Hi, i'm really confused trying to fixed this. hope you can help me with this.

    =IF(OR(L36="Pending", L36=""), 50, 200)

    question: based on the above formula,
    i want cell B to formula Yes if cell A contain "pending"
    i want cell B to formula EmptyBlank if cell A does not contain "pending"
    in cell A contain : text and numeric of different content.

  48. I am trying to figure out how to use IF for a range.

    I want to check if Cell A1 is within 52 to 60 to be true. I tried the suggested formula but it is not working. =IF(AND(A1>=52,A2<=60),"OKAY","NO")

    Am I missing something?

  49. I need a cell to show a numerical value for ex
    If b2 is =999 then f2 =1
    what formula do I use?

    1. what I meant was;
      If b2 is greater than or equal to 300 or less than or equal to 999 then f2 equals 1

  50. One Order No. has 4 different sku and 3rd coloumn signifies the quantity of units ordered.. Blank space in first column represents that all sku s belong to order no in the above row. This is what I have:
    No sku Qty
    200090505 DO-NANO-CABLE-WRAP-TEAL 2
    DO-PICO-CABLE-WRAP-TAN 2
    DO-SMALL-CABLE-WRAP-TEAL 2
    DO-TINY-CABLE-WRAP-BEIGE 2
    200090494 BRAINSTO-PPS-PLN-NTBKA5 1
    DO-SMALL-CABLE-WRAP-BEIGE 1

    This is How I want Basically Transpose it in 1 single row: All sku's in 1 single row adjacent to the order:
    Column A B C D E F G H I
    Order200090505 SKU QTY SKU QTY SKU QTY SKU QTY
    Order200090494 SKU QTY SKU QTY

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)