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

  1. HOw to write the following using IF ?

    0.70-0.75 1
    0.75-0.80 0.9
    0.80-0.85 0.8
    0.85-0.90 0.7
    0.90-0.95 0.6
    0.95-1.00 0.5
    1.00-1.05 0.4
    1.05-1.10 0.3
    1.10-1.15 0.2
    1.15-1.20 0.1
    1.20-1.25 0

  2. Hello;
    I have the formula which i stated below, doesn't work. Could you help me on this matter.
    The idea is: i will insert the data in C30 and if it's blank it will show "Please insert rank in cell C30" but if it's not blank it will show the value that searched through the table which is between "b33 and b41". So far it worked (without using AND formula).
    Lastly i want the insert values to be between 0 and 10. So i put the and formula but it doesnt work.
    Sincerely.
    =AND(IF(ISBLANK($C$30);"Please Insert Rank in cell C30";(INDEX($B$33:$B$41;$C$30)));(c30>0;c30<10)

  3. Hi, im looking for the right formula to identify the value statement.
    example:
    Category Description Amount BDO Eastwest
    Cash In BDO 500 (formula?)- on how to appear the amount

  4. if i want to input a value in cell A1, then each time the output value in cell B1 must be added to that value. in this scenario which formula i should use ?

  5. Hi, I'm trying to create a formula that will provide a "True" statement if the cell is blank between 1/1/19 and 5/30/19. Then a formula to identify blank cells after 6/30/19. I'm using these formulas for conditional formatting.

  6. I want to see automatic date in cell if I enter some dispatch quantity
    Example--
    Date Dispatch Quantity
    Today Date 150 and any other number >0

  7. is there any formula to find out the total figer of a person whose name in a sheet 3 to 4 time.

  8. AMT Clientname client code
    42560 SAP 1234
    31760 DELL 4321
    2150 INTEL 3412

  9. i want to user if and in single formula
    exp:A=>1&B=SAP=1234
    A=>1&B=DELL=4321
    A=>1&B=INTEL=3412

  10. I want to know how to work this formula out: in cell M1, I add F1+ I1+J1+K1+L1, but if the total in cell F1 an I1 is 0 then the total should be 0

  11. Can you please help with a formula to calculate a Balance column for a Cost and/or Refund for an expense sheet. I cannot figure out how to set up my balance column to calculate when a cost or refund was entered and give the new balance (either an addition for the refund or subtraction for the cost). The header for each column for the sheet looks like this:
    Date Initials Client Name Description Cost Refund Balance

  12. Hi,
    I am Aaditya please help me how can set this formula
    Data
    If A, B and C dealer has Purchased goods in different slab
    Slab Slab Point Purchase
    Silver 8000 7200
    Purple 5300 4900
    Blue 3000 5300

  13. HI i am having on question
    i am having a excel file that contains website names
    in my sheet eg
    abc.com
    abc.com/test
    abd.com
    abc.com/hello
    I need to highlight those cells which Matches first 15 letter's
    Answer would be Highly appricated

    thanks

  14. I have a list that is a drop down
    hello
    goodbye
    so sad
    I have a summary column that I just want a numerical value to appear in. So if someone chooses goodbye my summary column would show 2. If someone chose hello is would show 1 etc. What is the formula I should have in the summary column for it to determine this? Lets say the drop down values are in column B2.

  15. Hi, how would you write
    if a range of cells contains L, then add 9.5

  16. I want a function in excel where when I enter "A" as absent in respective Cells for a month then other cells are marked as "P".

  17. any idea how to make formula if I need something like this
    I have few different numbers (example 35413565 , 14451844) nd I need if first number is 3 then text in other column is (ex.YES) or if is 2 then text NO.
    Could someone help ?

  18. =IF(J10="","",(IF(J10>=75%,"HIGH"),(IF="",""(IF(J10,˂49"LOW,"MODERATE"))) my formula dont work

  19. Hi All ,

    I hope everyone is well !

    I currently have the following in a cell =((H21-G21)*24-1)

    this simple formula allows me to count the number of hours between two times ( when working out working hours for my staff)

    I am looking to have this cell note that if a time is not put into the cells ( h21/g21 ) this information is current data validation from another sheet - and instead the word "holiday" is added the cell will generate an automatic number ? is this possible ? any help would be great !

    Out normal working hours for a "holiday is 8 hours" so if the cell says holiday i would like it to default this to "8"

    thanks again

  20. trying to write a formula that will look at 3 inputs and assign a point value.

    i need a formula that will assign 3 points to the highest score, 2 points to the second highest and 1 point for the lowest.

    thanks

  21. I am pulling my hair out on a formula. I have a workbook with 5 tabs ( Gen212, Poe,360 BWS, sample, Production). What would the formula be to have a flow number from tab 5 (production) in column H row 7 automatically populate into tab 3 (360 BWS) in column C row 18?

  22. Can someone help with formulas for the following 2 scenarios:
    1. Groups submitted from the 25th of the previous month
    -For month of February: statement is true if the date is 1/25/19 thru 2/28/19
    2. Groups submitted from the 25th of the current month
    -For month of February: statement is true if the date is 2/25/19 thru 2/28/19
    Thank you!

  23. I have two columns. Column G is my due date and column J says whether I have approved or denied the application. If the due date is in the next 7 days, from today, and the corresponding cell in column J is blank (meaning I have not approved or denied the application) I want the cell in column G to highlight, or the row, to let me know the due date is coming up and I have not taken action on the application yet.

  24. Hello-

    I am in desperate help of trying to figure out an if statement that can help me calculate # of days past due.

    I have a column for start date, end date and actual date of completion. I have a column with a formula for today’s.

    Originally, I used the networkdays formula to calculate the days from due date to today’s date, but when I put in the actual completion date, the past due days keeps updating and I don’t want that.

    Have no idea if that made any sense. But hopefully someone can help me out !!

  25. Hi,
    May i Know how to use TODAY function when my table has different dates,
    Date-1, Date-2,Date-3,
    How to get the same data on one column based on IF Function,
    thanx

  26. Hi,

    I want to write a function that will find alternate minimum value for a range where logical value TRUE equal 1 and the logical value FALSE equals 0, and the text and blanks equals 0.
    Thanks,

  27. G 12 is an unknown which is a value where i keyed in, meanwhile E12 is a value which i obtained through a returned value from another keyed in a value, both G 12 & E 12 multiply each other and should not give you more than 220, if it does, it supposed to print out 220 or else the value is supposed to be the the equation provided,need some help as i am getting errors when executing it

  28. Problem getting IFS formula to work.
    Problem I want the formula to read the cell and if it contains a certain text,then it is to input certain text, and if it is another text,then input another text,if the cell contains a certain text,then input a certain text.

    Example:
    Cell: =IFS(C1="CAR","A.V",C1="NQR",C1="SCAR")
    Formula only regonizes CAR and inputs A.V.,but will not work for other two conditions.

  29. Hi Svetlana,
    How would I write a formula for time zone conversion sample:
    Cell D2 Local Time, E2 is the GMT Conversion and L2 is the Converted TIME Zone
    is it like; =IF (E2=+2), D2+600
    My logic is: The time in L2 is the equivalent of D2+600 if E2 has value of +2
    Hope you could reply.

  30. What my goal is to have a set of cell content only (B4:K6)to empty at 6am daily. Is it possible and if so how do I accomplish this task?

    Thank you.

  31. I'm trying to use this formula for these three qualifiers. Can't get it to work. Any suggestions?
    =IF(AND(ToCSDIncidents[@[Assignment Group Final],"CSD_SERVICES",ToCSDIncidents[@[Reassignment Count]],"0",ToCSDIncidents],ToCSDIncidents[[Exclusion]],""),"TRUE","FALSE"))

  32. =IF( ((G12 * E12) + 14)> 220), 220, ((G12*E12)+14)
    Hi everyone i need help on these if statement on excel, G 12 is an unknown which is a value where i keyed in, meanwhile E12 is a value which i obtained through a returned value from another keyed in a value, both G 12 & E 12 multiply each other and should not give you more than 220, if it does, it supposed to print out 220 or else the value is supposed to be the the equation provided,need some help as i am getting errors when executing it

  33. that looks at the contents of a particular cell (either Global Brokerage or USA Brokerage) and then, depending on Global vs. USA, I want to point to a 1 of 2 cells that contains a percentage. The percentages can change and I do not want to type the actual percent into the formula. I want to point to the % in 1 of 2 cells.

  34. Please assist me as I am trying to create if then function in excel but I don't know how to include if the cell is blank =IF(K2335="eamc","YES","NO") but this statement also put "no" if the cell is blank. how can I make it like if there is no data then it has to be blank as well.
    Thanks

  35. Hello,
    I have tried several ways to do this,please help.
    if A2:21 says complete it equals a % and totals all within
    Thank you

  36. I have an simple If function in B1 for example =IF(A1>=1,"1","0").
    The entry number works if I use it in a =SUM formula horizontally in D1 =SUM(C1-B1). Example: A1 entry is 2, B1 becomes 1. C1 entry is 4, D1 becomes 3. Works great.
    Here is where I run into trouble. I copy formulas vertical example: B2, B3, B4... then attempt a simple SUM formula =SUM(B1:B4) the result is always 0 even if B1 thru B4 all have 1 as a result.

  37. IF Cell A1 IS NOT EQUAL TO Cell B2 or Cell A1 is blank then it is to be considered as Cell C1 data

  38. Hi , may I seek some helps on excel function... is there a way for the excel function to do an automatic recalculation if a defined criteria is not met..like IF B2=5 or less than 5 then goto do recalculation..
    Thanks and best regards

  39. I am trying to setup a formula that looks at the contents of a particular cell (either Global Brokerage or USA Brokerage) and then, depending on Global vs. USA, I want to point to a 1 of 2 cells that contains a percentage. The percentages can change and I do not want to type the actual percent into the formula. I want to point to the % in 1 of 2 cells.

  40. Hello,
    I am trying to add a column of IF statements and the sum will not recognize any number other than 0. My IF function in each cell is:
    =IF(H91>14.9%,"3",
    IF(H91>9%,"2",
    IF(H91>8%,"1",
    IF(H91=0%,"0"))))

    How can I sum this column automatically?
    Thank you.

  41. Hi, can you Help me to solve this, i want to create 'A,B,C and D' for following marks
    A=90 above
    B=80-89
    C=70-79
    D=60-69

    please help me to create the formula using IF function

  42. Hi! I need a formula for the following: If A1 or B1 or C1 or D1 contain "2" then write "True" in cell E1. I would like to know that any of the mentioned cells contain number "2". Thank you for your answer in advance! :)

  43. Hi Everyone,

    i need some help in replacing the column text.
    Scenario: A3 needs to be replaced in new column J3. If it matches with the values in (B3 = H3 AND J3 = C3) when the data between these column matches i need to replace the text value from A3 to J3 else "NA". Can someone please share the logic that would be much appreciated!!

  44. Here is the scenario. I am currently counting down sizes for tshirts once as someone purchases one. I want to say if Any Row 1 to 200 on column D contains an "XL" then minus one from 100. How do i write that? So far i have =IF(D1="XL",E546-1,"") and it is just working for the cell D1 only. I want it work for the entire D column. Please note i have the 100 on E546. Help please

  45. Hello i am struggling on what formula am i going to use in order for me to put the value of a cell using some reference, example,

    if A1 CONTAINS 12345678 THEN PUT B1 ON THIS CELL. Can you understand the situation? please help

  46. I am trying to write an IF statement for a gym membership. Column C5 is the cost of the membership and Column D5 is the extra cost for a locker. If there is a YES in D5, add $75. If NO, no extra cost.

  47. I need something this type of answer in excel.. Please help:
    I want answer as "Sensitive" or "Insensitive" as per the texts in the reffered cell.

    exp: =if(A2= 376/377/376A/376B/376C, "Sensitive", "insensitive") .. hope you get my Idea what I am want in separate cell ?

  48. How to I call to both numbers and have text in my if statement. I want to say something along the lines of "You are " + x + " dollars over budget". With the X being a called variable able to be changed.

    Thanks y'all for your help

  49. Values to appear
    - to 10
    11-20
    21-30
    Above 31

    How can we use this in IF Condition as a status.....

  50. My total due amount is in cell A1 and status in cell A2
    i want cell A2 to change itself as per my conditions
    For ex,
    If A1>=0 then A2 should be "PAID",
    If A1 is greater then 0 but less then 350 A2 should be "DUE",
    & If A1 is greater then 350 A2 should be "OVER DUE"

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