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 27. Total comments: 2999

  1. Could you help with this formula?
    "J21" = "%" and "M21" = "%" "K18" is the result but the are 3 answer.
    J21 is > than 0
    M21 is > than 0
    J21 & M21 are > than 0 And needs to be
    / by 2
    Hope you follow what I am looking for
    Thank You

    1. in col write a formula to give rank to student based on below table(without using if condition)

      marks:=33 but less than 60,>=60 but less than 70,>=70 but less than 80,>=80 but less than 90,
      >=90

      grades:= <33,"Fail",<60,"pass",<70,"3rd div",<80,"2nd div",90,"distinction

      plz solve this question

    2. Hi Gene,

      I understand that you have some percentages in cells J21 and M21, but all the rest is befogged.

      Can you please try to formulate the task in the following way?

      If J21 >0, then we do what?
      If M21 >0, then we do what?
      If J21 and M21 >0, then we do what?

      1. If J21%>0 then put % in K18 as that %
        If M21%>0 then put % in. K18 as that %
        If M21 & J21 %>0 then devied by 2 then put
        That % in K18
        Only 1 of the 3 formula's can be true at a
        Time and it's % in put in K18
        Hope this is better for you to see what I want
        Thank you

        1. As I understand, it should be like this

          in cell(k18)=if(and(j21>0,m21>0),(j21+m21)/2,0)

  2. I'm trying to figure out a formula that will compare two columns "dates" and then produce the greater value of the two columns in the third column. Can you please help?

    1. Hi Shannon,

      Assuming that you have dates in columns A and B, the formula is as follows:
      =IF(A1>B1, A1, B1)

      1. Hi,

        I read above that excel doesn't recognize dates unless wrapped in DATEVALUE.

        I had the above formula that you gave and it doesn't always work. In my case one of my columns could possibly have a 0 or be blank.

        My formula is in column A my date columns are D and G. So I had

        =IF(G2>D2, G2,D2)

        G is the column that sometimes could have a 0 or be blank (the data is pulled from another system.

        Also, my formula doesn't recalculate once I update the data in column G. Any thoughts? I have auto calc turned on.

      2. Hi,

        I Have a Data My software needs to work 99.99% availability when measured on Monthly basis If the downtime exceeds 60 min in a month for every 30 min Penalty should be calculated on Quarterly Payment of rs 10000 can u design a methodology with formula with calculated output.

  3. =IF(A2=5, "OK", "Wrong number")
    BUT I WANT TO VALUE OF A1 CELL AT "Wrong number" POSITION

    1. I am attempting to devise a formula that will assist me to calculate the payroll for my employees. It needs to address number of hours worked & then various rates of pay for those hours.

      So far I have come up with the following:

      =IF(AJ6>=3,(AJ6-3)*AN6+(3*AK6))+IF(AJ6>=3,(AH6-AJ6)*AI6)+IF(AJ6=3 - in the first instance I need a 0 recorded if cell
      AJ6> cell AH6
      - should that not be the case then I need the following calculation to take place (cell AH6 - cellAJ6)*cell AI6 (where cells AH & AJ are hours worked & cell AI is a rate of pay.

      My many attempts have been close but do not seem to recognise the IF/OR component - I need this because if it calculates a negative number as it can in the first instance it decreases the pay of my employees.

      Can someone out there help me?

    2. Hi!

      If my understanding is correct, you simply need to replace "Wrong number" with A1 like this:
      =IF(A2=5, "OK", A1)

      The formula returns "OK" if A2 is equal to 5, otherwise it picks a value from cell A1.

      If you want something different, please clarify.

      1. =IF((Data!$D$11="Concrete"),SUM((SUM($B$40*9)/1.3)*0.865/Data!$B$26),SUM((SUM($B$40*9)/1.3)/Data!$B$26))

        I am trying to use the above if statement to calculate a value based on the value selected in Data!D11. The choices in a drop down D11 are Concrete or Frame. My logic being if D11=Concrete then do the first formula but if false (Frame) do the second formula. When Concrete is put into D11 this works correctly but if Frame is selected the value is not correct. I can put the second false formula in another cell and it calculates correctly. Can someone tell me how to fix this.

        Rick

      2. IF A2's value = O then take date from B2 And IF A2's value = D then take date from C2 And IF A2's value = K then take date from D2

        1. Hi Sveta,

          I am doing a pivot table from a sheet which contains formula, but the pivot table always counts the supposedly blank cells but with formula, is there anyway to avoid this?

        2. Hello AJAY,

          Here you go:
          =IF(A2="O",B2,IF(A2="D",C2,IF(A2="K",D2,"")))

          1. Dear Sir,
            I am very kind, that the formula is fantastic , i was in big trouble when i check this formula and its perfectly working. thanks a lot for your help.

            best regards.

          2. Hi
            I want know the formula which applies only when value is above 0

          3. I am working in an excel file right now.

            I wanted to show that when either of the two (2) columns have answered yes, it will be counted as 1.

            Example. A1 has answered yes while B1 has answer no; therefore, the value will be 1.

            Need your help on this.

            1. Hello, Joboy,
              try this formula:
              =IF(OR(A1="yes",B1="yes"),1,"")

              Note, that if neither of the cells have "yes", the cell with the formula will remain empty.

          4. Do you help me? How to create tables in excel?

          5. If in place of Date in B2 there is alphanumeric text, then?

            1. Hello Arnab,

              It does not make any difference, the formula pulls any value from B2.

              1. i need to find the maximum value and to show its time from the column of time. each value has got its specific time which is on the same row but different columns.

                A formula please.

                Thank You in Advance.

              2. Hello Japhetmunnah,

                This can be done using a VLOOKUP formula, like this:

                =VLOOKUP(MAX(A1:A10), A1:B10, 2)

                Where A is the column where you look up for max value, and B is the column containing times, 2 is the column number (col_index_num) in table array from which the value should be returned.

          6. It worked! Thank you Svetlana

      3. if only you'd be my professor

      4. Hi, I want to enter a formula that will give me a result only if there is a value > 0 in a row

        i.e. if A1=0 then give me B1. But if B1=0 then give me B2 etc.

        Can anyone help? I've got this so far, but it just gives me 0:
        =IF(R2=0,S2,IF(S2=0,T2))

        1. =IF(OR(A1=0,B10),B1,B2)

          This is your solution mate :)
          enjoy it

          1. How can I get a sum on page 1 cell D23 of numerical value from page 2 cells in column D if equal to specific text in column J?

        2. :)

        3. I work at a school, and i manage the book shop over there, so i want to keep my record straight for my boss, i forget things so fast :) and also my purchase record, thats why i want to set a formula to fastly enter. i deal with notebooks, diaries and artpads, so I want a formula for text and numbers both, for example if i write 'D' in a cell, the formula will write the amount of '55' for a Diary, and so on for notebook, i mean if i write 'N' for notebook, the formula will write the amount '50' for me. I just want to set that formula. So i can deal with other customers also.

          1. =IF(A2="D","55",IF(A2="N","50"))

        4. =if(L4,"Half Yearly,180,If(L4,"Quarterly",90,if(L4,"Yearly",365)))

        5. =IF(OR(A1=0,B1,B1=0),B2) u try this.

          1. =IF(OR(A1=0,B1),IF(B1=0,B2))

            1. How do I get to extrapolate for if A#=0, B# without just typing =IF(OR(A1=0,B1),IF(A2=0,B2),IF(A3=0,B3),IF(A4=0,B4)) and on and on

            2. I have 2 cells within Excel that I would like to apply the if function to, but I am not sure how to setup it up properly. Right now it reads:

              =IF(T11=R13,"Ok","Check Total")

              But, I want the formula to take in account if T11 is less than or greater than R13 by "5 units or 5 increments" to indicate "OK" not just equals to. How would I setup my formula?

              Thank you in advance!

              1. =IF(OR(T11R13),"","")

              2. Check Below Formula Once
                =IF(OR(T11R13),"5units/5increments","Ok")

              3. I have 2 cells within Excel that I would like to apply the if function to, but I am not sure how to setup it up properly. Right now it reads:

                =IF(T11=R13,"Ok","Check Total")

                But, I want the formula to take in account if T11 is less than or greater than R13 by "5 units or 5 increments" to indicate "OK" not just equals to. How would I setup my formula?

                Thank you in advance!

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