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

  1. =IF((I2-H2=10);10;(SUM(I2-H2)))

    If (I2-H2) = display 10

  2. If I2-H2 10 I want the have 10 in that column. I think it needs to be something like this?...

    =IF((I2-H2=10);10;(SUM(I2-H2)))

    Could you please help me?

  3. I want to keep the formulae changing for a chemical based on the value of n.
    Can you tell me how I can use excel for this

    CnHn+2O8P

  4. If value is high and low then result should be Amber

  5. Hi, Goodmorning,
    I want to use IF function to compare "the sum of of a range of cells, with a figure in a cell, and to return "Complete" if the sum equals to the figure in the cell, and if it is not equal then to return "Not complete, yet to receive:" and display the remaining quantity by doing a subtraction of the total quantity and received quantity.

    Please suggest me how to do this.

  6. Hi Svetlana,

    Your help on this forum is truly invaluable and helped me many times but now I run into a problem that I cannot solve.

    Column A contains a list of computer names
    Column B contains the list of IP addresses detected on the computer.

    Computer Name IP Address
    COMP1 192.168.10.1
    COMP2 192.168.3.225
    COMP3 192.168.3.224
    COMP4 192.168.3.226
    COMP5 192.168.3.178
    COMP6 "192.168.146.147,192.168.3.172"
    COMP7 192.168.3.170

    Issue:
    When two IP addresses detected on the computer, the reporting software puts these into parenthesis and separates them by a comma.

    Task:
    If the computer only have one IP address I'd like to show this in column D
    If the computer have two IP addresses then I'd like to show the first address in column D and the second IP on column E, like this.

    Computer Name IP Address First IP Address Second IP Address
    COMP1 10.136.10.1 192.168.10.1
    COMP2 10.136.3.225 192.168.3.225
    COMP3 10.136.3.224 192.168.3.224
    COMP4 10.136.3.226 192.168.3.226
    COMP5 10.136.3.178 192.168.3.178
    COMP6 "10.136.146.147,10.136.3.172" 192.168.146.147 10.136.3.172
    COMP7 10.136.3.170 192.168.3.170
    COMP8 10.136.3.165 192.168.3.165
    COMP9 10.136.3.175 192.168.3.175
    COMP10 "10.136.144.137,10.136.3.33" 192.168.144.137 10.136.3.33
    COMP11 10.136.3.105 192.168.3.105

    So far I have been able to display the first IP address by using a helper column C where I detect the presence of the comma using:

    =COUNTIF($B2,"*,*")

    It returns 0 when a single IP is resent and 1 when there are two.

    Next in column D I use this formula to parse out the first IP address:
    =IF($C2=0,$B2,LEFT($B2,(FIND(",",$B2,1)-1)))

    Next in column E I use this formula to parse out the second IP address:
    =IF($C2=0,"",MID($B2,FIND(",",$B2)+1,256))

    Problems:
    How do I rewrite the formula to eliminate the helper column C?
    How do I get rid of the preceding and trailing parentheses?

    So far this is how my sheet looks like.

    Computer Name IP Address Dual IP First IP Address Second IP Address
    COMP1 10.136.10.1 0 10.136.10.1
    COMP2 10.136.3.225 0 10.136.3.225
    COMP3 10.136.3.224 0 10.136.3.224
    COMP4 10.136.3.226 0 10.136.3.226
    COMP5 10.136.3.178 0 10.136.3.178
    COMP6 "10.136.146.147,10.136.3.172" 1 "10.136.146.147 10.136.3.172"
    COMP7 10.136.3.170 0 10.136.3.170
    COMP8 10.136.3.165 0 10.136.3.165
    COMP9 10.136.3.175 0 10.136.3.175
    COMP10 "10.136.144.137,10.136.3.33" 1 "10.136.144.137 10.136.3.33"
    COMP11 10.136.3.105 0 10.136.3.105

    Your help is greatly appreciated!

    Cheers,

    Attila

  7. Somebody please help with data validation, Like i need to extract data from one worksheet to different worksheet for more THAN 4 columns at a time .. don't know to deal with it...

    Thanks

  8. Somebody please help with data validation, Like i need to extract data from one worksheet to different worksheet for more 4 columns at a time .. don't know to deal with it...

    Thanks

  9. Hi,

    I have three colums and four rows with different values.I have a column more with a date for each row.As example:

    Date: C1 C2 C3
    12.12.2013 205 201 100
    11.12.2013 32 10 150
    9.12.2012 99 52 87

    What command i must use to see in a tabel the most recent date appearence of any value from colums C1,C2,C3?

    1. Sorry i have 4 colums with 3 rows and the table is this:

      Date: C1 C2 C3
      12.12.2013 / 205 /201 /100
      11.12.2013 / 32 /10 /150
      9.12.2012 / 99 /52 /87

      What command i must use to see in a tabel the most recent date appearence of any value from colums C1,C2,C3?

  10. Sir,
    I need to solve the below:
    if(a1="exe",2999), if(a1="dlx",3999), if(a1="std"=1750)
    Please help me.
    Thanks a lot.

  11. Hello i have this formula in my cells =IF(AND(M$2>=$D5, M$2<=$E5),"1")
    then at the bottom of the sheet, I tried to sum all the 1 in the column but that "1" must not be a number because I always get a 0 result.
    I am really trying to put a 1 in if between a certain date and then sum them all up.
    can anybody help?

  12. Hi What do I put if the customer’s age is less than 25, the Daily Insurance is £4; if not, it is £3.

    Thank you

  13. Please help!

    I want to put a formula in so that D4 = A4, But if A4 is blank I want D4 to = C4 instead.

  14. Can you please help me to make these formula;

    if I enter :

    yes = 0
    no = -2
    n/a = -1

    Thank you very much

  15. Marks
    10 to 20 = good
    21 to 30 = very good
    31 to 40 = excellent

    formula please ?

    1. =VLOOKUP(C2,$E$2:$G$5,3,TRUE)
      In this example, your data would be in column C. The formula, which will return the value of Good, etc., will be in Column D.
      You will need to create a VLookUp table with 3 Columns. In the formula above, the VLookUp table is in Columns E, F and G.
      Cat Min Cat Max Value
      10 20 Good
      21 30 Very Good
      31 40 Excellent

  16. Plz Suggest function about the following criteria
    If A1 have integer/numeric value then pass 1 and if A1 have string/alphabetical value then pass 2

  17. I WANT THE FORMULA IF A1>0 THEN ONLY FORMULA APPLICABLE IN B2

  18. I am trying to enter a formula based on the following scenario: If I have 3 cells selected in excel and want to automatically have the last cell populate in another cell how is that done?
    Lets say I have cells A, B, C and I have column D for the final estimate. Lets say there is a estimate in cell A and cell B.I do not need the two cells to add up, but I need cell D to only give the total for cell B since that is the last cell with an estimate.
    Basically, I only want the last estimate out of the 3 cells.

    lets say there is a estimate in cell A and cell B

    I do not need the two cells to add up, but I need cell D to only give the total for cell B since that is the last cell with an estimate

  19. >10 lac upto 50 lac Rs 2500/- flat

  20. Dear Sir,
    If calculating late coming
    TIME PERIOD IS MORNING “7AM TO 13 PM, EVENING 16PM TO 21 PM “
    Condition for late coming
    1: If attendance is marked before 7Am and 16 Pm as like after 13 PM and 21Pm is not affected
    2: If attendance marked “after 7Am and 16 Pm” as like “before 13 PM and 21Pm” need to calculated
    3: If employee is absent, there is no attendance, same time need to show the result column as “Blank”
    Just attach the Sheet details
    TIMINGS 1-Feb 1-Feb 1-Feb 1-Feb 1-Feb
    AM PM PM PM IN OUT IN OUT late
    7:00 13:00 16:00 21:00 7:00 14:00 14:00 0:00 0:00
    6:00 13:00 16:00 20:00 0:00 0:00 0:00 0:00 9:00

    Equation : .=IF(G5<C5, C5-G5, )+IF(I5B5,F5-B5)+IF(H5>D5,H5-D5,)
    Please check the second column, there is no date, the late coming column shown the Amount, Need to blank the column

  21. 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!

  22. 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.

  23. 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.

  24. 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.

  25. 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.

  26. 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

  27. 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.

  28. 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!

  29. 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.

  30. 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

  31. 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

  32. 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")

  33. 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"))

  34. =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

  35. 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! :)

  36. 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

  37. 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.

  38. 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

  39. 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?

  40. 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.

  41. 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?

  42. 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)

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

    What do you mean with "figure shown"?

  44. 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"?

  45. 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.

  46. 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"))

  47. 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

  48. 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

  49. 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?

  50. 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

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