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

  1. Hi - need help to have If statement - i.e if cell a1 has a value of #Value!, I want to add the cell content from Y1

    1. Hi Colin,

      Is my understanding correct that you want to add the content from Y1 to A1 when A1 has the #Value! error? If so, you can encslose the formula you currently have in A1 in the IFERROR function, like this:

      =IFERROR(your formula, Y1)

  2. hi,

    I want to do an if function with something like this
    if(40<x<140,5%*5,).
    I tried it but it does not pick those numbers between 40 and 140 for the calculations. the output i get for those numbers are 0.

    please assist. thanks in advance.

    1. Hi Abraham,

      To pick numbers between 40 and 140, you need to embed an AND statement into your formula, for example:

      =IF(AND(A1>40, A1<140),5%*5,)

  3. TABLE 1
    COUNTRIE CAPITAL RESULT
    INDIA LUCKNOW
    USA ITALY
    THAILAND BANGKOK
    THAILAND SPAIN
    INDIA NEW DELHI
    USA AMERICA

    TABLE 2
    COUNTRY CAPITAL
    INDIA NEW DELHI
    THAILAND BANGKOK
    USA AMERICA

    AS GIVEN IN TABLE 2, ARE THE CORRECT CAPITALS OF PROVIDED COUNTRIES AND ACCORDING TO THAT DATA, MATCH THOSE CAPITALS FROM TABLE 1. IF THE CAPITAL IS CORRECT, THEN RESULT SHOULD BE TRUE, OTHERWISE FALSE

    PLEASE HELP ME

    1. Hi Neeraj,

      I have used the data table structure you have mentioned in your post and got the required outcome you are looking for.
      Given below is the formula with the column reference:
      Table 1 with incorrect capitals - column A=Country, Column B=Capital and Column C="True/False".
      Table 1 with correct capitals - Column E=Country, Column F=Capital.
      Formula: =if(iferror(vlookup(A2, E:F,2,false)=A2,"TRUE"<FALSE".
      In the column you can get the True or False depending on whether the right capital has been typed in.
      Let me know is you get this or I can send you a sample template with your data.
      Regards,
      Ramki

      1. Oops, The table with the correct capitals should be Table 2 and not table 1.
        Regards,
        Ramki

  4. if the cell has a vlue it has to take that value or else it has to take next cell value by multiplying *3.75
    Ex-if A1 has a vlue it has to take A1 or else it has to B1*3.75
    Anybody can help to solve this function

    1. Hello Hashid,

      Here you go:

      =IF(A1<>"", A1, B1*3.75)

      1. Thank you Svetlana ..Thanks alot it is very help to my day today work..once again thank you...

  5. I have one table that has a Zip code and other info about a person and also needs a County column filled in this same table. I have another table that has Zip Code with a County connected to it. How would I approach this?

    1. table 1 that needs the county info attached has the following columns.. Zip is H. Where I need the data is N. The other table where im pulling the county info from is.. Zip is column T and county column V

  6. Hi there,

    good day. I am trying to make a date to a number (the month to be number 1,2,or 3), but I don't know how to set the formular, could you please help?

    example:

    2/Jan/16 = 1
    3/Mar/16 = 3
    5/Apr/16 = 4

    Thank you very much in advance for your kind help. Looking forward to hear from you. have a great day.

    1. Hi Joyce,

      You can use the MONTH function for this, and the formula is as simple as =MONTH(A1) where A1 is a cell with the date.

      1. Hi Svetlana Cheusheva,

        Thank very much, wish you have a pleasant day :)

  7. Hi,

    I have a customer list in excel, and am trying to mark each customer as either 'new' or 'repeat'. A new customer is one that appears only once in the list, and a repeat customer appears in the list more than once.

    Can you help with this? Thank you so much.

    1. Hi Mick,

      You can use a formula similar to this, where column A is customer names:

      =IF(A1="", "", IF(COUNTIF(A:A, A1)=1, "new", "repeat"))

  8. Why is this not working?

    =if(G1="",E1,G1)

    However, when G1 is blank "", it is not giving me the value of E1, it is just showing a blank cell?

    When G1 contains a value, it gives me the value of G1

    Am I overthinking this?

    1. Hi!

      Your formula is all right, and you can make sure of this by testing it on a new sheet. Most likely there's some issue with your source data, but it's not possible to pin down the root of the problem without seeing it.

  9. Hi Svetlana,

    Good Day, I am working on two different cells, one cell has 4 categories(james,nadine,clark,leah) while the other cell has 3 options (yes, no, NA). Per category has different values, let say for james, the value of (yes, no, na = 20, 0, na), whilst, nadine has (10, 0, na). This is my formula but it doesn't seem to work:( I know there is too many conditions. I do not know how to simplify it. Thanks in advance and more power.

    =IF(C104="james",IF(D102="yes",10,IF(D102="no",0,IF(D102="NA",D102)))):IF(C104="nadine",IF(D102="yes",20,IF(D102="no",0,IF(D102="NA",D102))))

    1. Hello, Mitch,

      Could you clarify what result you need to get? If no is selected for James, what needs to be displayed in the resulting cell? Thank you.

      1. Mitch,

        Please try this formula:

        =IF(D102 = "NA", "na", IF(D102 = "no", 0, IF(C104 = "nadine", 10, IF(C104 = "james", 20, IF(C104 = "clark", 30, IF(C104 = "leah", 40))))))

        Please modify the values for clark and leah according to your data.

  10. % time exceed ranges from +51% and above 1
    % time exceed ranges from 1% to +50% 2
    % time exceed ranges from 0% to -24% 3
    % time exceed ranges from -25% to -50% 4
    % time exceed ranges from -51% and below 5

    1. can you help me write a formula with these. if like for example cell a1 reach value of +51% and above it gets the value of 1, so on.

  11. Hi, how can i formulate the value of YES to 4 and NO to 0? Meaning, all YES are equal to 4 and NO is 0.

    Thanks

    1. Hi Mervin,

      If you want to input 4 or 0 in some column depending on the value in another column (column A in this example) in the same row, you can use the following formula:

      =IF(A1="YES", 4, IF(A1="NO", 0, ""))

      If you want something different, please clarify.

  12. I am trying to formulate an spreadsheet to determine employee vestment. I need a formula that shows if the date in C2 is two or more years from the current date then D2 will show YES but if it's less than two years from the current date D2 will show NO.

    1. Hello Tiffany,

      Does "the date in C2 is two or more years from the current date" mean 2 or more years in the past or in the future?

      If C2 is a past date, you can use the following formulas:

      To calculate the difference in complete calendar years, use the DATEDIF function:

      =IF(DATEDIF(C2, TODAY(),"y")>=2, "YES", "NO")

      To calculate the difference by subtracting the year in C2 from the current year, use this formula:

      =IF(YEAR(TODAY()) - YEAR(C2)>=2, "YES", "NO")

      If C2 is a future day, you just need to swap today() and C2 in both formulas.

  13. I have a spread sheet for some frequency assessments for the testing of equipment. There are several boxes at the beginning of the assessment sheet where I add in a number and a box at the bottom which gives me a overall score, from all the upper boxes being added together.
    I want to put another box next to overall score box, that will be auto-populated with text and that text will be dependant on what the score is.
    E.g - If score is between 1 - 14, then the box should show the text "3 Yearly".
    15 - 29 = "2 Yearly"
    30 - 44 = "Yearly"
    45 - 60 = "6 Monthly" and
    61 - 75 = "3 Monthly"

    Tried several IF formula, but nothing working.

    Many thanks

    1. Hi Dave,

      You can use the following nested If functions:

      =IF(A1>60, "3 Monthly", IF(A1>44, "6 Monthly", IF(A1>29, "Yearly", IF(A1>14, "2 Yearly", IF(A1>0, "3 Yearly", "")))))

  14. Hello Svetlana,

    I need a Formula with possible 4 options:
    If A1="yes" and B1="yes" then use value(€)C4 in field D1
    If A1="no" and B1="yes" then use value(€)C3 in field D1
    If A1="yes" and B1="no" then use value(€)C2 in field D1
    If A1="no" and B1="no" then use value(€)C1 in field D1

    Is it possible to help me out?

    Kind regards,
    Patrick

    1. Hello Patrick,

      Here's the formula for D1:

      =IF(AND(A1="yes",B1="yes"), C4, IF(AND(A1="no",B1="yes"), C3, IF(AND(A1="yes",B1="no"), C2, IF(AND(A1="no",B1="no"), C1,""))))

      1. Hey Svetlana,

        This is the final formula for D1:

        =IF(AND(A1="ja";B1="ja");C4;IF(AND(A1="nee";B1="ja");C3;IF(AND(A1="ja";B1="nee");C2;IF(AND(A1="nee";B1="nee");C1;""))))

        It won't work with "," only with ";" in the formula.
        That's why I couldn't figure out what was wrong earlier...
        Does it got anything to do with the version (2013) of excel?

        Thanks for your help anyway!

        Kind regards

        1. Hi Patrick,

          >Does it got anything to do with the version (2013) of excel?

          Nope, it depends on what character is set as the List Separator in your Regional Settings (Control Panel > Region and Language > Additional Settings).

          Usually comma is the default list separator in North America and some other countries. On my PC it is also the comma and that is why I separated the arguments with ",".

          In European countries, comma is used as the decimal symbol and the list separator is usually set to a semicolon, which seems to be your case.

          1. All clear.

            Thanks again!

  15. Hi Svetlana,

    Good day.
    One more help please.

    Could you help me, convert (ex: "3days 1hour 25minutes") into minutes. Just to "total minutes" only, so that I can have them rate easily.

    Many thanks in advance.

    Adzhar

  16. Hi Svetlana Cheusheva
    i need some help with one formula.
    i need a formula if i have in cell A1 text "UP" or "OA" or a value <80 to result/display 30 and in the same cell A1 if i have text "STD" or a value <100 to result/display 45
    thank in advance
    Mihai

    1. Hello MIHAI,

      Here you go:

      =IF(OR(A1="UP", A1="OA", A1<80), 30, IF(OR(A1="STD", A1<100), 45, ""))

  17. I am unable to paste the exact command here as there seems to be a problem in your website.
    Basically if the value in B24 is 0-20, it should be excellent, if it is 20-50, it should be OK, If it is 50-100, it should be subjective, If it is greater than 100, it should be "Over Subjective".

  18. Hi, need a formula that will look for numbers either in cells A2 or B2 and if present add the total of A2 and B2 to the number in N1 and show in N2.

    Thank you for your help.

    1. Hi Legat,

      Here is a formula for N2:

      =IF(OR(A2<>"", B2<>""), A2+B2+N1, "")

  19. Hello,

    I want to give If formula,

    in Perticular Cell if 4 result should be 1, if 8 result 2, like that multiple ive to get. Please assist me on this.

    1. Hello GANESH,

      You can use the following nested IF's:

      =IF(A1=4, 1, IF(A1=8, 2, ""))

  20. Hi, can you help me with this please?

    =IF('6'!$K$8>0, '6'!$K$4, "")

    So what I want to achieve is: if K8 in tab 6 is greater than 0, it will show the value in K4 in tab 6, if not blank.

    However it is not working, it doesn't take into consideration whether K8>0 or not, it just shows the value in K4 anyway.

    Thank you and I appreciate your help!

    1. nvm I got it thank you

  21. Hi Svetlana,
    Hope you can help me as I can't find the answer anywhere. Our challenge is for our sick days calendar. We created a spread sheet with 5 tabs from 2016 to 2020. We have changed our system so you can collect only 30 sick days. Some people who have been employed for a long time have more then that and they are allowed to keep adding to their days. So my claculation needs to be if over 30 days keep adding, if they fall under the 30 days in a calendar year they can collect no more then 30 days. Below is my formula for not over 30 but how do I do the first part?
    =MIN(C14+E14-G14,30)

    Thanks

    1. Is this correct>
      =IF(R4>=30,R4+T4-V4,IF(R4=30,0,IF(R4=29,1,IF(R4=28,2,IF(R4=27,3,IF(R4=26,4,IF(R4=25,5,IF(R4=24,6,IF(R4=23,7,IF(R4=22,8,IF(R4=21,9,IF(R4=20,10,IF(R4=19,11,IF(R4=18,12,IF(R4=17,12,IF(R4=16,12,IF(R4=16,12,IF(R4=15,12,IF(R4=14,12,IF(R4=13,12,IF(R4=12,12,IF(R4=11,12,IF(R4=10,12,IF(R4=9,12,IF(R4=8,12,IF(R4=7,12,IF(R4=6,12,IF(R4=5,12,IF(R4=4,12,IF(R4=3,12,IF(R4=2,12,IF(R4=1,12,IF(R4=0,12))))))))))))))))))))))))))))))))

  22. Hope you can help...I'm struggling with this!
    I need the result of cell F12 to be this: If E12 is less than 40 then cell F12 is equal to E12 BUT if E12 is greater than 40 then F12 is equal to E12-40.
    i hope that makes sense!
    Thanks

    1. Hi Michele,

      Here you go:
      =IF(E12<40, E12, E12-40)

  23. So here is my problem:
    =if(E4=L5,G4=M5)
    WHY IS THIS NOT WORKING?
    So what I am really trying to do is I have a chart and on this chart I have 10 - 100 going by 10's so 10 goes with 133, 20 goes with 263, 30 goes with 407...
    so I need to set up a data base where is someone is a 10 another cell automatically shows 133.

    1. Hi,

      I am not sure I can follow you regrading the chart. As for your formula, you can enter this one in cell G4:
      =IF(E4=L5,M5)

  24. How would I enter:

    if cell A is greater than number Y but less than number Z put a 1, otherwise return a blank space?

    1. Hi Mary,

      Here you go:

      =IF(AND(A1>Y, A1<Z), 1, "")

  25. =IF(C6="","",VLOOKUP(C6,C6:D6,2,0)),IF(C1="A","2%",IF(C1="B","2%"))

    please help function not working

    1. note that "" and blank are not the same
      if C6 is formula then it should be working, if not then

      IF(isblank(C6),"",VLOOKUP(C6,C6:D6,2,0)),IF(C1="A","2%",IF(C1="B","2%"))

  26. is there a way to have the data auto fill from A4 into Cell A3 If the data in A1 matches A2?

    1. Hi Venessa,

      You can enter the following formula in A3:

      =IF(A1=A2, A4, "")

  27. Hi there,

    I got the first question solve while I was browsing the above comments, thanks by the way.

    If I may ask again, Could you please help me how to formulate a formula for the following given figures below. I want to calculate the elapsed time in minutes for each row.

    DATE IN TIME IN DATE OUT TIME OUT ELAPSED TIME
    15/03/2015 9:49 15/03/2015 10:30 ?
    15/03/2015 7:00 15/03/2015 13:30 ?
    15/03/2015 8:30 16/03/2015 6:30 ?
    15/03/2015 9:00 22/03/2015 8:30 ?
    15/03/2015 9:30 20/04/2015 6:30 ?

    Many thanks,
    Adzhar

    1. lets say A1 = 15/03/2015 10:30 , B1= 15/03/2015 10:30
      then C1 will be : =((B1+0)-(A1+0))*24*60
      the "+0" trick is really cool once you get used to it, it convert values that have diferent types of representations back to its numeric equivalent.
      Next part "*24" turn the value into hours with floating point and the "*60" turn it into minutes.
      If you get strange value just correct the format in C1 and you are good to go :)

  28. Hi Svetlana,

    Good day. I hope you could help me with my issue.

    I want to give a three different ratings for the following figures:

    If 0 to 200 = A
    If 201 to 800 = B
    If greater than 800 and any numbers with a negative signs = C

    Many thanks in advance.

    Regards,
    Adzhar

    1. Hi Adzhar,

      Here you go:
      =IF(A1>800, "C", IF(A1>200, "B", IF(A1>=0, "A", "C")))

  29. I have set a couple of conditions as follows

    Permanent 299 0 300 Yes(E7)
    Temporary 3 0 100 Yes(E8)

    Final Result: Yes

    Even if one of the records is failing i.e its a "No" then the final result should be "No"

    For the Final Result I have used the following formulae
    =IF(E7="No","No","Yes")

    My question is how do I define a range (E7 to E8)

    In the above formulae I can only select one particular cell i.e E7

    Regards,
    Sachin

    1. Hi Sachin,

      If the range included only two cells, the easiest way is to use the OR statement, like this:

      =IF(OR(E7="No",E8="No"),"No","Yes")

      If the range includes several cells, you can use the COUNTIF function, e.g.:

      =IF(COUNTIF(E7:E16, "yes")=10, "yes", "no")

      Where 10 is the number of cells in the range.

  30. Svetlana,

    Thank you for your continued dedication to this thread and the extremely helpful responses you've given to the users thus far!!
    If possible, I also have a question that you may be able to assist me on.
    I'm utilizing the IF formula as follows:
    =IF(D3=40,"110")
    It goes on continuously basically for a scale that doesn't mathematically progress properly so I'm doing it individually by each number. My question is, when it returns the value of "110", is there a way for Excel to read that as a number and not text? I'm trying to also incorporate a SUM function but it won't read it as an actual number.
    Any insight would be great and thank you again!
    Respectfully,
    Shon

    1. Hi Shon,

      This is because Excel interprets any value enclosed in double quotes as a text string. So, simply remove double quotes and your formula will work just fine:
      =IF(D3=40, 110)

      1. Svetlana,

        Thank you very much for your insightful assistance and valuable time!

        Respectfully,
        Shon

  31. I need some help with a formula I am trying to make in a workbook I use for sports tickets. I have a workbook, with a sheet labelled "Cards 2016" that contains all the games with a unique reference of the word “Open” if the tickets haven’t been sold in col E, If col E = “Open” then I want to enter the date from same sheet “Cards 2016” B3 on to a new sheet labelled "Available 2016". If that cell has anything other than "Open", I just want the cell on sheet "Available 2016" to read "Sold"

    This obviously didn't work but here is what I thought would work. In a cell on "Available 2016" I put this formula =IF('Cards 2016'!E5="Open";'Cards 2016'!B5),("Sold")

  32. hai,
    i want a formula stating if A1=B1 "ok" then A1>B1 "short" A1<B1 "excess"
    can you help?

    1. Try this:
      =IF(A1=B1,"ok",IF(A1>B1,"short",IF(A1<B1,"excess")))

  33. Hi,
    I see you help many people.

    I would like to compare 2 cells and if the difference is greater than 500, I want it to highlight / put a comment.

    IF(columnA-columnB)>500

    is not working.

  34. Hi,

    I am looking forward to creating one formula excel in which if I enter "1" then it chooses USA, "2" chooses Asia. Can you please tell me how to achieve that.

    Thanks in advance,

    Manaf

    1. Use nested IF functions:

      =IF(A1=1,USA,IF(B1=2,ASIA,""))

    2. for Example:
      1 = USA
      2 = INDIA
      3 = UAE
      Regions
      PO Amt Country USA INDIA UAE
      55000 1 55000
      12000 2 12000
      25000 3 25000

  35. Help me write function if .for this table below
    Column A1= Mr
    Column B1= " "
    Column C1= " "
    I want result Column D1= Mr

    Column A2= " "
    Column B2= Mrs
    Column C2= " "
    I want result Column D2= Mrs

    Many thank for your help.

    Column A3= " "
    Column B3= " "
    Column C3= Ms
    I want result Column D3= Ms

    1. Hi Sem,

      You can use the following nested IF functions:

      =IF(A1<>"", A1, IF(B1<>"", B1, IF(C1<>"", C1, "")))

  36. Hello,
    I am trying to use an if function and have the following:
    =IF(H6="E6/7","","Reset Required")

    However, I haven't been able to figure out how to have the cell return to and empty cell instead of it coming up "reset required" when items in column H are blank? Can you help me?

    1. Hi Rachel,

      Just add one more condition to your formula, like this:
      =IF(OR(H6="E6/7", H6=""), "","Reset Required")

      FYI, if E6 in your formula is a cell reference, then you shouldn't enclose E6/7 in double quotes, otherwise Excel interprets it as a text string.

  37. Hi,

    I'm trying to generate a formula that will list a "last completed" date (from column E) for each of the categories in column A.

    =MAX(IF(A3:A100=$I$2,E3:E1000=DATEVALUE("mm/dd/yyyy")))

    Column A: Column E: Column H: Column I:
    1 [title]
    2 Children's DVDs 7/13/2015 Adult fiction books
    3 Adult Biographies 10/7/2015 Last Completed:[formula here]
    4 Adult Fiction Books 11/18/2015
    5 Adult Fiction Books 12/12/2015
    6 Adult Biographies 12/13/2015
    7 Children's DVDs 12/19/2015 ...

    I'm not very good with multiple criteria formulas, so I know something is off.

    Thanks!

    1. oh, the spacing got screwed up.The titles are under column A (Children's DVDs etc.) The Dates are under column E. Column H has the "last completed" and Column I has the actual formula. Sorry.

  38. IN EXCEL SHEET THERE IS A CELL A1,B1,C1,D1,.....
    IN CELL A1 IS 50
    IN CELL B1 IS 50,
    IN CELL C1 IS A1+B1
    IN CELL D1,IF THERE IS OPTION LIKE YES OR NO,IF I PUT YES IN CELL D1 THE VALUE OF C1 SHOULD SHOW,IF I PUT NO IN CELL D1 THE VALUE OF C1 SHOULD NOT SHOW.
    CELL D1 SHOULD OPERATE CELL C1.
    PLEASE GIVE ME THE FORMULA FOT THIS.

    1. Type this in C1:

      =IF(D1="YES",A1+B1,"") -> This way, anything other than "yes" in D1, will not show anything.

      If you want something else to show in case theres neither "yes" or "no" in cell D1, let me know because you'll need a nested IF formula.

      1. THANK YOU
        BUT FOR ADDING THIS IS OK, BUT IF I NEED TO MULIPLE OR SUBRACT
        WHAT I SHOULD DO
        FOR EG:IN C1 =A1/50*B1

        WHAT SHOULD I DO

        1. SALMAN,

          Just replace A1+B1 in the formula with any other calculation you want, e.g.:

          =IF(D1="YES", A1/50*B1,"")

  39. If I enter cell A1 date C1 should calculate and show number of days pending, if enter date in B1, C1 should show message completed, if A and B1 empty C3 show Don't worry massage.

    1. Hi Manju,

      > If I enter cell A1 date C1 should calculate and show number of days pending

      How exactly shall the formula calculate the number of days pending? Is it a difference between the date in A1 and today's date? Shall it be a past or future date, or either?

  40. I need to know the formula if you enter value ex. in cell b2 and the output in b3 will be the date today

    1. Henry,

      Our blog engine has problems with < and > symbols, sorry for this.

      Nana,

      Here's the complete formula:
      =IF(B2<>"", TODAY(), "")

      1. Thank you! lol

        1. THANKS SVETLANA

    2. Put this in B3:

      =IF(B2"",TODAY(),"")

      1. For some reason the formula above didnt show up correctly, but between B2 and "" you got to include

        1. OMG it isn't showing lol
          you got to include the "not equal" sign

          1. THANKS HENRY HELPS A LOT..... :)

  41. Hi,

    I need to make cell 'BLANK' if data less than 0.
    I work on daily precipitation data, if precipitation value for day j is missing the value equal to (-999), for this I want cell becomes empty for all the data below zero.
    thank you in advance for any help you provide.

    1. You have to open Excel Options, Formulas, and Enable Iterative Calculations.
      then you put in the cell you want to make blank if below zero (for instance A1).

      =IF(A1<0,"",A1)

  42. Hi i want in one raw if value <3 then give me value 0 if <2 then give me 1 and if <1 then give me 2 and if blank give me 3 in one formula so that i can drag same formula in entire excel sheet

    1. Hi Dhirendar,

      You can use nested If functions as follows:

      =IF(A1="", 3, IF(A1<1,2, IF(A1<2,1, IF(A1<3,0,""))))

  43. Hi ,

    I am trying to create a formula if the sale days are less than 100 to take the name of the sale item

    1. Can you send me a screenshot of it so I have a better idea of how your table is set up?

  44. Hello,
    I am trying to create a formula for:
    CellA: $amount CellB: Store Number
    How can I make all cells with the same store number to add up as the amount is
    being inputted into the table.
    Thank you please help

    1. I'm not sure I completely understand your question

  45. Hello,

    I would like to write a formula to give me alarm by changing the cell to red color and the formula is about the following:
    If the amount in Cell (AE300) is bigger than zero and the date is 30 days before the date in cell (G300), then change the cell (AE300) color into red.

    Thanks in advance.

    1. What cell are you taking the first date value from, to compare to the date in G300?

  46. basically i do not want have a value over 1, if it comes out over i want it to be 1. if it is under then whatever the number is fine.

    1. =IF((C6/D6)>=1,1,(C6/D6))

  47. Hi Svetlana
    Imam kolona B so brojki od pravecot na veterot( vo stepeni) od 0 do 360. Vo kolona C treba namesto brojki da stoi samo znak so strana na svetot ( I,Z,S,J,SI,SZ,JI,JZ). Probav so slednata formula ama nekade gresam.
    IF(OR(B2>=337;B2=23;B2=68;B2=113;B2=158;B2=203;B2=248;B2=293;B2<=336);"NW"))))))))
    Molam za pomos. Fala odnapred.

    I have a column B with numbers of wind direction (deg) from 0 to 360.In column C How to change number to data ex: if wind direction is 0 = N, 46=NE,....

    1. =IF(OR(B2>=337;B2=23;B2=68;B2=113;B2=158;B2=203;B2=248;B2=293;B2<=336);"NW"))))))))

  48. Hi,
    i want to use the function to write a value in another cell whether the result is true or false.
    For example: if cell (A1+A2)>3; true: A3=4; false: A3=0
    Is it possible or it is with another function?
    Thanks in advance!

    1. If you want the cell A3 to always display 4 in case its true, the formula would be:

      IF((A1+A2)>3,4,0)

      Now, if you want the cell A3 to display the result of A1+A2, then the formula would be:

      IF((A1+A2)>3,(A1+A2),0)

  49. Hi,

    So I have two tables. Table 2 is named "Servers" has two columns. One column with a list of databases and the column next to it with the list of servers those databases are located on. Table 1 is for data entry. I want the cells in Table 1 to auto populate the associated server name when the database name is input.

    I'm an excel noob I'm sorry.

    =IF(H72=(*a specific value found somewhere in column C in table "servers",*the value in the column B in the same table, *if no value exists remain blank))

    I hope you understand.

    1. Nevermind I found it!

      =INDEX(Servers!A:A,MATCH(H64,Servers!C:C,0))

  50. hi team,

    how can I get the total number of "yes" and "no" in excel sheet ?

    For eg:- I have used Column B3 to B35, this can be Yes and No according to jobs closed.

    1. Hello Kullan,

      You can use the following COUNTIF formulas:

      =COUNTIF(B3:B35, "yes")
      =COUNTIF(B3:B35, "no")

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