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 29. Total comments: 4822

  1. Hi, I've been reading your article and i must say it's outstanding. However though it was useful in some areas I still found myself stuck.
    I've been trying to insert an if function
    =If(G2>=0.5,C2,"APPROVE", "DISAPPROVE")
    But its not working
    Any Help is appreciated

    1. Hello Cresia!
      Unfortunately, you did not write what result you wanted to receive. Perhaps your formula should be written as follows:

      =IF(G2> = 0.5 * C2, ”APPROVE”, “DISAPPROVE”)

      I hope my advice will help you solve your task.

  2. Thanks for the article.
    I need help with writing out a formula that says, if column B = yes and column C = yes, then D should say True.
    So far I tried:
    =IF(B2="YES",TRUE(IF(C2="YES",TRUE),False))

    Looking forward to hearing from you.

    Rgs

  3. Hi
    I would like to return a value to be 1 if the division of the two cell equals or greater than 1 and .5 if it's less than 1
    =if(c5/d5>="1","1",".5")
    I inputted the formula above but it's not showing the proper value..
    Hope it makes sense. Much appreciated, ty!!

    1. Hello Priscilla!
      If I understand your task correctly, the following formula should work for you:

      =IF(C5/D5 >= 1,1,0.5)

      I hope this will help

  4. i have "NEFT/BKIDN20137329397/GCHZ0000M12104/ " in one worksheet and "BKIDN20137329397" in other workbook with some values. how to use if formula to bring these values in first worksheet.

  5. Hello
    I am struggling with an IF formula, hopefully i have explained it clear enought.

    I want to return a value to cell V1 (the cell i am entering the formal into), if the value of cell U1 is greater than 0.1. The value i want to return is in cell T1, if the value is less than 0.1, i want to return a value of 0.

    Any help would be much appreciated

    Thank-you

    1. Hello Sarah!
      If I understand your task correctly, the following formula should work for you:

      =IF(U1 > 0.1,T1,0)

      I hope this will help

  6. Hello Mathew
    How can I delete a group of numbers that have same last digit from a list.
    For eg: 10, 22, 31, 32, 35, 38, 40, 42, 44, 50, 51, 52, 62, 63
    From this list numbers have last digit '2' are to be deleted. 22, 32, 42, 52 and 62
    I appreciate any help.
    Thank you.

  7. Hello,
    I am struggling with my nested IF formula. I am trying to return a "Y" or "N" if the given date meets certain requirements but sometimes strings of text can be found in the cell. If the string of text matches I would like it to return "N" or "Y" based off the requirements. Any help would be greatly appreciated.

    =IF(EDATE(A5,12)<TODAY(),"N",IF(A5TODAY()+30,OVERDUE,IF(SEARCH("OVERDUE",A5),"N","Y"))))

    1. Hello Matthew!
      If I understand your task correctly, the following formula should work for you:

      =IF(EDATE(A5,12)

  8. Hello,

    I have some dates in column K that I periodically add to or update, also some blanks that don't have dates added yet. I would like a formula for column L that gives dates 6 months in the future if there is a date in column K. However, if there is no date (blank cell) in column K, I would like the corresponding L cell to remain blank until a date in inputted into the K cell. Hope that makes sense! I appreciate any help.

    1. Hello Teresa!
      If I understand your task correctly, the following formula should work for you:

      =IF(K1 <> "",EDATE(K1,6),"")

      After that you can copy this formula down along the column.
      Hope this is what you need.

  9. I cant find what the problem is with this simple thing
    =IF(BASE!MONTH=ENGINE!B1,"yes","No"

    1. Hello Herman!
      You did not indicate exactly which error occurs in your formula. However, MONTH is a function and cannot be a link. Therefore, BASE! MONTH does not make sense and is a mistake.
      The formula lacks the brackets at the end.
      If you give more complete information, perhaps I can still suggest something else.

  10. I have a row of cells which have values in for lab results - I can't figure out a formula to work out a minimum value where values are either numbers or come as say <0.05 etc. i.e where they are less than the laboratories limit of detection (<LOD). I want the minimum number so i want it to register that <0.05 is less than 0.06 either through the presence of a less than symbol or any other way. I tried to play around with any cell being greater than 0 calculate a minimum and for where cells do not to populate as"<LOD". i.e. 1.9 , < 0.05, 0.29 would return a minimum of "<LOD". 1.9, 0.06, 0.29 would return a minimum of 0.06 and < 0.05 < 0.05 < 0.05 would return a minimum of "<LOD". Any help would be greatly appreciated - I can't find any examples where you have an if statement followed by a function if the statement is true, and to present a text value if not ("<LOD").

    1. Hello Nic!
      If I understand your task correctly, the following formula should work for you:

      =IF(NOT(ISERROR(MATCH("<*",A7:A15,0)>0))," < LOD",MIN(A7:A15))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Hi Alexander,
        Thank you for your time - this seemed to work perfectly. Is there a way to do the same for the max, i.e highest number or <LOD if all are less than detection. I've tried playing around with the formula to try for max but can't get it to work for all cases. Please could you assist. Thanks again.

        1. Hello Nic!
          I’m sorry but your task is not entirely clear to me.
          For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find. It’ll help me understand it better and find a solution for you. Thank you.

          1. Hi Alexander.
            So I have two columns in the spread sheet, one which calculates the minimum concentration from all the values from the lab results, which now works using your formula to pick the lowest concentration - <LOD (< limit of detection) or the lowest number if above that. The second column I need your assistance with is to work out the maximum value for all the cells in the row, as before some have < 0.05 for example, if all the concentrations are above this that's fine but it doesn't populate an answer for where all are <LOD. And the maximum in that case would be <LOD. Hope that clarifies things?

            1. Hello Nic!
              If I understand your task correctly, the following formula should work for you:

              =MAX(--(SUBSTITUTE(A7:A10,"<",""))) Hope this is what you need.

  11. I am having trouble with this If Formula. =IF(K3/31*(X3-H3+1)+M3 >200, "$50"), 200, "$50)
    3.33 X 24 days = $79.92, M2 =150 (150 + $79.92 is more than $200 so I need the return to be $50. If less than $200 I need the total of (K2/31)*(X2-H2+1).

    ANY help would be greatly appreciated. Please and thanks

    1. Hello Dusty!
      Your explanations are not very clear, but I can assume that something like this would suit you

      =IF(K3/31*(X3-H3+1)+M3 > 200, "$50", (K2/31)*(X2-H2+1))

      If I did not guess, then explain in more detail and accurately your problem.

  12. I have a row, the 'amount' row, containing numerical figures from about 100 to about 1000000. I need a formula that will put corresponding figures in another cell. For example if the figures In the 'amount' row are greater than 100 and less than 5200, 100 should go to the cells beside the "amount" row, which I'll call the 'charge' row. And if the cells contains figures greater than 5,200 and less than 10300, then 200 should be in the 'charge' row, so that at the end, I can calculate the total charge for every single amount figure.

  13. Hello,
    Can you please assist me to create formula for following scenario
    When I put patient's name in a cell, patient's UHID unique number should reflect automatically or vice versa
    Thanks

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Thank you!

  14. I am looking for a help finding a formula

    If cell A1 is a negative number it will turn to a positive number and if cell A1 is a positive number it will turn to a zero.

    1. Hello Vincent!
      If a number is written in the cell, Excel can only change it using VBA. An Excel formula can only change the value of the cell in which it is written. But if a number is written in the cell, then you will not be able to write the formula into it.
      The formula in cell B1 might look like this:

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

      I hope it’ll be helpful.

  15. I'm having trouble using the IF logic statement- here it is =IF(C2=7592.00,1251.80,0. What Im trying to say is if the value in C2 is (7592.00) if true input 1251.80, if false input 0.
    C2 actually does not contain 7592.00. Its referencing and reading a value from another cell in a different sheet. ='DRP Implementation'!N127 Result is Im not getting the result I want 1251.80 if 7592.00 is present. Is it because C2 is a ref cell and not a discrete numerical value? How would you correct this?

    1. Hello Larry!
      First off, please check your Excel settings: Excel - File - Options - Advanced - Update links to other documents. Also, please go to Data->Edit Links and make sure the autoupdate for each link is enabled.
      Check the automatic calculation in your Excel. Go to File -> Options -> Formulas -> Calculation Options and switch to Automatic option there.
      Note! In this case, you'll need to hit Shift+F9 to recalculate the formulas in any workbook you're working with.
      Hope it'll help, otherwise please do not hesitate to contact me back.

  16. Hi! I am trying to create a complicated If Then statement and haven't had success. Could you help?
    If the text in a cell says "Participant #1", then I want it to tell me the name which is found in cell C2. But if the text says "Participant #2" then I want it to tell the name in cell D2. And so on through to Participant #4. If there is no text then leave it blank.

    =IF("Participant #1",E2, IF("Participant #2",F2, IF("Participant #3",G2, IF("Participant #4",H2,"")))
    This is what I had, but is saying I cannot coerce my formula into a boolean equation... what on earth?

    Thank you so much for help!

    1. Hello Penny!
      If I understand your task correctly, the following formula should work for you:

      =IF(B1="Participant #1",F2, IF(B1="Participant #2",G2, IF(B1="Participant #3",H2, IF(B1="Participant #4",I2,""))))

      Hope this is what you need.

      1. I ended up having to use this sequence, which I got from an excel whiz that I happened to connect with through a friend:

        =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2, "Participant #1", $E2 & " " & $C2), "Participant #2", $F2 & " " & $C2), "Participant #3", $G2 & " " & $C2), "Participant #4", $H2 & " " & $C2)

        K2 is the cell of the activity participants, E2 / F2, etc. are the Participant First Names, and C2 is the last name

  17. Hi,
    Wondering if someone can please help me with a formula?
    If column G contains the text "Yes", then I want column I to generate a sequential number, starting at 001. If G contains "No", then no number is to be generated and the cell will remain blank, and the next sequential number (002) will appear the next time column G contains "Yes"
    I understand the IF function, but am struggling with the numbering part.
    So far I have =IF(G:G="Yes","*","")- I need to fill in the * part!
    Thanks :)

    1. Hello Amy!
      Please try the following formula:

      =IF(G1="Yes",COUNTIF($G$1:G1,"Yes"),"")

      After that you can copy this formula down along the column.
      Please go to Format Cells, choose Number -> Custom Format and set
      00#
      Or instead of the number in the cell, you can write text and not use custom format:

      =IF(G1="Yes",TEXT(COUNTIF($G$1:G1,"Yes"),"00#"),"")

      I hope this will help

  18. Hi,

    I am trying to write IF functions based on multiple criteria. I need a formula to demonstrate that IF cell V8 is a date that you put in, for example 20/04/20 and cell W8 is <= 16:00:00 (time), then cell Y8 can be either 20/04/20 or 21/04/20 (or whatever the next business day is) and cell Z8 can be equal to or less than 12:00:00 (time) the next business day, if true then pass, If false then fail.

    Thanks

    1. Hello Philip!
      I’m sorry but your task is not entirely clear to me. I can only help you if you accurately describe all the conditions. For example, you wrote that cell Y8 can be either 20/04/20 or 21/04/20. So what date should it be there? Z8 may be equal to or less than 12:00:00 the next business day. Is it 10:00 a.m. or 11:00 a.m.? For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  19. hello,
    I wanted to put a date in one cell then it will populate in other cell. for example if I put a date in A1, whatever I put in A1 should populate in A3, A5, and A7. If I put a date in A3 then it will populate in A1, A5, and A7. If I put a date in A5 then it will populate in A1, A3, and A7. If I put a date in A7 then it will populate in A1, A3, and A5. Hope you can help me with this. Thank you

    1. Hello Winterstory!
      Please note that a cell may contain either a value or a formula. Since it is unknown which of your cells should be filled with data, which should be auto-populated by the formula, your task can be accomplished with the help of VBA only.

  20. good day sir, I would like to ask, if there's a formula that when I put "paid" automatically a blank cell will pop up where I can input the date. Thank you

  21. DEAR SIR,

    1. amount 15000 below amount fix 1800 amount 30 days but less days amount per day less
    2. 15000 and above amount fix 12% amount
    please share excel formula

    1. Hello!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  22. I select a date from cell Q33 ( 04-May-2020), and in cell R33 add some duration to it from either P34, or Q34 and or R34 (Year, month and day respectively). In this case 0 years (P34), 0 months (Q34), 200 days (R34) which gives me 16-Nov-20 in R33. The date so arrived at in R33 should not be greater than year end date i.e, 31-Mar-21. If it exceeds it should return FALSE in R33, if not it should return the correct date.
    I tried following formula, but it does not work. Please help.

    =IF(DATE(YEAR(Q33)+P34,MONTH(Q33)+Q34,DAY(Q33)+R34)>(DATE(YEAR(Q33)+(Q33>DATE(YEAR(Q33),3,31)),3,31),"FALSE",(DATE(YEAR(Q33)+P34,MONTH(Q33)+Q34,DAY(Q33)+R34))

    Thanks in anticipation.

    1. Hello Mahesh!
      The formula you wrote does not work. I don’t quite understand why you have the end of the year - March 31. May 4 plus 200 days is November 20, and not November 16, as you have.
      I suggest using the formula

      =IF(DATE(YEAR(Q33)+P34, MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34 > R33,Q33,DATE(YEAR(Q33)+P34,MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34)

      where R33 is the end date of the year.

      1. Hello Alexander!
        This is used for calculating interest on investment starting and ending in same Fin. Year (FY).
        We have FY starting 1 April and ending 31 March e.g., 1-Apr-20 to 31-Mar-21.
        Thus tenure can be max 1 year or less than 12 months or less than 365 days or in months and days.
        Starting date (Q33) is always =TODAY() and cell is locked, but end date (R33) can not be more than 31-March of FY. Thus if tenure input (P34 Y, Q34 M, R34 D) given is such that end date goes beyond 31-March cell (R33) must return "FALSE".
        First part of formula has to add tenure to start date, > (THEN) part of formula has to find FY end date using start date, and (else) part has to either give end date or if more than FY end date return FALSE.
        Tried your suggestion, but it seeks >#REF! as follows: =IF(DATE(YEAR(Q33)+P34, MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34>#REF!,Q33,DATE(YEAR(Q33)+P34,MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34). This is because formula is being built in cell R33 so it cannot have input >R33
        Kindly suggest.
        Thanks.

        1. Hello Mahesh!
          I copied the formula from a blog in Excel and made sure that it works. Perhaps the error #REF! caused by incorrect copying. To make the formula fit your needs, use

          =IF((EDATE(Q33,Q34+P34*12)+R34) > DATE(2021,3,31),FALSE,EDATE(Q33,Q34+P34*12)+R34)

          1. Hello Alexander.
            This formula works.
            But, I would request you to help make year change automatic i.e., there should be no need to change (2021,3,31) with every FY change in >DATE(2021,3,31) e.g. if date next year is 09-May-2021 it should become >DATE(2022,3,31). Please suggest replacement of Date(2021,3,31) with a formula to achieve it.
            Thanks.

  23. Good day,
    If a cell has no amount - in need to make it zero.
    I use to use this formula and now it is not working:
    =if(d4"",0,D4)

  24. Hi, I am struggling to set the formula for the following:
    Column B needs to read May if the value in column C is between May 1 and May 31, 2020.

    1. Hi Candice,

      Assuming column C contains dates, you can extract month to column B with this simple formula:
      =TEXT(C1, "mmmm")

    2. Hello Candice!
      Please try the following formula:

      =VLOOKUP(MONTH(C1), {1,"January";2,"February";3,"March";4,"April";5,"May";6, "June";7,"July";8,"August";9,"September";10,"October";11, "November";12,"December"}, 2,0)

      I hope it’ll be helpful.

      1. Hi,

        Similar Question with the dates. I have a source data and I want to get the dates based on cut-off.
        1. Row 1 . May 23 - June 20, 2019. Payments made was June 21 (Column C). I would like my Column D to read July.
        2. Row 2. April 23 - May 22, 2020. Payments made was April 25 (Column C). I would like my Column D to read May.
        Can I use "IFS Function" for this? Thank you in advance.

        1. Hello Philip!
          I cannot recommend you which function to use - IF or IFS, since you did not say anything about the conditions under which the formula will determine the month in column D. If there is one condition, then you can use IF. But this is only an assumption. No information needed to answer.

  25. Hi,

    I am trying to do an IF then formula but having trouble coming up with it. I have 4 different columns that I mark with an X dependent on which patient meets which column. However I want an additional column (if,then column) that will calculate if I have put in X in any of the 4 columns in that row and to mark it by yes if there is an X or no if there is not. Can you assist with this?? Thank you!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(COUNTIF(A1:D1,"X") > 0,"X","")

      I hope it’ll be helpful.

  26. Hi,
    I am using a formula like "=IF(D49>=35,"PASS", IF(E49>=35,"PASS", IF(F49>=35,"PASS", IF(G49>=35,"PASS", IF(H49>=35,"PASS", "FAIL")))))"
    In this any one cell 35 also its coming "PASS". Please get me some solution in this and resolve with an appropriate formula.

    1. Hello MURALI!
      I’m sorry but your task is not entirely clear to me. What result do you want to get? Could you please describe it in more detail? Thank you!

  27. Hi I am trying to create a formula that allows me to quickly match the same data that appears in 2 columns

    so if text in column A has a match with text in column b no matter what row it is displayed in, display OK can you please help?

  28. Hi, i am so terrible with code, a little help with the following would be much appreciated.

    how do i write an if statement for, If Column 'N' has text 'Not completed' then Column 'O' must be a mandatory cell to fill(text required)

  29. I need an IF statement that determines the unfilled up cell.
    ex.
    if a number of cells like 05,P5,Q5,R5,S5,T5,U5,V5,W5 which belong to step 1, step 2 and soon are blank I need to specify which step is missing.

    1. Hello!
      If I understand your task correctly, please try the following formula:

      =SORT(IF($O$5:$W$5="", COLUMN($O$5:$W$5)-15,""), ,1,TRUE)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  30. my question is that,
    my formula is accurate, but i want that if in my selected cell no any data in formula cell show that " - " character,
    mostly when your selected cell not have data , your formula cell show that " #value! "
    I want that show as like " - ", with out effected the formula,
    please help me in this task.

  31. I am trying to use =IF... If the text in C48 equals a picklist (five different products) I would like cell E48 to equal the dollar value associated with the picklist name.

      1. Thanks

  32. Hi I would like to create a formula that gives out 100% if "Text1" or "Text2" but a variable percentage that I put in on my own if "Text3" or "Text4". I have at this point reached this formula which does not seem to work: = IF(OR(A3="Text1";"Text2";);100%;"")
    Excel complains and says that my conditions for OR formula needs to be a number, but my conditions are in text so I dont know what to do, so maybe another forumla similiar to OR but you can use text. Maybe some sort of If and Else formula needs to be used here but I dont know how.

    1. Hello Edgar!
      Please try the following formula:

      = IF(OR(A3="Text1",A3="Text2"),100%,"")

      I hope it’ll be helpful.

  33. If you are trying to match two different spreadsheet, one spreadsheet has a number that matches the other spreadsheet but you need to enter the code number in another cell on the other sheet can you write a formula for that ?

  34. Hi- am I able to get a cell to say “1 week” if a different cell says “Extreme”?
    If so can multiple choices and responses then be made to the same cell?

  35. Hi Svetlana
    Please if I have a formula like this:G1=A1&B1&C1&D1&E1&F1 the value of the cells are in words not numbers.the formula actually gave me the my desired results but how can I give a demacation like " , " in between each words. Secondly, how can I make the results to centralized in the cell.
    Thanks.

    1. Hello Enity!
      If I understand your task correctly, the following formula should work for you:

      =A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1

      or

      =CONCATENATE(A1,"",B1,"",C1,"",D1,"",E1,"",F1)

      To center the text, use the menu Alignment - Center.
      I hope it’ll be helpful.

      1. How to I extract text before the Qty ? suppose I just want to extract "bought" from 1st line
        Bought 300 CODX @ 10.3
        Sold 300 CODX @ 10.5
        Sold Short 300 TWTR @ 24.7
        Bought to Cover 300 TWTR @ 24.857

  36. I am in if assistance. This is what I am attempting to do. I need to calculate the total amount (dollars) that meet delinquency days. For example, Column A is the amount, Column B is the days late, and return the total in other cells based of a time span. "0-29" in Cell A7, "30-59" in Cell A8, and "60-89" in cell A9

    1. Hello Thad!
      Please try the following formula:

      =SUMIFS(A1:A15,B1:B15,">0",B1:B15,"<30")
      =SUMIFS(A1:A15,B1:B15,">=30",B1:B15,"<=59")
      =SUMIFS(A1:A15,B1:B15,">=60",B1:B15,"<=89")
      I hope it’ll be helpful.

  37. I need a simple formula, but cannot seem to figure out the answer. I need an IF statement. If there is text in either C8 or C9 greater than 0, then ((c8+c9)/c5) then show -. So far I have =IF(C8>0,((C8+C9)/C5),"-") which works if I have text in c8, but I cannot figure out how to include c9. I may not always have text in C8 and still need the percentage to calculate if c9 has text too, Thanks

    1. Hello Gabriel!
      If I got you right, the formula below will help you with your task:

      =IF(OR(C8>0,C9>0),(C8+C9)/C5,0)

      1. Hi,
        If you have four categories like some, little, none and regular and you want them to prioritise them then how could you do that using IF function or VLOOKUP function?

        1. I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find. Give an example of the source data and the expected result.
          It’ll help me understand it better and find a solution for you. Thank you.

  38. I am trying to use an IF formula.
    - I have Sheet 2 col DL concatenated text that matches col o2 on sheet 1
    - Sheet 2 col M matches Sheet 1 col D
    - I need to match these then if in col CV = 'Open' on sheet 2 I then need to find the latest date in sheet 2 col CX and bring that back if equals 1. critical or 2. Severe if not critical.
    How do I combine match and If please help
    This worked below but for another query but if I chance AM to CV if fails.
    =IFERROR(INDEX('sheet 2'!$AM$1:$AM$7000,MATCH($O2&$D2,'Sheet 2'!$DL$1:$DL$7000&'sheet 2'!$M$1:$M$7000,0)),"")

    1. Hello!
      Unfortunately, your question is not clear enough. Your description is very confusing. For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

    1. Hello Vladimir!
      I’m sorry but your task is not entirely clear to me.
      "If Column A has a “NO” and Column I is blank Column J should have a “NO” '" - formula
      =IF(AND(A6="No",I6=""),"No","")
      But you have a completely different more complicated formula written in the table.
      For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  40. Say for eg: IF(A3>A2, "Increase", "Decrease") and i want to fill the background for increase with green and Decrease with red.....Can it be done using IF statement other than conditional formatting option

    1. Hello Shirish!
      If I understand your task right, you want to change the cell background using a formula, not applying Conditional Formatting.
      Unfortunately, an Excel formula cannot format the cell - it can only return a value. Your task can be solved either with Conditional Formatting or VBA.

  41. Can u please help me for following date formula
    =if(A2:A30 01-01-2018 to 31-01-2018,sum(B2:B30)
    how i write please?

    1. Hello Nayon!
      Please try the following formula:

      =SUMPRODUCT(--(A1:A10>=DATE(2018,1,1)), --(A1:A10<=DATE(2018,1,31)), B1:B10)

      Hope you’ll find this information helpful.

      1. Hi Alex,
        Really appreciate your explanations and I wasn't aware of the "--" notations and it is quite handy.

        Thanks for the great work the Ablebits Team does!

  42. Hello I help to create an IF statement...
    =IF(D14>0 then D14*D15 place value in D16 otherwise leave blank.
    I know I'm missing something just can't figure out what it is

  43. Hello,
    Can you please help me with the following formula?

    469,581,409.75 CR
    271,160,113.76 DR

    assume numbers are A column and CR/DR in the B column.how can we get the - values in 'DR' to the C column.

    1. Hello Sumedha!
      Unfortunately, your question is not clear enough.
      Is 469,581,409.75 in Cell A1?
      What result would you like to get in Cell C1?
      Please describe the problem in more detail.

  44. hi, everyone, can anybody hope you are doing well.
    I have a question of how we can use the IF function for the following argument.
    if student mark is 15 and over 15 will be reward 2
    If student mark 10 or above 10 will be Rewarded 1
    if student mark is 9and 0.5 will be rewarded 0.5
    I did the following but it doesn't work.):
    =IF(G15>=15,2,IF(AND(G15>=10),1,IF(G15>=9,0.5,0)))

    1. Hello Sadaf!
      It is better to write your formula in the following way:

      =IF(G15>=15,2,IF(G15>=10,1, IF(G15>=9,0.5,0)))

      However, it is not quite clear to me what exactly is not working in it. Please explain what this sentence means: "if student mark is 9and 0.5 will be rewarded 0.5". Please specify what the mistake is, in your opinion, and I will try to help you.

  45. Hi pls I need an urgent help hear pls. You are to write a program that will accept the student name and score then automatic "call d name of the student with congratulation if he/she score above 10. A1= John, B1= 12 C1= if(B1>10, A1, Congrate

  46. why won't this work?
    =IF(A1="Departmental Manager",20000,IF(A1="Departmental Supervisor",18000,IF(A1="General Employee",15000,IF(A1="Cleaner",8000))))

    1. Hello André!
      Please describe your problem in more detail. The formula is bringing back the results of calculations. Thus, it is working. Perhaps, you would like it to work differently. What results would you like to get? How do they differ from the results which your formula is bringing back?

  47. Hi there. I am trying to create a formula based around this information. Let me know if it is possible.
    If D1 is less than 3 than or equal than the value should be 7. If D1 is greater than 3 but less than 7 than the value is 10. If D1 is greater than 7 than the value is 16.

    Thanks

    1. Hello Sarena!
      Please use the following formula

      =IF(D1>3,IF(D1<=7,10,16),7)

      If there is anything else I can help you with, please let me know.

      1. Thank you! I am just starting to work outside of basic formulas.

  48. I am coming with my problem directly. In a particular cell I enter something like this- 20AAACT2520C1ZT. Now the first two digits of this number i.e. '20' represents state code. I want the adjacent cell to automatically recognize the state code and display the name of the state. Is it possible to do so???

    1. Hello Debarun!
      You can extract the first two characters using this formula:
      =LEFT(A1,2)

      After that, you can use this code with the IF function:

      =IF(LEFT(A1,2)="20", [to show if YES], [to show if NO])

      Or use the VLOOKUP function for pulling the state name based on the code. You can find more info about VLOOKUP here: https://www.ablebits.com/office-addins-blog/excel-vlookup-tutorial/
      If there is anything else I can help you with, please let me know.

  49. HI.
    I trying to insert icon to replace formula return ex:IF(B2>=A2,"?",IF,"?" like this?
    If can, please draft the formula.

    1. Hello Ahmad!
      You can see symbols that can be inserted in an Excel cell via Insert –> Symbol. There you can also see a code of a symbol. This code can be used with the CHAR function. For example, =IF(A2>B2,CHAR(74),CHAR(76))
      Please remember that you should apply the Wingdings font to the cell where you insert the formula so the symbols display correctly.
      I hope it’ll be helpful.

  50. HI.
    I want to know is it can insert icon to replace formula return ex:IF(B2>=A2,"?",IF,"?" like this?
    If can, please draft the formula.

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