Using IF function in Excel: formulas for numbers, text, dates, blank cells

The IF function is one of the most popular and useful functions in Excel. You use an IF statement to ask Excel to test a condition and to return one value if the condition is met, and another value if the condition is not met.

In this tutorial, we are going to learn the syntax and common usages of Excel IF function, and then will have a closer look at formula examples that will hopefully prove helpful both to beginners and experienced Excel users.

Excel IF function - syntax and usage

The IF function is one of Excel's logical functions that evaluates a certain condition and returns the value you specify if the condition is TRUE, and another value if the condition is FALSE.

The syntax for Excel IF is as follows:

IF(logical_test, [value_if_true], [value_if_false])

As you see, the IF function has 3 arguments, but only the first one is obligatory, the other two are optional.

  • logical_test (required) - a value or logical expression that can be either TRUE or FALSE. In this argument, you can specify a text value, date, number, or any comparison operator.

    For example, your logical test can be expressed as or B1="sold", B1<12/1/2014, B1=10 or B1>10.

  • value_if_true (optional) - the value to return when the logical test evaluates to TRUE, i.e. if the condition is met.

    For example, the following formula will return the text "Good" if a value in cell B1 is greater than 10: =IF(B1>10, "Good")

  • value_if_false (optional) - the value to be returned if the logical test evaluates to FALSE, i.e. if the condition is not met.

    For example, if you add "Bad" as the third parameter to the above formula, it will return the text "Good" if a value in cell B1 is greater than 10, otherwise, it will return "Bad": =IF(B1>10, "Good", "Bad")

Excel IF function - formula example

Excel IF function - things to remember!

Though the last two parameters of the IF function are optional, your formula may produce unexpected results if you don't know the underlying logic beneath the hood.

  1. If value_if_true is omitted

    If the value_if_true argument is omitted in your Excel IF formula (i.e. there is only a comma following logical_test), the IF function returns zero (0) when the condition is met. Here is an example of such a formula: =IF(B1>10,, "Bad")

    In case you don't want your Excel IF statement to display any value when the condition is met, enter double quotes ("") in the second parameter, like this: =IF(B1>10, "", "Bad"). Technically, in this case the formula returns an empty string, which is invisible to the user but perceivable to other Excel functions.

    The following screenshot demonstrates the above approaches in action, and the second one seems to be more sensible:
    IF formulas with the value_if_true argument omitted

  2. If value_if_false is omitted

    If you don't care what happens when the specified condition is not met, you can omit the 3rd parameter in your Excel IF formulas, which will result in the following.

    If the logical test evaluates to FALSE and the value_if_false parameter is omitted (there is just a closing bracket after the value_if_true argument), the IF function returns the logical value FALSE. It's a bit unexpected, isn't it? Here is an example of such a formula: =IF(B1>10, "Good")

    Putting a comma after the value_if_true argument forces your IF statement to return 0, which doesn't make much sense either: =IF(B1>10, "Good",)

    And again, the most reasonable approach is to put "" in the third argument, in this case you will have empty cells when the condition is not met: =IF(B1>10, "Good", "")
    IF formulas with the value_if_false argument omitted

  3. Get the IF function to display logical values TRUE or FALSE

    For your Excel IF formula to display the logical values TRUE and FALSE when the specified condition is met and not met, respectively, type TRUE in the value_if_true argument. The value_if_false parameter can be FALSE or omitted. Here's a formula example:

    =IF(B1>10, TRUE, FALSE)
    or
    =IF(B1>10, TRUE)
    an example of the IF function that displays logical values TRUE or FALSE

    Note. For your Excel IF statement to return TRUE and FALSE as the logical values (Boolean values) that other Excel formulas can recognize, make sure you don't enclose them in double quotes. A visual indication of a Boolean is middle align in a cell, as you see in the screenshot above.

    If you want "TRUE" and "FALSE" to be usual text values, enclose them in "double quotes". In this case, the returned values will be aligned left and formatted as General. No Excel formula will recognize such "TRUE" and "FALSE" text as logical values.

  4. IF statement to perform a math operation and return a result

    Instead of returning certain values, you can get your IF formula to test the specified condition, perform a corresponding math operation and return a value based on the result. You do this by using arithmetic operators or other Excel functions in the value_if_true and /or value_if_false arguments. Here are just a couple of formula examples:

    Example 1: =IF(A1>B1, C3*10, C3*5)

    The formula compares the values in cells A1 and B1, and if A1 is greater than B1, it multiplies the value in cell C3 by 10, by 5 otherwise.

    Example 2: =IF(A1<>B1, SUM(A1:D1), "")

    The formula compares the values in cells A1 and B1, and if A1 is not equal to B1, the formula returns the sum of values in cells A1:D1, an empty string otherwise.

Using the IF function in Excel - formula examples

Now that you are familiar with the Excel IF function's syntax, let's look at some formula examples and learn how to use IF as a worksheet function in Excel.

Excel IF statement for numbers: greater than, less than, equal to

The use of the IF function with numeric values is based on using different comparison operators to express your conditions. You will find the full list of logical operators illustrated with formula examples in the table below.

Condition Operator Formula Example Description
Greater than > =IF(A2>5, "OK",) If the number in cell A2 is greater than 5, the formula returns "OK"; otherwise 0 is returned.
Less than < =IF(A2<5, "OK", "") If the number in cell A2 is less than 5, the formula returns "OK"; an empty string otherwise.
Equal to = =IF(A2=5, "OK", "Wrong number") If the number in cell A2 is equal to 5, the formula returns "OK"; otherwise the function displays "Wrong number".
Not equal to <> =IF(A2<>5, "Wrong number", "OK") If the number in cell A2 is not equal to 5, the formula returns "Wrong number "; otherwise - "OK".
Greater than or equal to >= =IF(A2>=5, "OK", "Poor") If the number in cell A2 is greater than or equal to 5, the formula returns "OK"; otherwise - "Poor".
Less than or equal to <= =IF(A2<=5, "OK", "") If the number in cell A2 is less than or equal to 5, the formula returns "OK"; an empty string otherwise.

The screenshot below demonstrates the IF formula with the "Greater than or equal to" logical operator in action:
the IF formula with the

Excel IF function examples for text values

Generally, you write an Excel if statement with text using either "equal to" or "not equal to" operator, as demonstrated in a couple of IF examples that follow.

Example 1. Case-insensitive IF formula for text values

Like the overwhelming majority of Excel functions, IF is case-insensitive by default. What it means for you is that logical tests for text values do not recognize case in usual IF formulas.

For example, the following IF formula returns either "Yes" or "No" based on the "Delivery Status" (column C):

=IF(C2="delivered", "No", "Yes")

Translated into plain English, the formula tells Excel to return "No" if a cell in column C contains the word "Delivered", otherwise return "Yes". At that, it does not really matter how you type the word "Delivered" in the logical_test argument - "delivered", "Delivered", or "DELIVERED". Nor does it matter whether the word "Delivered" is in lowercase or uppercase in the source table, as illustrated in the screenshot below.
Case-insensitive IF formula for text values

Another way to achieve exactly the same result is to use the "not equal to" operator and swap the value_if_true and value_if_false arguments:

=IF(C2<>"delivered", "Yes", "No")

Example 2. Case-sensitive IF formula for text values

If you want a case-sensitive logical test, use the IF function in combination with EXACT that compares two text strings and returns TRUE if the strings are exactly the same, otherwise it returns FALSE. The EXACT functions is case-sensitive, though it ignores formatting differences.

You use IF with EXACT in this way:

=IF(EXACT(C2,"DELIVERED"), "No", "Yes")

Where C is the column to which your logical test applies and "DELIVERED" is the case-sensitive text value that needs to be matched exactly.
Case-sensitive IF formula for text values

Naturally, you can also use a cell reference rather than a text value in the 2nd argument of the EXACT function, if you want to.

Note. When using text values as parameters for your IF formulas, remember to always enclose them in "double quotes".

Example 3. Excel if statement for text with partial match

If you want to base your condition on a partial match rather than exact match, an immediate solution that comes to mind is using wildcard characters (* or ?) in the logical_test argument. However, this simple and obvious approach won't work. Many Excel functions accept wildcards, but regrettably IF is not one of them.

A solution is to use IF in combination with ISNUMBER and SEARCH (case-insensitive) or FIND (case-sensitive) functions.

For example, if No action is required both for "Delivered" and "Out for delivery" items, the following formula will work a treat:

=IF(ISNUMBER(SEARCH("deliv",C2)), "No", "Yes")
IF formula for text values with partial match

We've used the SEARCH function in the above formula since a case-insensitive match suits better for our data. If you want a case-sensitive match, simply replace SEARCH with FIND in this way:

=IF(ISNUMBER(FIND("text", where to search)), value_if_true, value_if_false)

Excel IF formula examples for dates

At first sight, it may seem that IF formulas for dates are identical to IF statements for numeric and text values that we've just discussed. Regrettably, it is not so.

Unlike many other Excel functions, IF cannot recognize dates and interprets them as mere text strings, which is why you cannot express your logical test simply as >"11/19/2014" or >11/19/2014. Neither of the above arguments is correct, alas.

Example 1. IF formulas for dates with DATEVALUE function

To make the Excel IF function recognize a date in your logical test as a date, you have to wrap it in the DATEVALUE function, like this DATEVALUE("11/19/2014"). The complete IF formula may take the following shape:

=IF(C2<DATEVALUE("11/19/2014"), "Completed", "Coming soon")

As illustrated in the screenshot below, this IF formula evaluates the dates in column C and returns "Completed" if a game was played before Nov-11. Otherwise, the formula returns "Coming soon".
An example of the IF formula with the DATEVALUE function

Example 2. IF formulas with TODAY() function

In case you base your condition on the current date, you can use the TODAY() function in the logical_test argument of your IF formula. For example:

=IF(C2<DATEVALUE("11/19/2014"), "Completed", "Coming soon")

Naturally, the Excel IF function can understand more complex logical tests, as demonstrated in the next example.

Example 3. Advanced IF formulas for future and past dates

Suppose, you want to mark only the dates that occur in more than 30 days from now. In this case, you can express the logical_test argument as A2-TODAY()>30. The complete IF formula may be as follows:

=IF(A2-TODAY()>30, "Future date", "")

To point out past dates that occurred more than 30 days ago, you can use the following IF formula:

=IF(TODAY()-A2>30, "Past date", "")
Advanced IF formulas for future and past dates

If you want to have both indications in one column, you will need to use a nested IF function like this:

=IF(A2-TODAY()>30, "Future date", IF(TODAY()-A2>30, "Past date", ""))
A nested IF formula for dates

Excel IF examples for blank, non-blank cells

If you want to somehow mark your data based on a certain cell(s) being empty or not empty, you can either:

  • Use the Excel IF function in conjunction with ISBLANK, or
  • Use the logical expressions ="" (equal to blank) or <>"" (not equal to blank).

The table below explains the difference between these two approaches and provides formula example.

Logical test Description Formula Example
Blank cells ="" Evaluates to TRUE if a specified cell is visually empty, including cells with zero length strings.

Otherwise, evaluates to FALSE.

=IF(A1="", 0, 1)

Returns 0 if A1 is visually blank. Otherwise returns 1.

If A1 contains an empty string, the formula returns 0.

ISBLANK() Evaluates to TRUE is a specified cell contains absolutely nothing - no formula, no empty string returned by some other formula.

Otherwise, evaluates to FALSE.

=IF(ISBLANK(A1), 0, 1)

Returns the results identical to the above formula but treats cells with zero length strings as non-blank cells.

That is, if A1 contains an empty string, the formula returns 1.

Non-blank cells <>"" Evaluates to TRUE if a specified cell contains some data. Otherwise, evaluates to FALSE.

Cells with zero length strings are considered blank.

=IF(A1<>"", 1, 0)

Returns 1 if A1 is non-blank; otherwise returns 0.

If A1 contains an empty string, the formula returns 0.

ISBLANK()=FALSE Evaluates to TRUE if a specified cell is not empty. Otherwise, evaluates to FALSE.

Cells with zero length strings are considered non-blank.

=IF(ISBLANK(A1)=FALSE, 0, 1)

Works the same as the above formula, but returns 1 if A1 contains an empty string.

The following example demonstrates blank / non-blank logical test in action.

Suppose, you have a date in column C only if a corresponding game (column B) was played. Then, you can use either of the following IF formulas to mark completed games:

=IF($C2<>"", "Completed", "")

=IF(ISBLANK($C2)=FALSE, "Completed", "")

Since there are no zero-length strings in our table, both formulas will return identical results:
The IF formula for blank / non-blank cells

Hopefully, the above examples have helped you understand the general logic of the IF function. In practice, however, you would often want a single IF formula to check multiple conditions, and our next article will show you how to tackle this task. In addition, we will also explore nested IF functions, array IF formulas, IFEFFOR and IFNA functions and more. Please stay tuned and thank you for reading!

You may also be interested in:

-->

3,525 Responses to "Using IF function in Excel: formulas for numbers, text, dates, blank cells"

  1. Akash says:

    Assign sequential numeric numbers to each company with IF funciton. If a company name is repeated, it will have the same number.
    anyone please slve this?

  2. Sittu says:

    Hi, I just want the formula like I just evolving each cell's values like marks. If more than (for example >=50) just want to show that value only using.
    For example, someone takes 40, 45, 50, 30 evolving all values and retrieve only more than 50 values.

  3. Eugene khanda says:

    Make sure the brackets are full closed .This will prevent the repetion.

  4. Sathyanarayana V says:

    Very Nice, i got it what i wanted thank you.

  5. Kamaran says:

    Hi,
    I have D9 = 0 to 2000, I would like to write this formula in C10
    if the value of D9 is equal or greater than 15, then cell C10 value will be 15, otherwise, the value of cell C10 should equal to the value in D9
    =IF(D9>=15,C10=15,C10=D9)
    But, it doesn't give what I want. anyone can help?
    Thank you

  6. D Patel says:

    hi! like to corelate data in 2 sheets of same excel file. e.g. sheet 1 and sheet 2
    I need help to create if or
    any formula like
    =if(sheet1b2=10,"sheet2A3=Sheet1A3","blank")

    plz help

  7. Jamie says:

    Can u direct me on the error of my formula? =IF(E22>0)B22+E22
    I want it to be cell B22+E22. But ONLY if there is an entry in E22. If E22 hasn't been filled in yet, then I want the formula to be in place but not to SHOW. Much Appreciated!

  8. Jaime Wong says:

    Can you help me write a formula for the following situation plse.
    If A1-A2 is less than zero, then show the negative value. If not, then show zero.
    Tks

  9. Ashwini Anand says:

    Hi Sveetlana Cheusheva
    Is it possible that if I enter any text in column"P", "L" should show what is there in "K".
    =IF(NOT(ISBLANK(P7)),"0","=K7")
    =IF(ISBLANK(P7)),"=K7","0")

  10. dEEPAK says:

    i using if condition formula it taking only 10 conditions is it only 10 conditions allowed

  11. Ricardo Lewis says:

    Can you help me to accurately represent this formula please:
    =IF(L4>0612,"High Risk")

  12. Sudone says:

    IF Early Bird criteria - Customers who want to avail of an Early Bird discount must place their orders on weekdays between 11:45 a.m. - 12:00 p.m. (for lunch) or 6:45 p.m. - 7:30 p.m. (for dinner).
    Write a logical function to find out whether a customer is an early bird. (refer to the 'Early Bird' criteria stated above) In your function, you will have to reference Column E values (day of week) and Column F (time of order) to determine whether customers are early birds. Think about how you will reference the early bird criteria.
    If a customer is an early bird, the function should return "Early Bird". Otherwise, the function should return a blank value.

  13. Ez says:

    Hi Svetlana,
    I am trying to achieve in the formula to show the following:
    If the cell drop down is selected with "CB2" is standalone to show "local", if "CB2" & "CB3" (combined) to show "local & far" and if "CB3" to show "far"
    How can I achieve this?
    TIA

    • Adrian says:

      Hi Tia,
      There seems to be a problem with the IF(AND()) function. The comment truncates the formula for some reason.
      =IF(A1="CB2","LOCAL",IF(AND(A1="CB2",A1="CB3"),"LOCAL & FAR",IF(A1="CB3","FAR","")))

  14. Ikram Siddiqui says:

    Good Morning,

    I am trying an exercise in Excel, we have a sheet some details, however, in the first column A2, have some digit number in the same column digit will be sometime 7, 8, 10 and some case digit numbers with dots then TWO digits.

    I am looking for a formula that can help me to write text in the next column............

    Example : =IF(A2=(len)8,"NOBILIA",IF(A2=(len)7,"IMPULS",IF(A2=(len)10,"LINEA DÉCOR"))).

    Will you please help me with it.

    Sincerely yours
    Ikram Siddiqui

  15. neil says:

    I would like to check a cell for currency format and if its in £ then multiply it by the $ factor to convert it to $s' and if its in $ then leave it in $s'.
    example: A1 is the £ to $ currency factor; A2 is the value in £ or $; A3 will be the value of A1 in $s.
    I have tried using the IF function, but it will not recognise A1 currency format! I would appreciate some help with this. Many thanks.

  16. Jonathan Peterson says:

    Assuming that A1 is 2006 and B1 is 2006
    =IF(A1=B1,"True","False") comes up True
    but
    =IF(A1="2006","True","False") comes up False.

    How do I resolve this within a formula since the 2006 is coming from a different formula.

    Thanks in advance.

  17. Cindy says:

    Please show me formula for the below. Thank you!
    IF A1 = ABC B1 = 1234; if A1 change to DEF then B1=5678; also if A1 change to GHI then B1=91110

    • JP says:

      This will only work on 4 letters at a time. I'm assuming you meant ABC = 123 and not 1234. If not this wont work for you.

      =IF(MID(A1,1,1)="", "", CODE(UPPER(MID(A1,1,1)))-64&""&IF(MID(A1,2,1)="", "", CODE(UPPER(MID(A1,2,1))) -64&""& IF(MID(A1,3,1)="", "", CODE(UPPER(MID(A1,3,1)))-64&""& IF(MID(A1,4,1)="", "", CODE(UPPER(MID(A1,4,1)))-64))))

      • Cindy says:

        Actually I need 6 digits (ABC=123456). So it doesn't work for me! Thanks for trying!

        • JP says:

          Here is the 6 letter version. To add more numbers you just have to copy everything from IF to the second & and change the number after the A1.
          =IF(MID(A1,1,1)="", "", CODE(UPPER(MID(A1,1,1)))-64&""&IF(MID(A1,2,1)="", "", CODE(UPPER(MID(A1,2,1))) -64&""& IF(MID(A1,3,1)="", "", CODE(UPPER(MID(A1,3,1)))-64&""& IF(MID(A1,4,1)="", "", CODE(UPPER(MID(A1,4,1)))-64&""&IF(MID(A1,5,1)="", "", CODE(UPPER(MID(A1,5,1)))-64&""&IF(MID(A1,6,1)="", "", CODE(UPPER(MID(A1,6,1)))-64))))

    • Hello Cindy,
      Please try to use the formula below to solve your task:

      =IF(A1="ABC", 1234, IF(A1="DEF", 5678, IF(A1="GHI", 91110, 0)))

  18. Ahmadullah says:

    Plz help.....
    If i write a number greater then 100 in a cell mistakenly. I want Excel shows an error in that cell.
    For example
    I write 23 26 56 100 in cells and write mistakenly 105. So i want to show 105 in error.

    • JP says:

      I would just highlight the whole column and do a Conditional Formatting, Highlight Cell Rules, Greater Than..., 100. Then if the cell is highlighted in red you'll know it's over 100.

  19. Syed M Ali says:

    Hey,
    Your tutorial is great. I am trying to get this nested formula but somehow its only picking up last logical test values. Can you please check & advise.

    =IF(B$4>B7<B$3, "Expiring within 2 Weeks", IF(B$3<B7B$2, "Expired", "Valid")))

    Regards

  20. G says:

    Depending on the city, you will either take a shuttle to/from the airport or rent a car. Insert an IF function that compares to see if Yes or No is located in the Rental Car? Column for a city. If the city contains No, display the value in cell F2. If the city contains Yes, display the value in the Rental Car Total (F4)

  21. Shelby Fling says:

    Hi is it possible to do an if formula when a cell is either finance or cash have it do another formula to equal commission?

  22. Megan says:

    I need to lock one column so the formula is displayed but do not want the rest of the spreadsheet formulas to show. How can you freeze one column display the formula?

  23. John says:

    Hi, I'm trying to write an if/then statement that will represent numerical values. For example, If L48 is greater than L47, then show the value inside L48. If not, show the value inside L47.

    I get it to do the yes, no feature, but I'm having trouble getting it to display the value inside of L48.

  24. Elida says:

    Hi,

    I have the conditional formula on my J cell that read as follows: =IF(G3="F2F","1", "0"). G3 has a drop down menu to choose from and one of the options is F2F. How do I add all the ones at the end of my J cells?

    Thank you,

    Elida

    • Adrian says:

      Do you want different values for the different drop down values in G3? Otherwise, use the formula below. This will give you a 1 if G3 has either F2F,F2F2, or F2F3 (replace with whichever is on your list)
      =IF(OR(G3="F2F",G3="F2F2",G3="F2F3"),1,0)

  25. Phillip Johnson says:

    i have the following formula:
    =if(isblank(A1),"", if(D1<0, weeknum(A1,1),""))
    working with this formula:
    =if(isblank(J2),"", hlookup(max(#REF!),$R$1:$DD$89,row(J2),false))
    to calculate weeks of perfect attendance. However, it has errored out for anything after week 52. how would i fix this to get it to continue past week 52.

  26. ronald greiner says:

    I want to use the IF function to test if one cell (say H5) is above zero, then insert a given cell's value (say the numerical value in C5), if the test is false, then insert another cell value (say the numerical value in E5).

  27. RAMSHI says:

    Hi,
    I need an help to auto calculate the order quantity, if a particular cell is less than eg : 60.

  28. Chamo says:

    I want to make a one formula in Excel for:
    IF 825>the answerthe answer4001,*0.5

  29. James says:

    if C1, C3 & C5 have "text", how to total them in C10?
    (There are approx. 20 cells with or without text that would report to C10.)
    Example: C1 = smith. C3 = . C5 = jones. In this case, I want C10 to show the number "2", allowing me to calculate off of that.
    Thanks in advance!

  30. KHRISTINE says:

    Please help me build a formula as follows:
    if A1 contains A,B or D then it is CLOSED, if A1 contain C then it is OPEN
    thanks

  31. Laura says:

    Hi,
    I am trying to have the value of a cell(B2) in Sheet2 populate in Sheet1 cell(B3). I thought I could do ='Sheet2'!B2, but when there is no data in Sheet2 B2 the value populates as 0 in B3, I would like it to remain blank.

  32. Amy says:

    I'm trying to get an if, then formula to generate a number in a cell if the adjacent cell is a certain letter (for GPA calculations), but i keep getting a value error message. Here is what I wrote: =IF(C3="A",4,IF(C3="b",3,IF(C3-"c",2,IF(C3="D",1,IF(C3="F",0)))))

  33. Needing Help says:

    I have a spreadsheet, with multiple tabs I need to be able to Concatenate a text cell (A3) with a date cell(B3) on a report form, but be blank when the date cell (B3) is empty. this is what I have so Far.
    =IF(AND(NOT(ISBLANK('Super(No7)'!A3))*NOT(ISBLANK('Super(No7)'!B3))),CONCATENATE('Super(No7)'!A3&""&TEXT('Super(No7)'!B3,"DD/MM/YYYY"),"")).
    The above formula works until I add the &TEXT& bit then it returns FALSE. can anyone help please.

  34. theresa says:

    Please i need you assistance to building formula as follows:
    if A1 contains specific text, A OR B OR C = CLOSED
    if A1 contains specific text, C = OPEN
    Many Thanks.

  35. Jason says:

    Id like to ask how to use if function or whatever easy function can be used should I want to display the percentage in a cell if a condition has been met. The condition is if all specified range of cells contains any values like a word or date. Lets just say that cell A1 will display the percentage of completion if cells ranging from A2,A3,A4,A5,A6 contains a value like yes or no or a date.

    • Hi Jason,

      It's not clear how you calculate the percentage of completion, but the idea is to count non-empty cells in the range A2:A6, and if the count is equal to 6, display the percentage, otherwise return an empty string:

      =IF(COUNTIF(A2:A6, "<>"&"")=5, percentage formula, "")

  36. carla says:

    Hi
    I have forgotten how to repeat a certain set of numbers for a spreadsheet.
    For example i have a list of 3 items and i want the IF function to repeat as 123123 etc.
    I have put 3 in the A1 cell and the number 1 in A2.
    Thanks

  37. dina says:

    what is the formula for interval,
    fill the cell outstanding if score 4.5 to 5
    fill the cell V.Good if the score between 3.5 to 4.4, and so on..
    i tried to make rounding but it doesn't work if the number not whole even after rounding

    • Adrian says:

      This one should work. You can change the text results to whichever you think fits better.

      =IF(E5>=4.5,"OUTSTANDING",IF(AND(E5>=3.5,E5=2.5,E5=1.5,E5<2.5),"SATISFACTORY",IF(E5<1.5,"FAIL","")))))

  38. Norrella Jalil says:

    HI, May u help me with this situation?i cant get the right formula for that 0 or negative figure is equal to 0
    0 or negative =0
    >0 -10% = 1
    >10% = 2

  39. Adrian says:

    =IF(G5>10,2,IF(AND ( G5 0),1,IF(G5=0,0,"")))

    The website seems to break the formula... I tried adding spaces to see if it will work...

  40. Adrian says:

    It seems the IF(AND( function is breaking the formula.
    =IF(G5>10,2,
    IF(AND[G50],1,
    IF(G5=0,0,"")))

  41. LEE says:

    Hello, how do I make an if statement see the text in a cell and not a reference? In Cell F2 the value is "T19" I want to say, if F2 = T19 than "A19" with A19 being text not a reference.

  42. Brett says:

    Hi Svetlana,

    I would like to return a value of "No" in B1 if A1 is either "0" or "na".
    Is this possible?

    Thanks!

  43. Rex Manuel says:

    Hi!

    I would to ask for your help with this formula.
    =IF((T17+U17)-V84V84,Z17,0))
    I got the same result which is 00:00 even if it has higher or lower value.

    Thanks!

  44. Rex Manuel says:

    =IF((T17+U17)-V84V84,Z17,0))

  45. Joshua Peralta says:

    Need help.. is this possible?
    If value of A1 is item no. And value of A2 is the qty (i.e. 3 pcs), can A1 be copied in column C thrice? A1 = C1 = C2 = C3.

  46. Madhosh says:

    Hello,
    Need one help. I have two values in cell A (Y or N) and i want to use IF statement but i am getting error. The statement is like this if the cell A contains N then it should return N else it should leave blank
    IF(A=N,"N","")

  47. Shubham says:

    Hi,

    For exm, We are receiving any project 12345 between 6:30:00 AM to 10:30:00 PM then time should automatically 6:30:00 AM.

  48. chris says:

    Could you please help, I want to write if E5 is greater or smaller than 0 enter that amount in C6

  49. Ankur Kumar says:

    Can anyone help

    =IF(A4="Month"&B4="US:W2 Salaried",173.33)

    why it is returning false statement

  50. Gunars Valters says:

    Hello, trying to get the word "OFF" to register as a zero value in a formula.=IF(D6=D6,IF(B6="OFF",0)-C6) The problem I have is this results in a negative number. I need B6 to equal 0 ONLY when the word OFF is entered there otherwise use the number entered. Here is the next cell down on the table I am creating hope this explains the D6=D6,
    =IF(D7=D6,IF(B7"OFF",0)-C7) . Thanks

  51. Gunars Valters says:

    Hello, trying to get the word "OFF" to register as a zero value in a formula.=IF(D6=D6,IF(B6="OFF",0)-C6) The problem I have is this results in a negative number. I need B6 to equal 0 ONLY when the word OFF is entered there otherwise use the number entered. Here is the next cell down on the table I am creating hope this explains the D6=D6,
    =IF(D7=D6,IF(B7="OFF",0)-C7) . Thanks

  52. Harold says:

    Excel 2017 Have 4 columns, need an if statement to add dollar amount in the 4th column. Three columns are designated as A, B, & C, 4th is Amount. If I put an X in Column A , needs to show $10 in column 4, if i put an x in column B, needs to show $5 in column 4, if I put an X in column C, needs to show $1 in column 4.
    The first one I can get to work, not the others. In column 4 I put this in:
    =IF(A1="X",10,"")

  53. Steven R says:

    Need some help please... I know I am probable over thinking this...
    so I am running three columns of numbers say , E,F,G.
    Column E is a Negative and Subrtacts From G
    Column F is a Positive and Adds to Column G
    But I would like a running total in C4, I was thinking that I could use the "IF" to read the last filled in cell in column G. so for example

    Cell C4 will show the running total amount without having to scroll down to G47...
    data starts in G6 and ends in G90 and with each entry it moves down to the next cell which is blank.
    I tried using the "lookup" to show me the value of the cell above the first blank cell but cant figure it out either...

    Geez I hope I explained this right.... compare it to a checkbook....
    Column E are withdrawls and deduct from G
    Column F are Deposits and Add to the current total in G
    I have all of the formulas for G and keeping the total but would like a "quick" glance to see the running total....

  54. A says:

    Hi Steven,
    =SUM(G6:G100)+SUM(F6:F90)-SUM(E6:E90)
    I'm not sure if that is what you mean.

  55. barbora says:

    Hi, Could you please help me? I need a function. B6 to B8 each have a drop down list and in C6 I need either "Section booked" if any of B6, B7 or B8 has any text. If all blank then "Select"
    It has been driving me crazy. Thank you so much

  56. LX says:

    how to get same value if its more than the actual value.
    for eg:- actual value is 100 and other value comes at 110 so i want 110 should display as 100.

  57. Shahid Hussain says:

    Hy I Make A Inventory Sheet And I Want To Highlight That Row Where Quantity is 0.

  58. SANKERDAS says:

    Hi,
    I am preparing a Excel LOG template for my document submissions and tracking. Documents submitted will have revisions & approvals based on comments from client. I want to prepare an excel LOG with functions to return Doc status whether it is Approved (A), Approved with Comments (B), Re-submit (C), Reject (D), Under Review (UR). I tried with some combinations of IF function & CHAR functions.

    CHAR function
    =CHAR(MIN(CODE(N12),CODE(V12),CODE(AD12),CODE(AL12),CODE(AT12)))

    CHAR Functions is working fine with one limitation, after getting status (C) for submitted document, while I re-submit and enter (UR) in Rev. status cell, the value is not returning. I guess with a combination of IF & CHAR function it should work.

    Please help me to solve this.

  59. Steven Saunders says:

    Hi there,

    I need a formula that says:
    If column M says 'Edinburgh - Leeds' then N will say 163 miles.
    Then if column M says 'Edinburgh - Manchester' then column N will says 175 miles.

    I have to do this for many different distances but they two are just an example.

  60. L M Barnard says:

    Hello
    I need a formula for if() i have 9 cells in a row for montly sales example 2 4 6 7 0 3 5 8 3 {yes}
    I want to use =if to see if there is a 0 in the row to say yes or no

  61. Stephen says:

    =IF(OR(ISBLANK(N9),OR(ISBLANK(P9),ISBLANK(O9))),,(N9*(P9-O9))*100) I want to add a function so that it will only multiply by 100 if Row L9 to L123 says Debit or Credit. so if it does not show debit or credit it will put the number instead.

    • A says:

      Hi Stephen,
      =IF(OR(L9="DEBIT",L9="CREDIT"),(N9*(P9-O9))*100,(N9*(P9-O9)))
      Formula above only checks per row. You get a value per row
      =IF(OR(COUNTIF(L9:L123,"DEBIT"),COUNTIF(L9:L123,"CREDIT")),(N9*(P9-O9))*100,(N9*(P9-O9)))
      Formula above checks the whole range of L9:L123 if it contains debit or credit.

  62. Stephen says:

    If *100 is not there it works for stocks, with the * 100 it works for options.

  63. Mallorie says:

    Hello! I am having trouble writing a formula.
    I need the date(that is not final) in C22 to project 107 weeks out and then compare that new date to 12/31/2024 and pick the earlier of the two. This new date would automatically go to cell c32.

    I appreciate any help. Thank you

    • A says:

      Hi Mallorie,
      Please try the formula below.
      =IF(C21<C22,C21,C22)
      You might have to format the columns to be shown in the date format.

  64. Stephen says:

    Thank you works like a charm.

  65. Carrie says:

    Hello,
    I have 2 tabs in one spreadsheet. On tab 1, I want the cell to change to 'Complete', if the column range on tab 2 are all answered 'Yes'. Otherwise, I want it to say 'Incomplete'. The answers for column on tab 2 are either "Yes" or "No". If there is even one 'No' in that column, I want the column on tab 1 to pick it up.
    The following formula is what I've tried and it is not working, although I get no error.

    Where tab 1 is named "Sold' and tab 2 is named 'SoldDetails'. Thanks for any guidance. Perhaps I should be using CountA to determine cells that are either 'No' or 'NA', but any help is appreciated.
    =IF(SoldDetails!C8:C21="Yes","Complete", "Incomplete")

  66. A says:

    Hi Carrie,
    please try the formula below.
    =IF(COUNTIF(SoldDetails!C8:C21,"NO"),"INCOMPLETE","COMPLETE")

  67. Carrie says:

    Hi A, Perfecto! Works like a dream. Thank you very much.

  68. j says:

    I am working on an inventory thing and all I want is for the drop list I have created for the cells to subtract 1 from the total in another cell.

    Example - If C3 is (I select an item from the drop down) then subtract 1 from V3.

    I just have a floor plan that I have layed out for deployed monitors and want to be able to select from the drop down a Samsung monitor for example and then it take 1 away from the total I have in another cell. Should be super simple however I can't get it work with the IF statement.

  69. Quentin says:

    I am trying to calculate a commission for realtors here in BC. We have a tiered structure here which is as follows
    7% on the first 100k
    2.5% on the balance.

    This is then further broken to reflect
    3.22% on the first 100k and 1.15% on the balance (agent representing buyer)
    3.78% on the first 100k and 1.35% on the balance (agent representing seller)

    I want to be able to put a client name in a cell and following cell the type of transaction (buyer or seller) and have a cell trigger a calculation for the above commission level when the word seller or buyer appears in the cell.

  70. Faisal Shahzad says:

    Hi
    can you help me to find a value
    if A1,B1,C1,D1,E1 have values like that
    0, 0, 2, 0, 0
    How we found this value
    C1 value not specified its Formula based value
    Regards

  71. RON says:

    IF THIS CELL IS LESS THAN OR EQUAL TO 30 COPY THE VALUE OF THIS ROW,IF NOT,ZERO,

    • A says:

      Hi Ron,
      =IF(A1<=30,A1,0)
      The site may have issues showing the formula. If the above is broken, try the one below.
      =IF(OR(A1=30,A1<30),A1,0)

  72. JD says:

    I need a function to determine if age greater than 23, and should return the text Yes if true and No if false.

  73. Gunar says:

    Hello,Can you please help me with this?=IF(AM4=AM6,IF(AK4="MED""NWRK""CLL""RQO""OOS""WCMP",0,AK4-AO4-AP4-AQ4)-AL4)
    This formula does what I want when there is data in AM6 but if AM6 is blank it returns #Value. This happens only when MED, NWRK, RQO etc is typed in AK4. Why?
    Thanks in advance for your time.

  74. Gunars says:

    Hello, can you please help me with this?=IF(AM4=AM6,IF(AK4="MED""NWRK""CLL""RQO""OOS""WCMP",0,AK4-AO4-AP4-AQ4)-AL4)
    If there is data in AM6 I get the result I want. If MED, NWRK, CLL etc is entered in AK4 and there is no data in AM6 I get #value. I would like a blank cell. This was working when it was just"MED" and had not added the others.
    Thanks for your time.

    • ABcode says:

      Hi, The concept you put up is conflicting, please narrate the job properly in order to provide you with the correct format.

  75. Suzette says:

    i have a formula to work out if stock is short over or equal how do it add the equal =if (c2>b2;"over";"short")

  76. Little Vermin says:

    Please help,
    what am I doing wrong? the cell just returns what is in AT2 regardles of what is in C9
    =IF($C$9="1",IF($C$9="2",IF($C$9="3",IF($C$9="4",IF($C$9="5",IF($C$9="6",IF($C$9="6",IF($C$9="8",IF($C$9="9",IF($C$9="10",BL2,),BJ2),BH2),BF2),BD2),BB2),AZ2),AX2),AV2),AT2)
    Thanks.

    • Little Vermin says:

      never mind fixed it
      =IF($C$9=0,"",IF($C$9=1,AT2,IF($C$9=2,AV2,IF($C$9=3,AX2,IF($C$9=4,AZ2,IF($C$9=5,BB2,IF($C$9=6,BD2,IF($C$9=7,BF2,IF($C$9=8,BH2,IF($C$9=9,BJ2,IF($C$9=10,BL2)))))))))))

  77. ABcode says:

    --=IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ")))-- done

  78. Antigoni says:

    Hi, i would like to ask you, if a cell has a value how can i ask to put an other value to the next cell which is empty?
    Thank you!

  79. Majli says:

    Hi,
    I have two tables with different data. I want to create a new table, extracting information from both tables (some of the data in the first table may not be in the other table but I want to be shown all of them), because I want to find the difference from both tables.
    Thank you,

  80. Sony says:

    Hi,

    What function would I use to say "If there is a value in this range of cells, input "yes", otherwise input nothing"

  81. Lesley says:

    Hi I am trying to create a formula in excel for a loan officer. I want it so that commissions are automatically populated based on the type of "lead" it is.

    I have column 'a' filled with the lead type, including either "self-gen", "branch", or "corporate"
    I have column 'b' filled with the loan amount
    I want column 'c' to auto-populate with the commission.
    If a "self-gen" lead, then it would = cell 'b' * .015
    If a "branch" lead, then it would = cell 'b' * .006
    If a "corporate" lead, then is would = cell 'b' * .0035

    Thank you!

    • A says:

      Hi Lesley,
      Try the formula below.
      =IF(A1="SELF-GEN",B1*0.015,IF(A1="BRANCH",B1*0.006,IF(A1="CORPORATE",B1*0.0035,"")

  82. Nadia says:

    Hi! I need this formula: =if((C1>0 and <30);A1;0). How to use formula in brackets to compare numbers. I need to be if C1 is between 0 and 30? Thank you!

  83. Georgiana says:

    Hi,
    Please help me with a formula for the following:
    I have 2 columns: HR100 101001
    HR102 101001
    HR103 101001
    Based on the value in the 1st column I want to return the value from the second column. But see, some has the same number. So if I am using Vlookup, if you have the same value will return N/A. Please help me. Thanks

    • Hello Georgiana!
      If there are some repetitive values in the column you're working with, you should sort it accending beforehand and set the Range_Lookup parameter as 1 or TRUE. Besides, it would be a good idea to place an unary operator (--) so that Excel considers it to be a number, not a text string. Having said that, the formula should look like the one below:
      =VLOOKUP(--F3, $B$1:$D$5, 2, 1)

      Alternatively, there is a combination of INDEX + MATCH functions that may be helpful as well. You may find more information on this topic here.

  84. Kedevin Christiaan Kastoor says:

    =IFS(A1 = 5, "Excellent", A1 = 4, “Very Good”, “Good”) has a syntax error/is incorrect
    What is the correct formula.

  85. Kedevin Christiaan Kastoor says:

    =IF(B2-C2,"OK" “Wrong amount”) has a syntax error/is incorrect
    What is the correct formula.

  86. Nitin Parmanand says:

    =iferror(if(c6="",1,match(c6,holidays,0)),0)
    Using the above code I can't find the answer. I used this program for the holidays on the helper shit, but the holidays are not being shown in attendance sheet. What do I need to do?

  87. Shobert says:

    Hi Svetlana,

    could you advise any "If" combination to read a range of text and return a specific value?
    Example: there are 10 test results and there is one overall result. If 9 test result are "passed" and 1 is failed then the overall result to be marked "failed". If all 10 test results are "Passed", then the overall result to be marked "Passed".

    Thanks
    Shobert

  88. Ahmad Faizal says:

    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.

  89. Ahmad Faizal says:

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

    • 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.

  90. Debarun says:

    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???

  91. Sarena says:

    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

  92. André Jooste says:

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

  93. salawudeen says:

    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

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard