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

  1. i have 3 conditions
    1. if A1-cell value is a +ve value then result in B1 as "Excess"
    2. if A1-cell value is a -ve value then result in B1 as "Short"
    3. if A1-cell value is a 0 value then result in B1 as "nul"

    1. ve? =IF(A1>0,"Excess",IF(A1<0,"Short",IF(A1=0,"null")))

  2. IN A COLUMN I HAVE "1,2,3" AND I WANT TO COVERT THE NUMBERS TO "WORKERS,CLEANERS,DRIVER". BECAUSE THAT IS WHAT THEY STAND FOR AND ALL THE OTHER COLUMNS HAVE TWO OR THREE NUMBERS THAT REPRESENT THE WORDS GIVEN ABOVE. THANK YOU.

    1. Hi, Grace,
      if you want to change the data in one and the same cell and you don’t want to do it manually, you need to use a slightly different Excel function – Replace. It's very easy to use, and here is an article that will help you with the task:
      How to replace one value with another
      But if you have a huge table with tons of values to be replaced, you can try VLOOKUP function.

  3. Hello since I see new posts I will ask. I have tables which I am entering numbers into that total different each time I enter new numbers and total the sheet.

    These numbers for example let's say 11+11+11+11 = 44 are then totalled into a new cell and added with +3. Okay so far I have a number in the example of 47.

    I want to use your formula with the number 47 (remember this will change) that I just generated to recall a value with it.

    Example) On page 2 I have values for each number.
    11 100
    20 300
    35 600
    47 750
    etc

    I want to get the formula to upon result of my number (say 47) to then pull the value associated with that. I've tweaked it to say on a specific value of 47 to pull the 750 into the cell, but that is by manually entering the one value. I need it to do a range of values in one forumla to pull from the list.

    Is this possible? or am I in the wrong formula.

    Thank you

    1. Hello, Dean,

      you were looking for a formula in the wrong article, because you need not IF, but VLOOKUP function.
      For example:
      on 'Sheet7' in A1:A4 we have the numbers. In B1 we put:
      =SUM(A1:A4,3)
      to sum them up and add 3.
      Then in C1 we use the next VLOOKUP function:
      =VLOOKUP(Sheet8!A4,Sheet8!A1:B4,2)
      to return the value of the corresponding number from 'sheet8'.
      'Sheet8' contains a little table with the possible results in A1:A4 and their corresponding values in B1:B4.

      Hope you won't have same troubles in the future!

  4. I'm trying to figure out how to write a formula to compare mileage from two columns. I'm tracking service intervals on fleet vehicles. when a current mileage is within 1,000 miles i want it to turn yellow. if its within 100 miles i want it to turn red. can anyone help me please. thank you in advance

  5. Am I able to have text in one cell determine if a number in another cell is negative or positive?

    If cell A1 says "withdrawal" I would like the number in cell B1 to be negative. If cell A1 says "deposit" I would like the number in cell B1 to be positive.

    1. Assuming, that column B already contains positive numbers, the next formula will do:
      =IF(A1="withdrawal",-B1,B1)

  6. Hello,

    I can't figure out what's wrong with the following:

    =IF(AK2=1, AB2*365,IF(AK2=2, AB2*52,IF(AK2=3, AB2*12,IF(AK2=4, AB2, 0))))

    Column AB is 'number of times' a person does something and column AK is 'during which time period' where 1=per day, 2=per week, 3=per month and 4=per year. Now, with this forumla, I'm trying to standardize these into single variable 'times per year' in order to calculate averages etc.
    Excel just doesn't understand my formula although to me it seems what I see in numerous examples.

    1. Ha! figured it out. Instead of comma, I have to use semicolon.
      Does anyone know where that comes from? Actually, I recall having this trouble earlier and spending hours trying to figure out what's wrong. Unfortunately my memory is good but short. Maybe next time...

      1. Where? Do you mean every comma has to be changed to semicolon?

        I have not tried, but I think we may get syntax error.

  7. Hello,
    Need some help on multiple conditions.

    IF A1 = A, D & M
    True = Fill up form A
    False = Fill up form B
    (Which A1 consist of A-Z)

    1. Hello, Patsy,
      please, give us more details on your task, so we could assist you.
      Do you want to fill the forms with colour or some value? What do you mean by "Which A1 consist of A-Z"?

  8. Hi
    Please can you help.
    I have a holiday formula see below, that H = 1 days, but I also need it to show if the member of staff decides to take a half day. I can't get the formula to work?????

    COUNTIF(C12:AG12,"H")

    Thanks in advance.

    1. Hi, Lynsey,
      if half day is 'HD', then the formula will look like:
      =COUNTIF(C12:AG12,"H")+COUNTIF(C12:AG12,"HD")
      But it will return the sum of both: complete and half days taken. If you want them to return two results separately, put each part of the formula in different cells.
      To learn more about how this formula works, read this article.

  9. I need help creating a formula in cell E120.
    If E1190,0
    I would like the formula to return the percentage of X.

    2. Not sure, why my information keeps getting cut off. I'll try again....
      If E1190,0. I would like the formula to return the value of X%

  10. I want to create YES for a number of columns when there is a nonblank in any of them. I tried the following, but it does not produce the correct result. Advice?

    =IF(ISBLANK(D33:O33)="","Yes ","No")

    1. Hello, RoseG,

      since ISBLANK is used for one cell only, try using this:
      =IF(COUNTIF($A2:$H2,"")=0,"No","Yes")

      For more info on the COUNTIF go here.
      Hope this helps.

  11. if A2 is between 501 to 1000 return value 20 and if A2 is between 1001 to 2000 return value 30.

    Example :
    Order value Charges
    0-500 10
    501-1000 20
    1001-2000 30
    20001< 40

    How to write formula to get this results ?

    1. Example :
      Order value -- Charges
      0-500 --------- 10
      501-1000 ------ 20
      1001-2000 ----- 30
      20001< -------- 40

  12. Hello I need help with a formula. For example if the Cell A1 reads a number less then 20 I would like that cell A2 to read what ever the number on A1 + 1. But if it reads more then 20 I would like to come back with A1+2. Did I explain my self correctly.

    1. Hello, Jonathan,

      I believe, this will help:
      =IF(A1<20,A1+1,IF(A1>20,A1+2,""))

      Note, that you didn't mention what should happen if the number is equal to 20, so in that case A2 will remain empty.

  13. I have a question, i have to check Year of Birth from 1 cell from another cell which has only Year of birth.
    EXample
    Cell A1 is 01/01/1979
    Cell B2 is 999-1979-9999
    I am trying to use the formula =if(mid(b2,5,4)=year(A1),"True","false")
    However all my values return as FALSE?
    Any advice?

    1. Hello, Kristoffer,

      as it is stated in the article, the MID function always returns a text string, even if the extracted substring contains only digits. To convert an output into a number, use MID in combination with the VALUE function. Your formula then will look like this:
      =IF(VALUE(MID(B1,5,4))=YEAR(A1),"True","False")

  14. Can anyone interpret this formula. It works but I'd like to understand what it means. It's in a document that I use.

    =IF(ISBLANK(F27),"",SUM(G26-F27))

    Thanks

    1. If cell F27 is blank, then return a blank cell. If F27 is not blank/has a value, then return the value of cells G26-F27.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  35. Hello sabrina,

    Please use below text formula to check diffrence between 2 dates insted of datedif:-

    =TEXT(C22-B22,"yy") (this will come in d22)

    then type in next coloumn =IF(D22<5,"80 Hrs","120 Hrs")

    Enjoy ...!!

    1. Hi Peshiyaboy,

      Regrettably, this simple solution won't work because the result of the TEXT function is always a text string, even if it looks like a number. Consequently, the IF formula won't work correctly.

  36. Hello, I am having some trouble with using the IF function to calculate the vacation entitlement of employees with a certain tenure.

    I would like to stipulate that an employee whose tenure is less than 5 years would get 80 hours of vacation, but if 5 or more years, would get 120 hours of vacation.

    On my spreadsheet, column D displays the tenure. NOTE: the tenure is not a straight number, but a calculation made with a =DATEDIF function.

    The below formula is what I've been using:

    =IF(D2<5, "80", "120")

    However, the answer always ends up being 120 even if the value in column D is less than 5 years. Any assistance in helping me correct this would be greatly appreciated, and thank you in advance!

    1. Hello Sabrina,

      Most likely, the value in column D is a text string that looks like a number, and it is the source of the problem. To be able to say with certainty, I need to know your formula in D2.

      BTW, the result of your IF formula will also be a text string because you enclosed the numbers in quotation marks, and therefore you wont't be able to use those "text-numbers" in other calculations. So, you'd better put it this way: =IF(D2<5, 80, 120)

  37. I have this formula so far:
    =IF(B4>=1000,1,"0")

    But I want that if B4 is greater or equal to 1000 then it gets 1 point for every 1000 or 0 if less than 1000.

    please help!!

    1. Hi Khush,

      Try this one: =IF(B4>=1000,INT(B4/1000),0)

  38. How I do a formula in excel (column C) to calculate the grade of the persons using the following data.

    A B C
    Name Points Grade
    X 57 F
    Y 88 B
    X 95 A
    W 73 C

    Grades
    A = 90 - 100%
    B = 80 - 89%
    C = 70 - 79%
    D = 60 - 69%
    F = 0 - 59%

    1. Hi luis,
      Plz use below one and replay if u have any doubts...

      =IF(AND(B2=90),"A",IF(AND(B2=80),"B",IF(AND(B2=70),"C",IF(AND(B2=60),"D","F"))))

      1. HI... check below

        =IF(AND(B2=90),"A",IF(AND(B2=80),"B",IF(AND(B2=70),"C",IF(AND(B2=60),"D","F"))))

        1. Sorry to say that .. this is not pasting my orginal formaula,...
          i have formula.. but unable to give... while pasting it missed to give some strings...

          =IF(AND(B2=90),"A",IF(AND(B2=80),"B",IF(AND(B2=70),"C",IF(AND(B2=60),"D","F"))))

  39. I am trying use an IF and OR function. The formula below works perfectly, but the one below that (where the cell A1 is replaced with the words) doesn't work and comes back with #VALUE.

    =IF(OR(L6="Research",A1),"","N/A")
    =IF(OR(L6="Research","Sales"),"","N/A")

    an anybody help??

    1. I have been able to use your answer for Keith on 2nd December, thank you.

      Answer:

      =IF(OR(L6="Research",L6="Sales"),"","N/A")

  40. Hi, I am trying to display the value in the next column if a value is found from a range. So, I have one workbook with three worksheets.

    The first worksheet has a list of ID's numbers and vendor names. Vendor names would repeat in this sheet and the customer ID could repeat if they use more than one vendor.

    The second sheet has a distinct list of the vendor names along with a tier rating (1,2,3,4).
    I am attempting to lookup the vendor name from sheet 1, find it in the vendor name range from sheet 2, and when found, populate the tier rating.

    To take it a step further, though I could do this function separately after I have the first done, I have a 3rd tier that includes a point value associated with the tier rating. I'll be looking to populate the point value for each row in sheet 1 based on their tier.

    Any help is appreciated. I am caught on how to display the tier value since I am not simple doing a yes or no of whether the text is found, but needing to display corresponding information when the text is found.

    1. I think I need to use a vlookup, but I am just not have any luck.

      1. A little funny with my repeated post, but I did solve my issue of generating the tier using an index/match formula.

  41. Is there a way to code a function in this example?
    IF a cell contains 1 word (of 3 choices) in A1, then I want the value in B1 to appear in C1.

    1. Hello Keith,

      You can enter the following formula in C1:

      =IF(OR(A1="word1", A1="word2", A1="word3"), B1, "")

      1. How can i do this for multiple cells and add them all together?

  42. Quick Question:
    I wanted to match last names from two excel sheets, then select the values (DOB) to be ported to the other excel sheet. What would be the formula?

  43. Hi can anybody Correct this Condition

    =IF(A3180,(A3+180),IF(A3>540,(A3-540),"")))

    1. Hello Pawan,

      Probably you meant this:
      =IF(A3>540, A3-540, IF(A3>180, A3+180, ""))

  44. I have four values in four columns. If first column's value is greater than 0 and the second colummn is equal to zero and third column is greater than 0 and fourth column is equal to 0, i want to type pass in the fifth column. Else fail. Can anyone help to write a formula for this?

    1. IF( AND( $A1>0, $B1=0, $C1>0, $D1=0), "Pass", "Fail")

  45. Hi there, bit of a tricky one here. This works fine, but i have to do this for 11 colums, is there a way so shorten this code?

    ="Port: "&IF(AND(G2>100,G2100,G3100,G4100,G5100,G6100,G7100,G8100,G9100,G10100,G11100,G12100,G13100,G14100,G15100,G16100,G17100,G18100,G19100,G20100,G21100,G22100,G23100,G24<500),"23,","")

    1. ="Port: "&IF(AND(G2>100,G2100,G3100,G4100,G5100,G6100,G7100,G8100,G9100,G10100,G11100,G12100,G13100,G14100,G15100,G16100,G17100,G18100,G19100,G20100,G21100,G22100,G23100,G24<500),"23,","")

      1. ="Port: "
        &IF(AND(G2>100,G2100,G3100,G4100,G2<500),"A2,","")

        And if possible to shorten this, because writing it out for every row 23 time s is a drag

  46. =IF(BD2,"Not a Target", "Not a Target, BD2&": "&BC2)

    I'm not sure what I am doing wrong? Any ideas?

    1. Hi Sarah,

      The syntax of the Excel IF function allows 3 arguments, while your formula has 4. If you can provide some details about the source data and expected result, I will try to help.

  47. I'm creating time sheets in Excel. I would like for a cell to automatically populate "8" if another cell contains any one of several dates. I am able to use the following formula successfully for one date:

    =IF(O9=DATEVALUE("12/23"),8,"")

    However, I would like to use multiple dates. For instance, show "8" if cell O9 contains 12/23, 4/14, or 11/24. Otherwise, leave the cell blank.

    Any ideas?

    1. Sorry this a cut and paste from me elsewhere. Check if the cell does/does not exist in a named range of cells containing your exception dates.

      Hi, sorry I don't have a spreadsheet in front of me to syntax check.

      Eg.
      IF( OR( A1="Football" , A1="Basketball", A1="Hockey"), "Sport" , "Something Else" )
      -OR-
      I often flip my IF statements to check for Errors or Exceptions first.
      Eg.
      Define an area in Formula Tab, MySportList=$L$31:$L$59
      Fill these cells with your sports.
      IF( ISERROR( VLOOKUP( A1, MySportList, 1, false)), "Something Else", "Sport")
      # IF TEST is, if you get an error when trying to lookup the contents of A1 from your list of Sports in cells, L31:L59, then display "Something Else", else display "Sport".

  48. if A1 is 0 print 0 and if B1 is 0 print 0 and if ((A1 and B1)>0) print =A1+B1

    1. IF( OR( A1=0, B1=0), 0, IF( AND( A1>0, B1>0), SUM(A1+B1), "A or B is less than Zero")

  49. i want to create a formula where =IF(D8"","+8","OFF") where if not blank add 8 and if blank put off. But the formula is not given me the required result

    1. Hello Emmanuel,

      Here's the correct syntax:
      =IF(D8<>"", D8+8, "OFF")

  50. I have a date format in column e and in column f I have put a formula for it to automatially update it to 3 years later by adding 1095 to column e. Now, I am trying to get column a to turn red 60 days before column f is mature. I have tried conditional formatting but for some reason it doesn't work.

    1. Hi, sorry I don't have a spreadsheet in front of me to syntax check.

      Checks:
      Column E has numbers that display as text and not text "12/09/2020".
      Column F should be the formula =E1+1095 and display 12/09/2023.
      Format:
      Condition Formatting on Cell Range =$E$1:$E$51 as a formula option.
      IF( TODAY() >= (F1 - 60), TRUE, FALSE)

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