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

  1. Hello

    I Hope you can help me with this,

    I want to create a formula where I can say this cell is = to a cell in another sheet, but if its blank, leave it blank, otherwise place the information

    example

    =IF(MASTER!D60,"","???") WHAT I WANT IS TO SAY THAT THIS CELL IS = TO THE OTHER CELL AND IF THE CELL IS BLANK, LEAVE IT BLANK DONT PLACE ANY NUMBER OR FORMULA OR ANYTHING

    thank you in advance

    1. if i got it right, i think it goes like this:

      =if(D60="","",D60)

      /this says if the cell d60 is empty(""),leave it empty(""),and if not input the text from it here.

      1. Good Day
        Thanks for the above answer
        if i got it right, i think it goes like this:

        =if(D60="","",D60)

        /this says if the cell d60 is empty(""),leave it empty(""),and if not input the text from it here.

        So I have a situation whereby I have 4 columns (A,B,C,D), I have information in either of the columns. I want the information to be populated in column E.
        I found the above if function don't work for multiple column ranges or maybe I just get it wrong. Please assist

  2. Hi,

    I hope you can help

    I am using this formula, but the cell I am using it in shows £300 when C4 is 0.00.

    When C4 is 0.00, I also want the cell to show as 0.00 until any information in entered, how do I incorporate this into the formula?

    =IF(C4=300,0,C4))

    Thanks in advance

    1. Hi James,

      Here you go: =IF(C4=0,0,C4)

      1. I want a formula if the ans is NO in one column then 888 in the next coulum

      2. Part 2

        IF(C5>=300,0,C5))

        1. James,

          Our blog engine often mangles formulas with comparison operators, sorry for that. If my understanding is correct you want the formula to return 300-C5 if cell C5 is less than 300, otherwise return 0:
          =IF(C5<300,300-C5,0)

          If you are looking for something different, please express the conditions in words and we will try to work out a proper formula.

      3. Sorry my formula didn't come up as I typed it, I'll try sending it in 2 parts

        =IF(C5<300,300-C5,

  3. I need a formula that will satisfy these conditions. The nominee must be male or female with more than 500 votes to qualify. My formula was wrong. Any suggestions?

    1. Hi Johnny,

      If you give more details about your data structure, I think we will figure out a proper formula.

  4. i want to use a formula for good or bad
    Examaple if i entre D6 value should have condition =< 12 is good and lower than 11.8 should be bad .

    Please help in how to use formula

    1. Hello Vishnu,

      Here you go:
      =IF(AND(D6<=12, D6>=11.8), "good", IF(D6<11.8, "bad", ""))

  5. Hi Svetlana, This is Amit.
    I want to check if numbers in first cell (3 numbers i.e.279) are matching with with second cell (8 numbers i.e. 27895613) and write result as "Match" or "Not Match" in third cell.
    for example,
    1. First cell - 123 Second cell 12435678 Match
    2. First Cell - 231 Second cell 23457698 Not Match

    1. Hi Amit,

      Try the following formula:

      =IF(A1=LEFT(B1,3)*1, "match", "not match")

      It compares a 3-digit number in cell A1 with the first 3 digits of a bigger number in cell B1.

  6. I need help creating a formula, please! I need column C to return today's date, only if column A and B both have a date in them. So if only one of the columns have a date in it then column C needs to remain blank. Column A already has an IF formula in it to populate it with today's date if another criteria is met. Hope there is someone who can help, as I am relatively new to formulas...Thanks!

    1. Hi Alice,

      If row 2 is the top-most row with data in your worksheet, you can enter the following formula in C2 and then copy it down to other cells in column C:

      =IF(AND(A2<>"", B2<>""), TODAY(), "")

      1. I was sure I had tried every variation possible, but clearly not! This has worked perfectly, thank you - I was about ready to scream :)

  7. Hi Svetlana,

    if have part of a If function =IF((AND(F33="Mild steel",F34="1mm")),"1.3".....

    is it possible to change to vale 1.3 to a cell that has the value in it, for example..

    =IF((AND(F33="Mild steel",F34="1mm")),"= F28"?

    if not. is there any way the "value" could be what is written in a cell?

    thank you in advance.

    Stephen

    1. Hi Stephen,

      If my understanding of the task is correct, here’s the formula you are looking for:

      =IF(AND(F33="Mild steel", F34="1mm"), F28, value_if_false)

  8. Give me a formula for:
    If c4=10 there will be 2500 commision, or if c4=15 there will be 5000 commiosion

    1. Hello ZAHIR,

      Here you go:

      =IF(C4=10, 25000, IF(C4=15, 5000, ""))

  9. i have two sheets 1 & 2. I need to put the employee names on sheet 1 columns a1:a10 to sheet 2 column d...what should be my formula? i want to automatically put there instead of retyping or copying it

    1. Hello!

      Just enter the following formula into cell D1 on Sheet2 and then copy it down to other cells:
      =Sheet1!A1

  10. somebody let me know what does it mean by formula =IF(A7="","",A7) in excell

    1. Hello Waqar,

      Your formula reads as follows:

      "If A7 is blank, return an empty sting (blank cell), otherwise return a value in A7."

  11. is it possible to have a formula for multiple IF's or another formula for if A2 is upto 127.99 the output would be 15.33,if A2 is between 128 and 187.99 the output would be 35.15 and if A2 was 188 and 244.99 the output would be 48.26.

    Thanks

    Jason

    1. Hi Jason,

      You can use the following nested IFs:

      =IF(A2<=127.99, 15.33, IF(A2<=187.99, 35.15, 48.26))

      The above formula returns 48.26 for any number greater than 188. If you want to limit it to 244.99, add one more IF statement that would return an empty string for all numbers greater than 244.99:

      =IF(A2<=127.99, 15.33, IF(A2<=187.99, 35.15, IF(A2<=244.99, 48.26, "")))

  12. Hi Svetlana Cheusheva,

    Kindly if you could guide, If E13 is equal to "salary" (text), it should multiply the values mentioned in Cell L13 and $M$10, otherwise N/A. Thanks

    1. Hi Aamir,

      Here you go:

      =IF(E13="salary", L13*$M$10, "N/A")

  13. Hi Dear,
    I need a formula to G16 where if F16 says "YES" then G16=C16-8

    1. Hi Fatema,

      Here's the formula for G16:
      =IF(F16="yes", C16-8, "")

  14. A1 = RECEIVED / CANCELLED / DECLINED (Dropdown)

    If A1="CANCELLED" then A2, A3, A4... will show CANCELLED
    If A1="DECLINED" then A2, A3, A4... will show DECLINED

    but

    If A1="RECEIVED" then A2, A3, A4... must be blank

    Please advise. Thank you

    1. Hi Brenda,

      Here's the formula exactly per your conditions:

      =IF($A$1="CANCELLED", "CANCELLED", IF($A$1="DECLINED", "DECLINED", IF($A$1="RECEIVED", "", "")))

      And I believe this simpler one will work as well:
      =IF($A$1="RECEIVED", "", $A$1)

  15. how to Segregate the Employees Categories from Excel sheet through Formula ? for example I have too many employees job title so I want segregate their profession project location wise through formula how can we do please help.

    1. Hi, Svetlana,

      how to Segregate the Employees Categories from Excel sheet through Formula ? for example I have too many employees job title so I want segregate their profession project location wise through formula how can we do please need your help to give me the Idea Soon.

  16. hi,i m having problem while calculating IF function with time.All cell are formatted for h:mm.
    A1 for start of duty say5:00,B1 for getting machine ready(allowed 2:00)say 8:00(more by1:00).now C1 shows working hours for machine(max value 4:00 hour).formula may be =IF((B1-A1)>2,4-((B1-A1)-2),4),HOLD GOOD for for GENERAL formate.with TIME format h:mm shows error/invalid.please help.

    1. HI SWETA,I TRIED FOLLOWING
      IF((time(hour(I17-G17),MINUTE(I17-G17),SECOND(I17-G17)))<=2:00:00,4:00:00,4:00:00-(time(hour(I17-G17),MINUTE(I17-G17),SECOND(I17-G17))-2:00:00)),BUT DIDNOT WORKED.

  17. Hi,

    I need some assistance with this...

    For Example

    =IF(G6>0,"FR")AND(H6+I6+J6=0,"NR")

    1. Hi Iris,

      I believe the correct syntax is as follows:
      =IF(G6>0,"FR", IF(H6+I6+J6=0,"NR", ""))

  18. Hello Everyone,

    If I could please have your help.
    =IF(AND(D4="",TODAY()>=C4,OR(AND(D4"",D4>=C4))),"Yes","No")

    I need an if function that con do the following:
    If return date D4="", and today's date is greater or equal to C4 then Yes, otherwise No. If D4 is not equal to empty, then D4>=C4, Yes otherwise no.

    I can't seem to figure it out =(

    1. I am not sure to have fully understood your query but try this way..

      =IF(D4>0,IF(TODAY()>=C4,"YES","NO")

  19. I have door numbers listed in boxes across. I need a formula that totals the number of boxes filled in with any number. 201, 202, 203 = 3

    1. =@COUNT( DID IT.

  20. Hi I am trying to use check boxes to add up a list of items one might purchase and give me a sum total. I tried the formula for just one box but it keeps giving me zero instead of the dollar amount I expecting ($5).

    The formula I am using is =IF(D3="true",C3, 0)
    In this case D3 is true
    C3 is $5
    The result comes back as 0

    Any ideas?

    1. Hi Paul,

      Because TRUE and FALSE are Boolean values, you don't need to enclose them in quotes, and your formula will work perfectly:
      =IF(D3=TRUE,C3, 0)

      1. Svetlana that works perfectly, now for part two which I thought I could figure out if part one worked. I have a list of prices that if they are true I would like them to total up at the bottom. I tried =IF(D3:D14=TRUE,SUMC3:C14, 0) but it doesn't work

        1. Paul,

          For this, you'd better use the SUMIF function:
          =SUMIF(D3:D14, TRUE, C3:C14)

          The above formula sums values in column C if a cell in the corresponding row in column D is TRUE.

          1. Thank you again, that worked.

  21. Hi!

    Good day! I was trying to link the formula from one cell to another.
    sample: =IF(D14=0,"Required Field","") ((This is under E14)
    Formula cannot be used id created under D14.

    Or are there any other formula that can produce the same answer which is "Required Field"

    Your help is much appreciated.

    Thank you.

    1. Hi Jenah,

      >Formula cannot be used id created under D14.

      Absolutely right. You cannot enter a formula in a cell that contains other data. If you enter it in E14, the formula should work fine.

      Please note that D14=0 checks D14 for 0. If you want to check for an empty cell, use D14="".

  22. Hi!

    Good day! I am currently working on an Application Form. I don't want them to missed any required fields. Is there any formula that we can use to prevent them in missing any fields or "this is a required field" will prompt on each missed out cell.

    =IF(D6=" "," ","Required Field")is not working. Help please!

    Thank you!

    1. Hi Jenah,

      If you want the formula to return "Required Field" when D6 is empty, you should put it the other way round:

      =IF(D6="","Required Field", "")

  23. Hi Svetlana,

    I am looking for a formula that will look across a range of values to see if any of the values contain a specific value (note: the values will never be exact) and then present the match in another range of cells.

    For example: in column A I have a list of songs:
    A1: free bird
    A2: you really got me
    A3: the pretender
    A4: the bird is the word

    I'd like to look across the range A1:A4 and then display the name of each song ONLY if it contains the word "bird" in it. So in column B for example: B1: free bird and B2: the bird is the word would display.

    1. Hi Joe,

      Here you are:
      =IF(ISNUMBER(SEARCH("bird",A1)), A1, "")

  24. Hi Svetlana!
    Please help me with my formula. This is how it goes: If A1 to A5 is greater than or equal to 3 and less than or equal to 10, show "OK" but if not, show "NG". This is the formula I have come up with but it doesn't seem to work: =IF(AND(A1:A5>=3,A1:A5<=10),"OK","NG")

    Thank you in advance and I'm looking forward for your reply.
    -Paul

    1. Hi Paul,

      The IF function does not work correctly with ranges, you need to reference each cell individually, like this:

      =IF(AND(AND(A1>=3,A1:A5<=10), AND(A2>=3,A2<=10), AND(A3>=3,A3<=10), AND(A4>=3,A4<=10), AND(A5>=3,A5<=10)), "OK","NG")

      Alternatively, you can use a more compact array formula (remember to press Ctrl+Shift+Enter to enter it correctly):

      =IF(SUM((A1:A5>=3) * (A1:A5<=10))=5, "ok", "ng")

  25. Hi There,

    Want to multiply cell B & C if cell A is greater than 0. Anything less than 0 in cell A should return 0.

    Thanks,
    Agana

    1. Hi Agana,

      Here you go:
      =IF(A1>0, B1*C1, 0)

  26. Please help!

    I need a formula that looks at a cell.EG A1

    If A1 says “card” or “mail order” then it needs to take the value off another cell (B1) and times it by 0.03. If however A1 says “defaults” it needs to take the value in B1 and times it by 0.015.

    This is what i have so far: it doesn’t work :(

    =IF(A11=”Card”,b1*0.03,IF(A1=”Mail Order,B1*0.03,IF(A1=””Defaults”,B1*0.015))

    1. Hi Kattrina,

      You just have 1 incorrect cell reference (A11 instead of A1) and a missing closing parentheses at the end of the formula. Once you fix this, the formula works just fine:
      =IF(A1="Card",B1*0.03,IF(A1="Mail Order",B1*0.03,IF(A1="Defaults",B1*0.015)))

      To make the formula a bit more compact, you can add the OR statement, like this:
      =IF(OR(A1="Card", A1="Mail Order"), B1*0.03, IF(A1="Defaults",B1*0.015))

  27. Hi, im looking for a formula that will help me with the below example:
    A1=600
    A2=8
    A3=122
    quantity over 50 for A3 to be multiuplied by A2, plus A1.
    if A3 is 50 or under, to display A1 only.

    please help.

    thanks, Rob

    1. Hi Rob,

      You can use the following formula:
      =IF(A3>50, A3*A2+A1, A1)

      1. Please I want a formula that can count through cells like C2:F2 to check for grade “A1” -“C6” = pass but if there is “D7” - “H” = fail

  28. Hello,

    I am looking for a formula that will return "rural" if the second character of postal is "0".

    N5Z3J2
    L5R3S6
    L2M4G3
    M2M3R1
    N0G1L0

    So far I have this :

    =IF(ISNUMBER(SEARCH("0",M2)), "rural", "city")

    Anyone knows how I can target the second digit only?

    Thanks,

    1. Hi Daniel,

      Try this one:
      =IF(MID(M2,2,1)="0", "rural", "city")

  29. hello
    I work in two sheet with if function
    =IF('Sheet1'!G12="WBGELD";"C";"A")
    that function is in second sheet and i like when the cell is empty in first sheet to have empty blank cell and in second sheet
    thank you kind regards

    1. Hi Ismaili,

      Just add one more IF function to check for a blank cell:

      =IF('Sheet1'!G12="";""; IF('Sheet1'!G12="WBGELD";"C";"A"))

  30. Hello,

    help me in a formula in when :
    for example : a1*b1= in_cell_ c1
    -am need if this number in cell c1>2.00 keep the cell c1 the same vale (i mean keep the result a1*b1 without change ).
    -and if the number in cell c1<2.00 replace the cell c1 to 2.00 automatically
    thank you

    1. Hello Razzouk,

      Here's the formula for C1:

      =IF(A1*B1>2, A1*B1, 2)

  31. I need a formula that will sum if the value in both cell a & b are greater than 0. If one or both are less than 0 I need the sum to be 0. So if I have a 4 in cell a and a 0 in cell b I need the sum in c to equal 0. If cell a and b are both 4 I need c to equal 8.
    Is there a way to do this formula? Thank you

    1. Hi Tegan,

      Here you go:

      =IF(OR(A1<=0, B1<=0), 0, A1+B1)

  32. I am looking for a formula to show date from today in a specific cell.
    So I want to put a date in a1, amount in b1 and I want c1 to always show TODAYS amount. Does that make sense? I provided a visual below.

    Example: Amount today
    Date Amount 57
    9/25/15 57

    I tried the following and it did not work
    =IF(A1=TODAY(), "B1")
    Any ideas would be great.

    1. Hi VIKING,

      You were on the right track, just remove the quotes surrounding B1 because they turn a cell reference into a text string:
      =IF(A1=TODAY(), B1)

  33. Hello Svetlana,

    I want to use the IF logic for the following, but don't know how to set it up:

    If B2=I, and H2=>15, I2 should =Yes

    If B2=R, and H2=>30, I2 should =Yes

    If B2=I, and H2=>45, I2 should =Yes

    If any of these are present and H2 is < then I2 should =No

    Hopefully, you can help me because this newbie is lost.

    All of the logic is based on the Letter in B2, and the time in B3.

    1. Hi Alvin,

      Here's the formula for I2 as per your conditions:

      =IF(OR(AND(B2="I", H2>=45), AND(B2="R", H2>=30)), "yes", "no")

      1. Svetlana,

        Thank you very much.

        Alvin

        1. Svetlana,

          Now the employee wants it to be time, can you show me how to convert the formula to time?

  34. I cant figure this out.
    If G6 equals "HPHe" then I want it to enter the value of P10, but if G6 equals "OFA" then I want it to enter the value of P11

    =IF(OR(G6="HPHE",P10,"")=IF(G6="OFA",P11,""))

    1. Hi Nick,

      You put it like this:

      =IF(G6="HPHE",P10, IF(G6="OFA",P11,""))

  35. =IF(B37="","",IFERROR(COUNTIFS(BASF_GLOBAL!$AI:$AI,"Recommended",BASF_GLOBAL!$D:$D,$B37),""))

    Can anyone tell me what B37="," means when B37 is a vell containing text value.

    1. Hi Richa,

      "" is an empty string. So your formula reads as follows: when B37 is empty, return nothing (blank cell), otherwise return the result of COUNTIFS. If COUNTIFS returns an error, then also return an empty string (blank cell).

  36. HI

    i need to know the formula for

    if A1 is US b1 value should be 1000 if A1 value is indiab1 value should be 2000.

    The value is fixed

    thanks

    1. Hello Prabhu,

      Here you go: =IF(A1="US", 1000, IF(A1="indiab1", 2000, ""))

  37. Hello!!

    I need help with this formula : =IF(COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, (Stock3) - 6)

    This works perfectly but I need to add "Right Triple Track" into the same formula for entire column Barcode:Barcode

    I tried =IF(COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, (Stock3) - 6),(Barcode:Barcode, "Right Triple Track") > 0, (Stock3) -6 but it didn't work.

    1. Hello Martin,

      If you want the Barcode column to contain both "Left Triple Track" AND "Right Triple Track" at the same time, then use the following formula:

      =IF( AND( COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, COUNTIFS(Barcode:Barcode, "Right Triple Track") > 0) , (Stock3) – 6)

      If it is sufficient for the Barcode column to contain either "Left Triple Track" OR "Right Triple Track", then use:

      =IF( OR( COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, COUNTIFS(Barcode:Barcode, "Right Triple Track") > 0) , (Stock3) – 6)

  38. Hi - I have an "IF" formula that I want to sum up but it is giving me "0" even when the "IF" formula's have a total.

    =IF(D4>1,"1","") this goes from A4 to A 103, I want A3 to be =Sum(A4:A103)

    What am I doing wrong? :(
    Thanks!

    1. Hi Rebecca,

      To add up cells that meet a certain criteria, use the SUMIF function like:
      =SUMIF(D4:D103,">1",A4:A103)

  39. HI,
    KINDLY HELP ME IN I HAVE A RANGE OF DATES VARYING FROM 0 TO 60. I NEED TO CATEGORIZE INTO FOLLOEING GROUPS IF DATE RANGE IS BETWEEN 60 TO 30 IT SHOULD RESULT > 30DAYS, 15 TO 30 AS > 15 DAYS, 5 TO 15 AS > 5 DAYS AND 0 TO 5 AS < 5 DAYS..

    =IF(OR(I25,"<5 DAYS",I2=30,">30 DAYS"),">DAYS")

    1. Hi KARTHICKEYAN,

      You can use a nested IF formula similar to this:

      =IF(I2>30, ">30 DAYS", IF(I2>15, ">15 DAYS",IF(I2>5, ">5 DAYS", "< 5 DAYS")))

  40. Hi I am unable to figure this out. I need a formula that says: If any of the cells Barcode:Barcode contain the term "1 In Production", then add the text "In Production" to corresponding [In Production]1" cell.

    Many Thanks

    1. Hi Martin,

      Try a formula similar to this:

      =IF(COUNTIF(A1:A10, "1 In Production")>0, "In Production", "")

      Where A1:A10 is the range of cells to check for the term "1 In Production".

      1. Works perfectly!!!!

        You saved my life!!

        Thank You

  41. Hi,
    I have 3 columns (A, B, and C) and I want to make a list of text of column A based on the conditions of column B and C. It is somewhat like "if B = "text" and C = "text" => copy the text in cell column A and paste to the new cell.
    How is the formula for that?

    Thanks.

    1. Hi Karissa,

      You can use a formula similar to this:
      =IF(AND(B1="x", C1="y"), A1, "")

  42. I am looking for a formula that will compare 2 cells on a sheet, if they both contain the letter o then the number 25 is to appear in a third cell, if one or both cells are blank, then 0 is to appear. i.e. if cell AE6=o and cell AE20=o then cell AF6 is to say 25. If either cell AE6 or cell AE20 (or both) are blank then cell AF6 is to say 0. Please help, thank you.

    1. Hi Chris,

      You can use a nested IF formula like this:

      =IF(AND(AE6="o", AE20="o"), 25, IF(OR(AE6="", AE20=""), 0, ""))

      1. Thank you. I am also looking for a formula that will post a symbol if 1 of 2 different cells = a value of 1, and fall within a of a number generated in another cell. Here is the formula that I am trying to use: =IF($E$7=1,IF(AND($L$18>=190,$L$18=160,$L$18<=164),"•")),"")))
        Let's say that this formula is in cell A1, and when E7 is "activated" by equaling 1 a random number is generated in cell L18. If the number in that cell falls between or equals 190 and 194, I want a • to appear in cell A1, if not, then blank (unless the following formula is true). In the same cell A1, when D6 is "activated" by equaling 1, a random number is generated in cell L18, and if that number falls between or equals 160 and 164, I want a • to appear in cell A1, if not then blank (unless the other formula is true). Both E7 and D6 will never be "activated" at the same time. My current formula returns a #VALUE report in the cell.

        Thanks in advance!

        1. Hello Chris,

          Please use:
          https://www.ablebits.com/office-addins-blog/excel-and-or-xor-not-functions/

  43. HI

    I have a problem where I have to write an if function for "if the last digit in a number is even enter 1000 if not, 0". I can't figure out how to put the "last digit is even" part into the formula to make it work in excel.

    Thanks in advance

    1. Hi Peter,

      You can use the RIGHT function to get the last digit and ISEVEN to find out whether it's even. For example:
      =IF(ISEVEN(RIGHT(A1,1)), 1000, 0)

  44. I want to create a formula. I have 5 types of value like 4.17, 4.33, 4.50, 4.67, and 4.83 I want if 4.17 comes in a cell the dependent cell will show 4 min 10 sec. For 4.33 it will show 4 min 20 sec, 4.50 show 4 min 30 sec, 4.67 show 4 min 40 sec, 4.83 show 4 min 50 sec and 4.00 show 4 min. How can I do it in excel?

    1. the value before decimal is min like 6.17 will represent 6min 10 sec

  45. Hi. I just wondering what is the formula to get Overtime Hours. The Normal hours is 8 and the Overtime Rate 1.5.
    Hope you can help me with this.

    Thank you.

    Louie

    1. I'm having problems trying to figure out the first name, last name, hours worked per day on a five-day workweek, with hours. worked 45, hourly wage $9.75, overtime hrs.5 at a time and a half, and the total earnings.please help me, I will be forever grateful.

  46. i have cell A1 with value 1.5.
    i want a value in cell A2. if value in cell A1 is less than 0.8, 0.8 should come in A2, but if value in Cell A1 is more than or equal to 0.8, same value should come in A2. like in my case 1.5.

    i tired this =IF(A1<0.8,"0.8","A1"), but its not working. i am getting text A1 in cell A2 not value 1.5

    please reply

    1. Hi Rahul,

      In Excel formulas, numbers and text references are not supposed to be enclosed in quotes. As soon as you remove them, your formula will work fine:
      =IF(A1<0.8, 0.8, A1)

  47. Hi id like to create a formula using If condition wherein you have three ..
    For example

    I use =if(or(c27=40,c19*c26,c27=60,c20*c26,c27=80,c21*c26)) how can i checj this?it is always error

    1. Hi Grace,

      Use a nested If formula like this:
      =IF(C27=40, C19*C26, IF(C27=60, C20*C26, IF(C27=80, C21*C26, "")))

  48. Thanks Svetlana, that works for my intent and purposes with a bit of mods. Now if you can help me with one more....
    I have 2 sheets containing about exact same data, but Sheet1 has several cells in Column B highlighted in Blue. How can I run a formula to color the matching cells on Sheet2 to be blue?

    1. Hi Jay,

      If you want to highlight cells on sheet2 that have the sames values in the same cells on sheet1 (regardless on cells' background color or other formatting), you can create a conditional formatting rule with the formula similar to this:
      =$B1=Sheet1!$B1

      For the detailed step-by-step instructions, please see:
      https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/

      If you want to highlight cells on sheet2 corresponding to blue cells on sheet1, there is no formula for this, and the only way is writing a VB script.

  49. Hi guys, extremely helpful site. I have a simple one. I have a sheet with several names in say Column B. Column C is looking for the City. If Sam is found in Column B, return Toronto. If Joe is in Column B, return London. Sam, Joe and the other names occurs multiple times in Col B.

    Thanks

  50. Please help if possible!
    Basically due to weekends (C Column is date included in metric sheet which i can change for data to be used within charts) some values in the F&G Column are 0 so it is returning #DIV/0!. I would like it if the values are 0 to put a "-" in. Is this possible?

    This is the formula i have at the moment.
    =IF((C3="Yes"),(F10/G10),"")

    1. Hi Ryan,

      Enclose your IF formula in the IFERROR function, like this:
      =IFERROR(IF(C3="Yes", F10/G10, ""), "-")

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