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 65. Total comments: 4830

  1. I'm trying to do monthly columns from a previous column in same spreadsheet with random/multiple dates; pulling a dollar amount from ea month into it's own months' column. Is this possible?

  2. Hi,
    Could you help with the following that I want use in conjunction your Merge Two Tables Add In? I read through your "IF function in Excel" page and the comments but don't see an answer to my problem:

    I have the following headers for columns A to F
    Appointment Status, Session, Date, Consumed, No Show, Balance

    In column A I have 3 states: Complete, No Show, NoShow NoEmail
    In column B I have various possible text strings: e.g. "PhonePill Flexi 20" "PhonePill Flexi 30", "PhonePill Flexi 40" the last number represents minutes.
    In column D, I used this formula =RIGHT(B2,SEARCH("",B2,2)) to show that last number from the column B text string: 20, 30 or 40

    However, what I really want is for those numbers to go in the appropriate Consumed (D) or No Show (E) column: i.e. if the Appointment status =Complete the number should go in column D. If the Appointment Status= No Show or = NoShow NoEmail, the number should go in column E

    Thanks for your help.

  3. Hi , could some one help me to create a formula please
    i have two products alloy and carbon steel. these have composition in maximum and minimum range in percentage . if composition is different then given minimum and maximum range result of formula should say pass or fail . range is :
    Steel Type Carbon Manganese phosphorus sulphur nickel chromium
    Carbon Steel Max 2.1% 1.5% 0.5% 0.3% 0.25% 1.5%
    Min 0.5% 0% 0% 0% 0% 0%
    Alloy Steel Max 1.5% 5% 1% 1% 5% 15%
    min 0.5% 0% 0% 0% 2% 11%

  4. HOW TO USE IF FORMUALA IN CASE OF
    C=COMP
    P=IND
    OTHER ALFABATE IS =FIRM

  5. Good Day

    I have managed to ad the if formula no problem. thank you for that.
    How ever I would like the cell background to turn green if the result is favorable.

    regards

    Leanne

  6. Hello

    I want to create a list to automatically fill in my prices. I have two values to look at: 1. is the size of the item. 2. is the description of the item.

    Ex: 10 Straw
    So it looks up "10" which refers to a pricelist.
    Then it looks up "Straw" from the list it was just referred t by the number.
    It then should return a price that is in the list.

    Any ideas?
    Having a dropdown would be even better. So you can choose your size form a list, and then in the next cell choose the item that is in that list.

    Thank you in advance,

  7. I am trying to write a formula in E5 where if D5 is > $5,000.00 then subtract $5,000.00 from D5, if not then blank.

    Thanks,
    Larry

    1. Hi Larry,

      Here you go:
      =IF(D5>5000, D5-5000, "")

  8. Hi,

    I'm looking for a way to get an average from a set of values with "<" contingencies. For instance I need the average of cells A1:A6. If <10 is present I would like to use 5 instead. If there are "<" than I would like it to use the values given.

    thank you in advance
    -Esmeralda

  9. Hi.

    I have been googeling this for some time now, and cant find out how to:

    check if the cell value is less than another cell value, and if it is, add a cell value to the first cell.

    A1 = 120, A2 = 200, A3 = 300. I need some function to check if A1 is less than A2, which it is, and then add A3 to A1, so A1 becomes 120 + 300 = 420, and is no longer less than A2.

    if anyone have an answer for this, i'll be very thankful.

    thank you in advance.

    - Morten

    1. Hi, Morten,

      I'm afraid you won't be able to put the formula in A1 and reference A1 at the same time. You will have to enter the formula to some other cell, say A4, to return the result:
      =IF(A1<A2,A1+A3,"")
      Also, note that if A1 is not less than A2 the cell with the formula will remain empty.

  10. Cell A1 Data like this "10+10"
    i want the result in B1 like "20"
    which formula i want to use. anybody help?

  11. I am trying to figure out how to use an if function when a cell contains a date in a format such as 1/1/2004. What I am trying to do is strip the month and day information and have an if statement that will return a result of "2004". For example if 5/5/1998 it should return a result of "1998". Can anybody help?

    1. Hi,Soccer Guy

      use this formula if your data in A1 "=YEAR(A1)"

  12. Hi,

    If A1 cell value 1, B2 cell value 2 = i need result not more then value 2

    Do you help me? How to create formula in excel?

  13. Hi,

    I have a list of activities if I select any one of those activities in one cell then the other cell should reflect as Production. Could you please help me with the formula

  14. Hi I need help figuring out formula to do.
    =if cell is 1-3=0 if cell is 4-7=5 if cell is 8-11=10 if cell is 12-15=15. using signs.

    ex.=IF(D2>=4=5,IF(D2>=8=10,IF(D2>=12,15)))
    where d2 is number of disks used
    and working from G2 where it is discount given in currency

    0-3 disks= 0$
    4-7 disks= 5$
    8-11 disks= 10$
    12+ disks= 15$

    1. Hi, Michael,

      If I understand your task correctly, you can try this formula in G2 (make sure to set the Currency format to the cell):
      =IF(AND(D2>=0,D2<=3),0,(IF(AND(D2>=4,D2<=7),5,(IF(AND(D2>=8,D2<=11),10,(IF(D2>=12,15,"")))))))

      Hope it helps!

  15. Good day. Instead of doing double entry on an excel spreadsheet, how do I link the sheet to read the following:
    Spreadsheet 1, Column C has number 12345, but spreadsheet 2 replaces the 1 with 70999 in Column C, thus creating 709992345. What formula would I use?
    Thank you in advance for your time.

  16. I have an excel sheet with three columns.
    I want to create a nested if formula that shows the following
    =If(RR>8,"P1",If(RR<=3,"P3","P2"))
    How do I re-write this formula in a situation I do not want a value returned when any RR cell is empty?

    Thanks

    1. Hello, Peejay,

      If you reference RR1 cell, then
      =IF(RR1="","",IF(RR1>8,"P1",IF(RR1<=3,"P3","P2")))
      then copy the formula down the column to return the values for other RR cells.

      Also, note that the formula above will return the text values P1, P3, and P3.
      If you want those to be cell references, then use the following formula:
      =IF(RR1="","",IF(RR1>8,P1,IF(RR1<=3,P3,P2)))

      Hope this helps.

  17. I have an excel sheet with a single cell (Ctrl+;) showing 'TODAY'S DATE', and two columns:

    Today's Date: 24/04/2017

    NAME DATE JOINED WORK
    John Smith 24/04/2017
    Mary Jane 01/04/2017
    25/04/2017
    Paul Loyd 02/04/2017
    Robert Gray 29/03/2017
    25/04/2017
    Ann Buck 24/04/2017
    25/04/2017
    25/04/2017

    One column for NAMES of staff, and one column for DATE JOINED WORK.
    I would like to always have 'tomorrow's date' in all cells in the DATE JOINED WORK column. And, if a name is entered on the NAMES column, I would like 'today's date' to appear in the DATES cell.
    When a cell in the NAMES column is blank, the cell in the DATES JOINED WORK column should show 'tomorrow's date'

    How do I do that?

    Thanks!

  18. i want convert formula to values like below
    =sum(A1+B1+C1) A1=10,B1=10,C1=10 total result D1"30"
    i want convert the result to values but i cont.
    i tried all the way in formula bar its not showing 30 its showing formula only, after one time using formula its want to show only 30 in formula bar also

  19. i want convert formula to values like below
    =sum(A1+B1+C1) A1=10,B1=10,C1=10 total result D1"30"
    i want convert the result to values but i cont.
    i tried all the way in formula bar its not showing 30 its showing formula only, after one time using formula its want to show only 30 in formula bar also

  20. Hi,
    I need an excel formula for the following:
    IF A1 & B1 HAVE NUMBERS THEN ADD THOSE NUMBERS, IF B1 IS BLANK THEN RETURN A 0.
    I am working on a compensation spreadsheet containing market adjustments for some of the staff. I calculated the market adjustment and need to calculate the new base rate. If the base rate did not get an adjustment then I need the cell to show 0. If the base rate did get an adjustment I need it to add the current base rate with the calculated adjustment for the new base rate.
    Thank you in Advance

  21. I need urgent help. I need to copy an formula into an spreadsheet of mine. Using Vlookup & IF. I have 3 ranges to work with. Pink, Blue, yellow and the sheet im using has 3 columns but mine has 3 actual rows with 3 different codes. How will I do this?

  22. Hi
    I am trying to do a simple less than < formula but can't seem to get it to work. I have two columns with dates in them say A & B. I want to be able to say:

    if A1 is than A1 then B1. If A1 is blank I want it to input what is in B1.

    Any help would be great!
    Thanks

    1. It would be wrong to check if A1 is less than A1. But, in case you misspelled and wanted to see if, for example, A1<A2, and return B1 in both cases, try the following:
      =IF(A1<A2, B1, IF(ISBLANK, A1), B1, "")
      If it's not helping, please, consider correcting the condition so we could assist you.

  23. Hi,
    I want to add cells up, and if they are greater than 0, add those cells up, multiply that time 200,000, then divide by other cells that contain numbers. For some reason, it will not give me the correct answer. Should I be using the IF function before the sum, or the sum function before the if?

    1. Hey, Rob,
      try IF function with SUM function inside.
      If the cells to sum up are A1 and B1, the formula will be
      =IF(SUM(A1,B1)>0, (SUM(A1, B1)*200000)/C1, "")

      If it doesn't answer your request or you meant something slightly different, please, specify your question.

  24. I need this:
    =IF(H18*I18=1,"B18","")
    but B18 doesn't contain a number, but text.
    I need the IF function to give me that text in cell B18 but it gives an error.
    Is there a solution?

  25. I am trying to construct a nested IF AND function that involves summing a range of cells. I tried using the following: =IF(SUM(I6:I15)>15,2,IF(AND(ISBLANK(H24)),0,1.5)) Where am I going wrong or what variation do I need to use? Thanks!

    1. Hi, Adam,
      AND function requires at least 2 parameters, but there’s only one in your formula. If you have only one condition – whether H24 is blank or not – you can omit AND. For example:
      =IF(SUM(I6:I15)>15,2,IF(ISBLANK(H24),0,1.5))

  26. Hey,i am supposed to record the following in excel. Can you assist me?

    THE FOLLOWING QUESTION;
    a) The hostels are allocated as follows
    Kenyan students are only considered if they are Female, 20 years and below, and without kids, such are housed in Nyumbani hostel.
    All other
    Single ladies without kids are housed in Manyaga hostel,
    Single Males without kids are housed in Belobelo Hostel.
    Married without Kids go to Honey hostel and Married with Kids go to Benba Hostel.

  27. i have 3 conditions
    1. if A1-cell value is a +ve value then result in B1 as "Excess"
    2. if A1-cell value is a -ve value then result in B1 as "Short"
    3. if A1-cell value is a 0 value then result in B1 as "nul"

    1. ve? =IF(A1>0,"Excess",IF(A1<0,"Short",IF(A1=0,"null")))

  28. IN A COLUMN I HAVE "1,2,3" AND I WANT TO COVERT THE NUMBERS TO "WORKERS,CLEANERS,DRIVER". BECAUSE THAT IS WHAT THEY STAND FOR AND ALL THE OTHER COLUMNS HAVE TWO OR THREE NUMBERS THAT REPRESENT THE WORDS GIVEN ABOVE. THANK YOU.

    1. Hi, Grace,
      if you want to change the data in one and the same cell and you don’t want to do it manually, you need to use a slightly different Excel function – Replace. It's very easy to use, and here is an article that will help you with the task:
      How to replace one value with another
      But if you have a huge table with tons of values to be replaced, you can try VLOOKUP function.

  29. Hello since I see new posts I will ask. I have tables which I am entering numbers into that total different each time I enter new numbers and total the sheet.

    These numbers for example let's say 11+11+11+11 = 44 are then totalled into a new cell and added with +3. Okay so far I have a number in the example of 47.

    I want to use your formula with the number 47 (remember this will change) that I just generated to recall a value with it.

    Example) On page 2 I have values for each number.
    11 100
    20 300
    35 600
    47 750
    etc

    I want to get the formula to upon result of my number (say 47) to then pull the value associated with that. I've tweaked it to say on a specific value of 47 to pull the 750 into the cell, but that is by manually entering the one value. I need it to do a range of values in one forumla to pull from the list.

    Is this possible? or am I in the wrong formula.

    Thank you

    1. Hello, Dean,

      you were looking for a formula in the wrong article, because you need not IF, but VLOOKUP function.
      For example:
      on 'Sheet7' in A1:A4 we have the numbers. In B1 we put:
      =SUM(A1:A4,3)
      to sum them up and add 3.
      Then in C1 we use the next VLOOKUP function:
      =VLOOKUP(Sheet8!A4,Sheet8!A1:B4,2)
      to return the value of the corresponding number from 'sheet8'.
      'Sheet8' contains a little table with the possible results in A1:A4 and their corresponding values in B1:B4.

      Hope you won't have same troubles in the future!

  30. Hello everyone,

    Please help me to add another formula in this line that word will convert to a value of number. Let's say for example I type word "SL" in the other sheet, then in the other sheet must appear the value of number I set for that word. Thank you.

    Here's the formula:

    =IF('Feb PTO'!M6="","",IF('Feb PTO'!M$5-'Feb PTO'!M6>14,1.2,IF('Feb PTO'!M$5-'Feb PTO'!M6>7,1.1,IF('Feb PTO'!M$5-'Feb PTO'!M6>2,1,IF('Feb PTO'!M$5-'Feb PTO'!M6=1,0.9,0.8)))))

  31. hi
    i am using past date and future date fuanction but some date not found
    then i am change some fanction like
    =if(date-today()>0,"future date",if(today()-date>0,"past date"))
    and i found all date right can i use this or not
    in your foumula what is 30?

  32. In A column 1 raw there is a name & B column 1 & 2 row address.
    same as 3 raw there is another name & 3 & 4 & 5 row address.
    So i have to merge that data in column C against name in same address single row

  33. Re-writing some details from above question for clarity.

    C . D
    02:00 . 5
    02:10 . 7

    .... if time is not 02:00 then D1 becomes ......

  34. I have cell A1 which changes with time. ( e.g. a1=5 @2:00, a1=7 @ 2:10 etc )

    I want to tabulate A1 data against time like

    C D
    02:00 5
    02:10 7

    Problem is I can get 5 in D1 @02:00 but if time 02:00 then D1 becomes either FALSE or = current value, depending on formula.

    How to keep 5 unchanged in D1?
    I have not meddled in Macro anytime.

    Thanks in advance.

  35. I'm trying to figure out how to write a formula to compare mileage from two columns. I'm tracking service intervals on fleet vehicles. when a current mileage is within 1,000 miles i want it to turn yellow. if its within 100 miles i want it to turn red. can anyone help me please. thank you in advance

  36. Am I able to have text in one cell determine if a number in another cell is negative or positive?

    If cell A1 says "withdrawal" I would like the number in cell B1 to be negative. If cell A1 says "deposit" I would like the number in cell B1 to be positive.

    1. Assuming, that column B already contains positive numbers, the next formula will do:
      =IF(A1="withdrawal",-B1,B1)

  37. Hello,

    I can't figure out what's wrong with the following:

    =IF(AK2=1, AB2*365,IF(AK2=2, AB2*52,IF(AK2=3, AB2*12,IF(AK2=4, AB2, 0))))

    Column AB is 'number of times' a person does something and column AK is 'during which time period' where 1=per day, 2=per week, 3=per month and 4=per year. Now, with this forumla, I'm trying to standardize these into single variable 'times per year' in order to calculate averages etc.
    Excel just doesn't understand my formula although to me it seems what I see in numerous examples.

    1. Ha! figured it out. Instead of comma, I have to use semicolon.
      Does anyone know where that comes from? Actually, I recall having this trouble earlier and spending hours trying to figure out what's wrong. Unfortunately my memory is good but short. Maybe next time...

      1. Where? Do you mean every comma has to be changed to semicolon?

        I have not tried, but I think we may get syntax error.

  38. Hello,

    I can't figure out what's wrong with the following:

    IF(AK2=1, AB2*365,IF(AK2=2, AB2*52,IF(AK2=3, AB2*12,IF(AK2=4, AB2, 0))))

    Column AB is 'number of times' a person does something and column AK is 'during which time period' where 1=per day, 2=per week, 3=per month and 4=per year. Now, with this forumla, I'm trying to standardize these into single variable 'times per year' in order to calculate averages etc.
    Excel just doesn't understand my formula although to me it seems what I see in numerous examples.

  39. Hello,
    Need some help on multiple conditions.

    IF A1 = A, D & M
    True = Fill up form A
    False = Fill up form B
    (Which A1 consist of A-Z)

    1. Hello, Patsy,
      please, give us more details on your task, so we could assist you.
      Do you want to fill the forms with colour or some value? What do you mean by "Which A1 consist of A-Z"?

  40. Hello,
    Need some help on multiple conditions.

    IF A1 = A, B & C
    True = Fill up form A
    False = Fill up form B
    (Which A1 consist of A-Z)

  41. Hi Sir/mam

    my query is

    if A1 divisible of 28(like 280) then system show in B1 (280/28*30=300)else system show b1=A1

    A1=280 B1=300
    A1=30 B1=30

    plz help me

  42. Hi,
    I am working in a company and I am using ms excel 2013. I want to know that how can i use date formula that if any date less than or equal to 31-1-2016 than comment This is old.

    Please clear me date formula. i am very confused.

    Thanks & Regards
    Dushyant Kumar

  43. Hi
    Please can you help.
    I have a holiday formula see below, that H = 1 days, but I also need it to show if the member of staff decides to take a half day. I can't get the formula to work?????

    COUNTIF(C12:AG12,"H")

    Thanks in advance.

    1. Hi, Lynsey,
      if half day is 'HD', then the formula will look like:
      =COUNTIF(C12:AG12,"H")+COUNTIF(C12:AG12,"HD")
      But it will return the sum of both: complete and half days taken. If you want them to return two results separately, put each part of the formula in different cells.
      To learn more about how this formula works, read this article.

  44. I need help creating a formula in cell E120.
    If E1190,0
    I would like the formula to return the percentage of X.

    2. Not sure, why my information keeps getting cut off. I'll try again....
      If E1190,0. I would like the formula to return the value of X%

  45. I need help creating a formula in cell E120.
    If E1190 then 0

    Thank you.

  46. Hi
    I require If furmula, in which emmotiation can be place with colour (smiling- green, angry-Red)

  47. Hi guys,

    I have recieving checklist for kitchen which I using as a refence for traceability. By the way I have columns A1 Date , B1 Delivered , C1 ID# , D1 Time Stamp.

    A1 Date , B1 Delivered , C1 ID# , D1 Time Stamp
    Ctrl + ; YES/NO I+A1TEXT =IF(B7="YES",IF(F7="",NOW(), F7), "")

    Row D I filling by this formula (for example) =IF(B7="YES",IF(D7="",NOW(), D7), "") only IF YES at B7, But I want to add next to that what OR IF B7 NO (if B7 NO than I will keep from previous D6). Because when I type in my B7 no than my D7 is empty, I dont want empty, I want record from previous (D6).

    I was recieving Salmon, and in recieving checklist I adding date of recieving (A1), than did I recieve that day or no (B1), than ID Number (C1) with I for ingredient and 6 digits date of recieving and time stamp (D1) same I for ingredient and 6 digits date of recieving.

    If I did not recieve today than row D I want to keep date of last recieving.

    Many thanks,
    Danilo

  48. pls. advice how to arrive 0 in the same cell if amount is -1, with along with below formula.

    =(AF9-300000)*10%

  49. A1 Cell Value 21/03/2017

    in Cell B i would like the Valve to be M31721
    Where M is for March
    3 is from Cell A1 (03 MM)
    17 is from Cell A1 (17 YY)
    21 is from Cell A1 (21 DD)

  50. hi i want to check where there term is more or less than one year or five year. i know to claculate the term but i dont formula now how to check the term with conditions

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