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 51. Total comments: 4817

  1. Dear Sir,
    there are three words in data validation list( CSL,CST,CFL), I WANT TO USE A FORMULA TO WITH RIGHT FUNCTION TO SHOW(IF RIGHT TWO WORD 'SL' THEN SHOW 'SOLID', IF 'ST' THEN 'STRANDED', 'IF; 'FL' THEN FLEXIBLE.

    1. Veeresh:
      Can you clear this up for me? Does the data validation return CSL,CST or CFL or does it return SL,ST or FL? If not why not? Why not have one set or the other?
      If it returns SL,ST or FL the answer is: Where the return cell is A64 it looks like this:
      =IF(A64="SL","Solid",IF(A64="FL","Flexible",IF(A64="ST","Stranded","NOT IN LIST")))
      If it returns CSL,CST or CFL the answer is: Where the return cell is A64 it looks like this:
      =IF(A64="CSL","Solid",IF(A64="CFL","Flexible",IF(A64="CST","Stranded","NOT IN LIST")))

  2. Hi, good evening,
    I want to use IF function in TRUNC calculated function. If i input the numeric digit 7.20 it's OK but double numeric digit (ex. 25.20) is not work properly (only 0.20). Please solve the problem.

    Thanks & regards
    neetu

    G16
    =G16-TRUNC(G16)
    =IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))

    1. Neetu:
      Why do you want to use TRUNC? That function truncates a number to an integer by removing the fractional part of the number. So, TRUNC(4.3) returns 4 and it's 4 for ever more.
      Are you trying to display a number? Maybe round off a number? Can you use ROUND or format the cell that holds the end result?
      Is G16 formatted as a number or text? Maybe TRIM?
      What I'm suggesting is that there are a number of methods you can use to display a number in the format you want without removing the data. Because, once the data is gone, it's gone.

      1. actually I need to calculation on after decimal number in 3 fixed value 0.50, 0.43 & 0.20

        G16
        =G16-TRUNC(G16)
        =IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))

        Is this correct formula? please help.
        thank u.

        1. Neetu:
          The formula you have here works great. When you input the value, it returns the text you want. I'm still not clear on why you need to use the TRUNC function.
          I don't understand what you want to do with the fixed values. What type of calculation do you want to do with them?

          1. Good Morning! I just try to prepare local invoice in excel sheet. where tare value is fixed bag weight (0.50, 0.43 & 0.20). Actually I want to in the sheet input area only G16 (with double digit numerical value) other cell are should be auto calculated. this is the matter. if i use wrong formula then please help me with new formula. TRUNC is not important.

            G16 H16 I16 J16
            Gross Tare Net Bag Size
            17.20 0.20 7.00 small bag

            1. Neetu:
              The values in H16,I16 and J16 can be auto-filled if Excel knows the relationship between the cells.
              For example, when H16 is ".2" then J16 is filled with "Small Bag". This is working on my practice sheet now.
              I can help you with this question more completely if you tell me what the relationship is between these cells. For example, if G16 is "17.2" is H16 ".2" and if H16 is ".2" is I16 "7"? Show me the relationships. It doesn't have to be in a formula.

              1. G16 (input double digit numerical value 25.20 is not working or 7.20 is working)

                =G16-TRUNC(G16)
                =IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))

                this is the relationship but i am unable to establish the small bag relation that is 0.20.

  3. for example:
    my data from number of line
    so eash 1 page have below or =<20 line
    i have 9282 name
    so every 1 page must below or =<20
    1st situation is if i use divide 9282/20 = 464.1 same like 465 page
    2nd situation is if i use divide 1/20 =0.05 same like 1 page

    So want kind of formula i can use IF?

  4. Hi can you solve this for me please.
    If(J4>J3,J4-J3,0) but If(J4=Blank of 0,0) and if(J3-blank or 0,0.
    it mean If J4>J3 calculate J4-J3 but if J3 or J4 = Blank or 0 return to 0.
    thank before hand.

    1. Sarith:
      It appears as if you've answered your own question. The formula is:
      =If(J4>J3,J4-J3,0)

  5. in a row ex. a2,a3,a4,a5,a6,a7,a8,a9,a10. out of this 1 cell contain a number "0"(a2=1,a3=1,a4=1,a5=1,a6=0). the formula i need is to know when one of the row is zero it need to pickup and tell there`s a zero in row a2___a10

    1. Sathi:
      Why not just highlight the cells that contain a "0"?
      Select the cells, go to Format, then Conditional Formatting.
      In the conditional formatting window select the "Cell Vale is Equal to 0" in the three cells then click the Format button and choose a pattern you like and OK out to the sheet.

  6. I am looking for a formula where:
    column b row 193= minimum
    column b row 194= maximum
    if b194 is greater than b193 then b198 must be populated with a specific note and validated in another worksheet.
    If note matched, passed; otherwise failed.

    if there is no range found between b193 and 194; 198 should be blank.

    I would really appreciate any inputs and help.

    mary

  7. Hello, I am trying to update a spreadsheet that was created by an old director. They have an area within the spreadsheet that has a column for goals, and then the monthly figures to in the columns next to it. The monthly number has either a checkmark, an exclamation point, or an x before the number to indicate if it is within goal. For example: Goal = 94% but the April column for that specific goal is 89%, this would read an 'X 89%' in the applicable cell. But the characters are also in color, so it is not just a letter or symbol typed in. When going in the cell, i cant even see any what if statements or anything other than the attached data to another sheet with in the spreadsheet. I hope this makes sense. Does anyone know how to enter a formula into a cell in order to get these characters in there? Thanks so much.

    1. Megan:
      To start, it might be beneficial if you can see the formulas and their respective cell references. There is a good description of how to accomplish this in the various versions of Excel here on AbleBits.
      In the search box type "show formulas".
      Once this is done you might be able to figure this out on your own, but if not write back and someone can probably help.

  8. Hi,

    can any one help me to solve my following issue,
    i have a students register numbers, based on these numbers i need to return a thee numbers in another excel sheet.if any break on these register numbers i need to return a blank value on excel sheet, and from next cell it will continue based on numbers.

    reference No Excel Sheet
    1 1
    2 2
    3 3
    4 4
    5 5
    6 6
    7 7
    9 it should be blank cell (bcoz break between 7 & 9)
    9

    please tell me the solution as soon as possible.

    Thanks in advance.

  9. Very useful. Thank you so much.

  10. Is an If Then appropriate to highlight a cell or row that is blank when based off whats in the adjacent cell there should be a number there, for example:
    if F1 is occupied and D1 is blank, Highlight that row.
    or if D1 is occupied and F1 is blank Highlight that row.
    what would that formula look like and can it be applied to an entire sheet?

  11. Sales 100% 90-99 89-80 70-79 <70
    Target
    <80% 2.10% 1.9% 1.7% 1.3% 1.1%
    <89% 2.40% 2.2% 1.9% 1.4% 1.2%
    <99% 2.70% 2.4% 2.2% 1.6% 1.4%
    100% 3.00% 2.7% 2.4% 1.8% 1.5%
    106% 4.50% 4.1% 3.6% 2.7% 2.3%

    i want to create a incentive sheet
    if sales target is less then 80% and avg selling price is <70% then value cell*with 1.1

    i am trying to calculate via this formula but the result shown false

    =IF(AND(E8<80%,E9<70%),D12*N15,IF(AND(E8<90%,E9<70%),D12*N16))
    plz correct it

    1. Faisal:
      I've looked at you question off and on for a couple of days and because of the formula you included it's still not clear to me what you're after.
      However, if what you want to accomplish is this:
      if sales target is less then 80% and avg selling price is <70% then value cell*with 1.1
      the formula would look like this:
      =IF(AND(A196<80%,A197=70%),A198*1.1,"Something Else")
      If this is what you want, replace the "A" cell addresses with the addresses where your data is stored.
      This formula says: If the value in cell A196 is "<80%" and the value in cell A197 is "70%" then multiply the value in cell A198 times 1.1 otherwise display "Something Else".

  12. Thanks a bunch! I had issues with using the IF function on other examples because it depends on your version of excel. I'm using 2004 I believe and this example worked perfectly for me! Cheers :)

  13. I am adding 2 cells together. Sometimes one or the other cell is blank and the =sum(a1+b1) formula doesn't bring back a value. if cell b1 is blank then return cell a1 value,or if cell a1 is blank then return cell b1 value, otherwise sum a1+b1

    1. Leni:
      This should work: =SUMIF(A5:B5,">0")
      Where the numbers are in A5 and B5 and are greater than zero.

  14. Asjath:
    This type of situation calls for the VLOOKUP function.
    In your case in Sheet 2 B1 enter this:
    =VLOOKUP(A1,Sheet1!A1:B3,2,FALSE)
    Where the data you want to lookup is in Sheet 1 cells A1:B3

  15. guys,

    I have two sheets in my excel.
    Sheet1,
    A1:A10 are text & B1:B10 are text

    Now in sheet2
    if i link any of item from A(A1 to A10), in next i need the B item automatically.

    who can help to this?

  16. I am trying to get a formula to work from a date table, and using today's date. For example: if today within April 2018 it will pull the code "EX" and place it in a cell in another sheet.

    YEAR/MONTH JAN FEB MAR APR MAY JUN
    2018 AX BX CX DX EX FX
    2019 AY BY CY DY EY FY
    2020 AZ BZ CZ DZ EZ FZ
    2021 NA PA QA RA SA TA

    This is what I was working with but because I am using 2013 I am limited to 7 rows.
    IF((YEAR(TODAY())=2018)
    AND(MONTH(TODAY())=01),Date!B2,
    OR(MONTH(TODAY())=02),Date!C2,
    OR(MONTH(TODAY())=03),Date!D2,
    OR(MONTH(TODAY())=04),Date!E2,
    OR((MONTH(TODAY())=5),Date!F2,
    OR(MONTH(TODAY())=6),Date!G2,
    OR(MONTH(TODAY())=7),Date!H2,
    OR(MONTH(TODAY())=8),Date!I2,
    OR(MONTH(TODAY())=9),Date!J2,
    OR(MONTH(TODAY())=10),Date!K2,
    OR(MONTH(TODAY())=11),Date!L2)

    Any help would be greatly welcomed.

  17. I am trying to use a formula to do the following

    in F1 I have the TODAY Formula

    Column A will contain various dates

    Column C an Y or N

    If Todays date matches the date in any cell in column A, i want the Y or N in column see to be pulled into the I2 under todays date. Can any one help with this?

  18. Sir
    I want to use formula that if cell no menton RD then multiple of 5% and if cell number mention URD then no multiple. Please tell me sir.

    1. Is the data in these cells "TEXT and a NUMBER"? Or do they contain only text or only a number. Show an examples of some cell's contents.

  19. Hi,
    i need formula:
    if m11=100% Q11 show 15%
    if m11=51% Q11 show 7.5%
    if m11=52% Q11 show 7.7%
    if m11=53% Q11 show 7.8%
    untell 99%

    thanks

  20. Hi,
    i need formula:
    if m11=100% show 15%
    if m11=51% show 7.5%
    if m11=52% show 7.7%
    if m11=53% show 7.8%
    untell 99%

    thanks

  21. Good afternoon,
    Cell A1 = 2 Sizes of mixed, ranging variable number.
    (Example:&UA63%38TL & E1D&HX1)They change radically inside there 10 and 7 digit format respectively. Tracking each number is impossible, but the total number of digits remain constant; 10 and 7)
    I need to get a True/False for a cell with a specific digit count in a cell.

    I hope that was clear enough to understand.
    Thank you!

  22. hi
    i am making price list
    i need to do in b column if number is same in b column so in in k column result will be plus in l column
    exam
    1 A B --------- K L
    2 1 --------- 25.50 38.25
    3 1 --------- 12.75
    4 2 --------- 05.75 17.20
    5 2 --------- 11.45

    SO WHAT SHOULD I WRITE IN L COLUMN....

    1. Hi,

      I am sorry, your data look distorted in the comment above. If you can send us a small sample workbook with your source data and the result you want to get, we'll be able to help you better.
      Please shorten your table to 10-20 rows / columns and email it to support@ablebits.com. Please also don't forget to include the link to your comment in the email.

      Thank you.

      1. thanks you so much for reply Ekaterina Bespalaya

  23. hi
    i am making price list
    i need to do in b column if number is same in b column so in in k column result will be plus in l column
    exam
    1 A B --------- K L
    2 1 --------- 25.50 38.25
    3 1 --------- 12.75
    4 2 --------- 05.75 17.20
    5 2 --------- 11.45

    SO WHAT SHOULD I WRITE IN L COLUMN....

  24. hi
    i am making price list
    i need to do in b column if number is same in b column so in in k column result will be plus in l column
    exam
    1 A B --------- K L
    2 1 --------- 25.50 38.25
    3 1 --------- 12.75
    4 2 --------- 05.75 17.20
    5 2 --------- 11.45

    SO WHAT SHOULD I WRITE IN L COLUMN....

  25. if I want to find if C1 is between 200 and -200) put "Lab" and if G2 is "PD25088" put "GEO"

    1. Your question is unclear because the connection between the two outcomes is unclear. I can't figure out the logic.
      However, the first part will require =IF(AND(C1-200), "LAB", "No LAB").
      The second is =IF(G2="PD25088","GEO","No GEO").

  26. I need formula in excel

    in entire row if same number - count only 1 and other show as zero

    111 - show as 1
    123 - show as 1
    124 - show as 1
    124 - show as 0
    125 - show as 1
    125 - show as 0
    126 - show as 1
    125 - show as 0

  27. i want to use that if condisation

    like when i type company name it has to show the GST no.
    i have so meany clients so thats way mail me or msg me how can i use that condisation 8197938637

  28. Hi! How can I create a formula for the following?

    =IF(H9="S4309","",IF(H9="S4310","",IF(H9="S4311","")=I9+365))

    So, if cell H9 equals either S4309,S4310 or S4311 then cell K9 will be blank. Otherwise, it will populate an expiry date based on cell I9.

    Thank you for your help!

    1. I believe this is what you're looking for.
      =IF(OR(H9="S4309", H9="S4310",H9="S4311"),"Blank",I9+365)
      I put the word "Blank" in there to test it. You can use "". I formatted I9 as Date and H9 as Text.

  29. Thank you for the lovely examples of using the IF() function. I'm going to use this for a lesson on teaching the IF() function.

  30. Please ignore my first post, as I have worked out the formula, but realised I cant sum the values in the target Cell

    So this what I am trying to do now.

    If C27 = P then S27 = 1
    If C27 = DO then S27 = 0

    Is the above possible.

    What is the best way to combine into one argument.

    Thanks.

    1. Looks as if this will need a nested IF formula. Ablebits has a good description of how to use these as well as similar conditions. Search "Nested IF formulas in Excel" here in Ablebits.

  31. I can get this working as individual formula's

    =IF(C27="P", "1")
    =IF(C27="DO", "0")

    What is the best way to combine into one argument.

    Thanks.

  32. How to write logical formula for the following ?

    If sales is 101% to 104 % of Target , incentive will be 1% of Target value
    If sales is 105% and above Incentive will be 1.25% of Target value

  33. Hi Team,

    I am trying to find a substring from a string of URLs

    example : I have column (a) with codes "xhd2424" and coloumn (b) with URLs "http://www.xyz/today_same-xhd2424". I am trying to see if the code in column (a) exists in column (b). If so I want to print "true" or "false" in a different column.

    I have been using the following formula " =IF(ISNUMBER(SEARCH(A2,B:B)), “Yes”, “No”)" it seems to work on some spreadsheets and for some i get an error "#Name". I am not able to figure out the issue with the formula. Only that the code in the URL doesn't always be at the end, it is embedded in the middle for some.

    Please help.

  34. Hi, I need help in creating a logic formula.

    Job date : 14 Mar 2018
    Job time : 18:00
    Job completed date : 15 Mar 2018
    Job completed time : 09:00

    I need to calculate the incentive.
    job time 19:00 to 23:59 : $5
    job time 24:00 to 07:00 : $10

    How do I write the logic formula?

  35. Hi, I need help in creating a logic formula.

    Job date : 14 Mar 2018
    Job time : 18:00
    Job completed date : 15 Mar 2018
    Job completed time : 09:00

    I need to calculate the incentive.
    job time 19:00 to 23:59 : $5
    job time 24:00 to 07:00

  36. Can I use cell/text formatting as a condition for an IF expression? For example IF the content of a certain cell (A1) is formatted as strikethrough THEN contents of the current cell (B1) = a value (1)

    Thanks for your help.

  37. Hello I need some help with my excel. I have a default value of 3 on A1 then if B2 is greater than 0, then i need to deduct 1 from A1. Thanks guys

  38. Hi,
    If i have a list of text and I wish to enter the blank cells with text and leave the other cells with there original text is there a formula for that.
    I have created a True false column as I thought this might help but am stuck. I can enter the true statement to add the text that but the false argument I am stuck on.
    Regards Sara

  39. Hi, please help me whit a formula for my need

    I have a cell that can be text or number ( AH99). I need to make an IF AH99 is any text, then return 0 and if not, multiply that number from AH99 whit another cell ( AI99) and return result

  40. Hi Team, You guys/gals do a great job. I hope you can help me.
    I need to check for duplication in a list of names. Column A contains the primary name. Column B through H contain other contacts. I need a way to check if any of the other contacts equal the primary name.
    Example: Adam, Bob, Jason, Rick, Susan, Adam, Ray, Zak. (the response here would be a fault.
    I tried: +IF(B2:H2=A2,"OK","Fault") but I get an error message. I also tried: +IF((B2:H2)=A2,"OK,"Fault")
    I know I could compare each of the other contact cells individually, but that would be inefficient.

  41. Hi,

    I have a question if there a same question but I have different answer for same as below which formula can I use.
    data Answer
    no water Vavle off
    no water empty
    no water abc
    no water cyzdf

  42. Chargeable Wt| |Handling & Doc| |Custom Clearance| |Transport Charges|
    335.5

    I wanted to put the values once chargeable wt key in but the formula that i try to input result is 0...

    actually i want to set like this - if chargeable wt less than 233.33 charges charge to us MYR21.00 but if move than 233.33 they will charge us MYR0.09 per kg.

    Can you help me..

  43. I need help with this formula: =if(B5:E11>100,"Yes","")

    I wanted to put the values if any from the test in the G column. I tried the formula in cell G5; selecting the G5:G11 in the G column and typing the formula in formula bar; and selecting B5:G11 with If from Autosum list.

    I get an #Value! message in each cell selected of the G column every attempt.

    What am I doing wrong and how can I fix it please?

  44. HI

    If TEXT IN A1=B1=C1 THEN THE SAME RESULT IF TRUE ELSE FALES

    PLEASE HELP HOW TO USE IF CONDITION

  45. I am trying to put a formula for multiple spreadsheets, where on spreadsheet A the date indicates something as completed and a blank spot indicates something is incomplete using - spreadsheet B contains the date/blank spot. So essentially spreadsheet A is meant to be a summary of spreadsheets B-G (B-G have the data).

  46. Trying to come up with a formula that says, if the number in cell A1 is greater than 1, but less than 500,000 the result is 120.

    Similarly, if the cell in A2 is greater than 500,001, but less than 1,000,000 the result is 200.

    Thanks in advance.

  47. I am trying to put a formula where the date of one column is more than or equals to todays date then it should display as "not expired" and if the date of one column is less than todays date then it should display as "expired".

  48. Sir
    I want formula for
    100g 36pkt
    Open stock 10cb
    Production 5cb+45pkts
    So for total stock 45pkts-36pkts difference to be converted into cab(10+1=11) and at pkts 45-36=1 pkt for doing this how to put formul
    Regards
    Ramachendir

  49. Hi
    I'm trying to type a if statement for text with the following criteria:
    if a cell begins with ="GA" then "General Assortment"
    if a cell begins with "7" or "9" and is 10 characters long then equals "Planogram"
    If cell is between "1" TO "9999" then store layout module

  50. Q: Please help me translate this into formula. I'm really having hard for the then statements that are equal to computations.

    Statement: << If Hours >

    Letter Assignments: Hours is B4 | Rate is C4 | Gross Pay I4

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