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 - a value or logical expression that can be either TRUE or FALSE. Required.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 - the value to return when the logical test evaluates to TRUE, i.e. if the condition is met. Optional.

    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 - the value to be returned if the logical test evaluates to FALSE, i.e. if the condition is not met. Optional.

    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 the 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,064 Responses to "Using IF function in Excel: formulas for numbers, text, dates, blank cells"

  1. Sajumon says:

    Pls help me create a formula:
    If column B contains a particular value, then find the corresponding value in column A

  2. Doug says:

    Ajith:
    Where the value is in A55, the formula to accomplish what you want is:
    =IF(A55>=5,500,"Something Else")
    The formula says, If A55 is greater than or equal to 5, then enter 500 in this cell, otherwise enter Something Else.
    You can change the cell address to the address you need. Also the "Something Else" text to different text. Just remember, if you want the cell to display text to enter it inside quotes. Numbers don't need quotes.

  3. greg says:

    I have trouble creating nested ifs. I have three conditions:
    T1 = 00:00 - 06:59
    T2 = 07:00 - 14:59
    T3 = 15:00 - 23:59
    And these are the cells that I need to match the above conditions. The dates and times are together in one cell.
    Col A
    Row 1: 06/04/2018 06:00:25
    Row 2: 06/04/2018 08:00:26
    Row 3: 06/04/2018 18:35:18

    This is my formula:
    =If(And(A1R1>T1, A1R1T1, A1R2T1, A1R3<T1), T3, "")

    How can I combine this?

    • Doug says:

      Greg:
      The formula you've provided isn't clear. Can you provide your needs in an IF/Then format? For example, IF A1>=6:00 then T1, IF A1>= 7:00 then T2. Something along those lines.

  4. Alex Nkonya says:

    =IF(X4=A,"1",IF(X4=B,"2",IF(X4>=C,"3",IF(X4=D,"4",IF(X4=F,"5",)))))
    What is the error in this if function?

    • Doug says:

      Alex:
      It looks as though you've got the quotes around the wrong
      characters.
      This should work:=IF(X4="A",1,IF(X4="B",2,IF(X4>="C",3,IF(X4="D",4,IF(X4="F",5,)))))

  5. savan says:

    Hi,

    If there a context in a cell separated by space or - then, I want the out put as true else false.

    Anybody can help on this?

  6. Syed Ibrahim says:

    Thank you. This article helped me alot.

  7. Scott says:

    Our electricity providers use very complicated formulas to calculate our monthly electricity rate. I am needing to change providers and find the least expensive company to use. I am trying to find an Excel formula to do a "look back" over my previous usage, using these current formulas.

    In simple terms, these formulas are like the following:

    "I have 10 apples. I will sell the first 5 apples for 50 cents each, the next 3 for 25 cents each and the last 2 for 10 cents each."

    Can anyone help me with a formula that would accomplish this task? In the case of the electricity providers, the variables are the cost per apple and the range of usage that falls into that particular rate.

    Thank you.

  8. Tim Carreau says:

    I want cell A, if showing a negative number, to show as zero in cell B, but if cell A is not a negative number, I want it to show as the non negative value in cell B. What formula would I use? Thanks

  9. Alex says:

    I need a formula that produces specific text if certain cells contain information (a date to be specific). The three preceding columns indicate an action being performed, and I want a column to auto populate the status.

    The status' would be "Drafted" "Reviewed" and "Finalized".

    Please advise if there is a formula that can accomplish this. Thanks

  10. Gayane says:

    I want to ask you a question. I want one cell turning into red when 30 days passed after day of payment,so i'll now that the customer have to pay after 30 days

  11. Ifeoluwa Odunbaku says:

    Overhead costs include maintenance, cleaning, and administrative expenses associated with a car rental. The Overhead for one rental transaction is $50 for an economy car and $75 for a premium car. This Overhead cost includes maintenance, cleaning, and administrative costs for each car rental. a. With this information, in the Profit Analysis worksheet, in the first cell under the Overhead label, calculate the overhead that corresponds to the number of cars rented (NumCars) using an "IF" statement as part of the formula. b. For clarification, use the "IF" statement to determine whether to use $50 or $75 times the NumCars so that the Overhead value represents the total Overhead costs for the Number of Cars (NumCars) rented in a given row.

  12. Julie says:

    Per the example shown below, how do I create a worksheet formula to look for duplicates in column B, then corresponding duplicates in column C and if true, sum amounts in D and then separately sum amounts in E? (e.g.- Jane Doe) If duplicate in column B, but not corresponding duplicate in C, then do nothing. (e.g.- John Smith)
    A B C D E
    1. DOE,JANE 234567890 5/1/2018 $84.85 $3.39
    2. DOE,JANE 234567890 5/1/2018 $405.32 $16.21
    3. SMITH,JOHN 987654321 5/1/2018 $247.13 $9.89
    4. SMITH,JOHN 987654321 6/1/2018 $247.13 $9.89
    5. JONES,JIM 736429746 5/1/2018 $369.91 $18.49
    6. ROBERTS,JUDY 103947492 5/1/2018 $555.88 $27.80

  13. John Duran says:

    Which logical formula can I use to verify that the companies haven't given employees more than 3% escalation, and if they do, what is the % applied between each year. Thank you

    Andy Garcia 2015 60.1 2016 67.31 2017 79.23 2018 79.23

    Domingo Solis 2015 45.00 2016 46.35 2017 48.66 2018 56.24

    • Doug says:

      John:
      Where the data is in O13 and N13 the formula is:
      =(O13-N13)/O13 then format the cell holding the result as a percentage with two decimal places.
      In your samples the increase for Andy from 2015 to 2016 is 10.71%. So, (2016-2015)/2016.

  14. Sanjib says:

    How to put this is formula (For calculation of Discount in a Cell on Text Basis)
    IF B1=YES then C1=A1*10/100 & IF B1=NO then C1=A1

  15. Rhonda Bruhn says:

    I am trying to create a formula that will compare a date in a column to a specific given date, if prior that that date, I want it to put the value that is stored in another cell. If the date is after either leave blank or put in a word.
    Example
    If(J47 earlier than 01/01/2017, +h47, "Active")
    I thought this would work:
    =IF(J47<DATEVALUE("01/01/2017")+H47,"Active")
    I get a message that says problem with this formula, how do I change it to make it work?
    Thanks for your help!
    Rhonda Bruhn

    • Doug says:

      Rhonda:
      I would put the 1/1/2017 date in say H47 and then compare the other dates to it. Looks like this
      =IF(J47<=$H$47,I47,"Active")
      IF J47 is earlier than or equal to the date in H47 then display the value in I47 otherwise display Active.
      In this example the cell holding the 1/1/2017 is being held as an absolute reference by the $. In other words, when you copy the formula up or down the J column each value is compared to the date in H47 or in this case, 1/1/2017.

  16. Francisco Amargo says:

    I created a formula that looks like: =if(d5=a12,f12,0). My problem is how will Excel compute autimaticaly if I change the value in d5 that equal the value in a13, the value in f13 will automatically be the value of the cell? Thank you

  17. Hazel says:

    I'm having trouble with this;
    if a cell shows Jul-18 (or another month) then do show a figure in another cell
    I've tried =if(d1=datevalue("01.07.18-31.07.18"),D36,0)

  18. Hazel says:

    I realise my question was not clear.
    I have forecast months in column A, prospective sales in column B and then a 12 month schedule starting Jan in column C, Feb in D and so on.
    I want a calculation to read the forecast month in column A (for example July) and then show the prospective sales value in the July column - and change to another month if I changed column A to September for example

  19. John says:

    Hi, I run a sports & social club register and each member has to do certain duties. I need to automatically assign a date a member did one of three duties by putting a code against their name in the register OP, REG, RAF into a last date column. I've tried the sumif but obviously not right for this requirement.
    Thanks for your assistance.
    John

  20. John says:

    Hi Doug - can you give me your email and I'll send you a mini spreadsheet with the requirements on.
    Thanks again
    John

  21. Sara says:

    Hi! I am trying to create a relatively simple formula. I need it to read one column, and if the cell says “credit card” to enter 0
    Ok so that’s =IF(C2=“credit card”, 0

    But then if it says, “cash,” I need it to copy the dollar amount listed. So I know I could write it in c2, d2, etc, But is there a way to write only one formula?

    Essentially, I need to add up only “cash” sales (not “credit card”). So I thought if I entered 0 for credit and could duplicate the cash cells, that I could then simply sum the new column. Thank you for any help!

  22. Sara says:

    Oh sorry, I didn’t mean c2 and d2 since I used column 2 as my example of cash or credit, but I meant to say the value listed in another, so say C3 and D3.

    If a text example would help, then let’s say:
    IF(C2=“cash”,C4=C3,0)
    So it’s copying the numerical value in C3 to a new column,
    But would I need to write this formula many times for each row? Or is there a simpler way?

    • Doug says:

      Sara:
      Right now the IF statement says, If the entry in C2 is cash, then put the value that's in C3 into C4 otherwise return 0. If this is what you want then all you need to do is copy this formula down the column and the relative cell references will follow, so you don't need to write the formula, just copy it down the column.

  23. vasant says:

    if i type some text (e.g vasant) i want is value in number, so which formula i use for this.

    • Doug says:

      Vasant:
      If I understand your question you want to try:
      IF(A2="Vasant",1000,"")
      Where the data is in cell A2 the formula says, If the value in A2 is Vasant, then display 1000 otherwise display blank.
      You can change the cell addresses,return text and values to suit your needs.

  24. Janelle Mellinger says:

    Hello,
    I have 2 columns - the first one has the service date listed, and the second column has the days between the service date and the payment date. There are 2 different payment dates for some of the service dates. I want to list a 1 or 2 in a third column - 1 for the first payment and 2 for the second. I have tried an if(and( formula and countif, but I cannot figure it out. Help please!

  25. adam torzillo says:

    hi am trying to create a time sheet to calculate working hours and my start time will be in one day and my end time will be in another day =IF(G28="","",IF(G28-F28>0.208333333333333,G28-F28-0.0208333333333333,G28-F28)) but i cant seem to work it any help would be great

  26. Ali says:

    Need formula for following
    Sheet 1:
    Date, Invoice No, Customer Name, Due Date, Amount
    Sheet 2:
    Customer Name, Customer ID, Due Days

    In Sheet 1 Once Date entered, Due Date column should automatically update by matching Customer name from Sheet 2 adding due days to Date.

  27. Sis says:

    I'm trying to get a cell to work with several other cells and I can't figure out how to do it, because I also need to use + and - inside the formula.

    I'm trying do something like this, but the word True or False keeps showing up in the cell, instead of the reference cell number.

    =IF(U134>U159+100,"",IF(U134<U159-100,"", Need missing formulas here))

    What I am trying to figure out:
    If cell number (U134) is greater than cell U159+100, then keep the cell blank. If cell number (U134) is less than cell U159-100, then keep cell blank. If the value of (U134) is equal to or above U159(but below U159+100), then display the value in cell U134. If the value (U134) is equal to or below U159(but above U159-100), then display the value in cell U134.

    Basically, if it is outside a range that I set +/- 100, then I want the cell to be blank and if it falls within the range, then I want the cell to display the reference number (U134). I don't want TRUE or FALSE placed into the cell, just leave the cell blank or have the number I am referencing it to.
    I can't figure out how to do that. Is there a way I can get it to do what I need it to do? Help please.

    • SIS says:

      Never mind. I figured it out myself. Tough one though.
      Here is the formula if anyone is having the same issue:

      =IF(AA134>AA159+100,"",IF(AA134<AA159-100,"",AA134))

      I guess I was trying to make it harder than it was, by trying to get the cell to add a lot more commands than necessary.

  28. Nimesh Shrestha says:

    hi i want to insert formula excluding 0 but including number 1 to 4 ....plz help

  29. IB says:

    Not sure if this will work, but...
    I want to know how to write the following;
    =if(left a1, 1) = A, or B, or C, then "ON"

    It's not working for some reason... help??

  30. Lauren Wheat says:

    I've got a formula that isn't pulling my true value correctly and I'm sure it's a minor formatting issue.

    =IF(P2> 0,"P2", "0.00")

    Basically, if "P2" is greater than zero, I'd like it to show whatever value is in P2. If it's less than zero, I'd like it to just show "0.00"

    It shows the false value just fine, but shows the literal text of "P2" when my scenario is true.

  31. Kirsten says:

    Same WorkBook with a number of worksheets which are all timesheets. In V25 on the previous worksheet a formula which happens to be =U18 returns a value in time of 13:30. This means I have done 13.5 hours overtime this month. I want to carry that figure forward to the next timesheet (new worksheet cell B11). We are only allowed to carry over a maximum of 14.5 hours. I would therefore like the formula to say =IF'14 MAY 18'!V25 is less than 14:30, then return the value of V25. In this case 13:30.
    However if'14 MAY 18'!V25 = 14:30 then return 14:30.
    However if,'14 MAY 18'!V25 is greater than 14:30 then only return 14:30. I then need to work out another formula that shows how many hours lost (if any).do another formula to work out how many hours lost in B12, if any at all.

    We got as far as:
    CELL B11 I had =IF('9 JUL 18'!V25>14.5,"14:30",'9 JUL 18'!V25)

    Ell B12 I had ='9 JUL 18'!V25-'6 AUG 18'!B11

    But it doesn't work as you can probably tell already! Your kind assistance would be greatly appreciated. With thanks K

  32. Wicks says:

    Hi guys

    I have column A and B.
    As long both columnA and B have any integer value that is marked as * completed* , column c have to executed as *yes*

    Example:

    Column A column B

    70591- Completed 80042-completed

    Column C

    Yes

  33. Wicks says:

    Hi guys

    I have column A and B.
    As long both columnA and B have any integer value that is marked as * completed* , column c have to executed as *yes*

    Example:

    Column A column B

    70591- Completed 80042-completed

    Column C

    Yes

    • Doug says:

      Wicks:
      Wildcards can't be used in IF statements. Will it work for your situation to split the "70591-" out into another cell and build the IF Statement for the cell that contains the word "Complete"? If that will work then you can easily build an IF statement to check for "Complete".

  34. Sasikumar Kannan says:

    Hi Team,

    I need a big help.

    If the first column contains a value of months like January, February,etc and the second column has years like 2016, 2017, 2018, etc.. and at the same time i have a different sheets for each month and year for example let we take the attendance and payroll sheet, If i want the data for the number of days present for the month of February 2018 to be displayed in the 3rd column so can you guys please help me out for the same.

  35. Tony B says:

    I have an issue with graphs in Excel. I have 2 workbooks, workbook 1 is the master and has line graphs on sheet 1 and data for the graphs in sheet 2.
    Workbook 2 is an exact copy as far as the graphs and data is concerned. I don't want to enter the same data twice so I am using this formula to copy values of data from workbook 1 to 2 and it works. =IF('[Commercial Sector KPIs 2018 V1.xlsx]Data'!H3,'[Commercial Sector KPIs 2018 V1.xlsx]Data'!H3,"")
    But..... The data is from January to December. I have only just started to use this formula in wb2 and when I copy it over from July to December the graph shows zero's for those 6 months which I don't want it to do as it affects the linear trend line I'm using.
    Is there a way of having the formulas in wb2 and the graph only showing results if there is an actual value in wb1.

  36. Dan says:

    Hi Team,

    I am trying to build a formula to tell if a given zip code falls within a list of zip codes. EG If I input 11201 in cell A1, I want A2 to tell me "Yes" or "No" if it falls withing a list of given zip codes.

    If it helps, the list of zip codes are: 11201, 11205, 11206, 11211, 11213, 11216, 11217, 11221, 11231, 11238, and 11249.

  37. Aaron says:

    Hi everyone,

    I want to take the AVERAGE of any cell if the Campus is X
    So, If Column C is my Campus and Campus=DEN (any value equal to DEN $C) will take the value of G$ and average it. This formula will be in a separate cell.

    Is this possible? If so, and I sort the table will the formula continue to work?

    Thank you for your help.

  38. JB83 says:

    Hi, I need the help of the experts! I've been tasked with creating a weekly time sheet for employees at my company, so I'm trying to figure out how to write a IF formula that sums up the totals of daily hours worked and if greater than 40 hours the remainder(overtime) goes into another cell, if less than 40 then the actual sum goes into the cell. In other words, I want cell C13 to have the sum of H29:N29 and if that value is over 40 then the remainder would go into cell C14. Is this possible?

  39. william says:

    I am kinda stuck with creating a formula. I want something that, example: IF A1 is equal to or greater than (numbers in Column E) then show cell in Column E that is Greater than A1. So basically I want it to show the next highest number. I am working with dates here by the way.

  40. MMorin says:

    I have tried to wrap my brain around this one, any help would be appreciated: If cell C3 contains Doctor then I want B3 do indicate 1, if cell C3 contains Dentist, I want B3 to indicate 2; If cell C4 contains Doctor then I want B4 do indicate 1, if cell C4 contains Dentist, I want B4 to indicate 2 etc. as per the list below etc
    1 Doctor
    2 Dentist
    3 Pharmacist
    4 Optometrist
    5 Veterinarian
    6 Consultant
    7 PME
    8 Other

    Thank you !

  41. Raheeb says:

    Hi,

    Please suggest a formula

    A1 value is 50

    If A1 >0 It should be "0" if not it should be "50" (value of A1)

    • SIS says:

      I had the same issue. Here is what I discovered on my own and it works.

      =IF(AA134>AA159+100,"",IF(AA134<AA159-100,"",AA134))

      So, for you, I would suggest replacing my cell letters & numbers with yours.
      Maybe try:

      =IF(A10,"50"))

      I haven't tried it yet to say if it will work, so try it.
      What the formula says is this "If A1 is less than zero, than put 0 in the cell. If A1 is greater than zero, than put 50 in the cell".

      I hope this helps.

      • SIS says:

        It took out half of the message that I wrote down. Weird. Let's try this again...

        =IF(A10,"50")) so let's see if typing after it helps to keep the sentence and formula together. lol

        • SIS says:

          This is ridiculous, it did it again.
          Gonna try a new way. I'm going to use spaces to make sure it does it this time. Sigh. Computers, am I right?

          = IF ( A1 0 , "50" ))

          When you type this, remove all spaces. It won't let me type the whole thing without removing half of the formula.
          I can also try this...
          '=IF(A10,"50"))
          I will see if it worked after its posted

          • sis says:

            OK.. nothing is working. Time to use the last resort

            =
            IF
            (
            A1
            0
            ,
            "50"
            ))

            • Sorry guys, this stupid blog engine often "eats" the "greater than" and "less than" symbols. So, let me post a formula for this condition:

              "If A1 is less than zero, than put 0 in the cell. If A1 is greater than zero, than put the value of A1 in the cell."

              =IF(A1<0, 0, A1)

  42. Yantie says:

    i try to figure out a formula

    if A1:A15=A and B1:B15=750 , C,D

    C is value from the other sheet

  43. Tomas says:

    If the "logical_test" is true, I want a value put into another cell. If the "test" is false, I want the words "Out of range" displayed.

    Example: =IF (A13=50,C23=95,"Out of Range")

    That is, if true, the value 95 is put into cell C23.

    How do I write the formula?

  44. Momma says:

    I need to create a formula that adds all the S, M, L XL, 2XL in a column. I want them individually, i.e. I am adding tee shirt sizes for a large group, would like an If it equals S, then add them up...

  45. SQL says:

    What formula/function should be used on number say
    if 123-45-6789 then SSN and if 66-666666 the EIN

  46. Abc says:

    100 direct 70 Indirect
    if direct method shows 100 value than 70 shows indirect method ?kindly help me

  47. PHIPHI KENT says:

    we need a more clear formula for this function

  48. N R Chavan says:

    Hi,

    I have 4 Column, In column A Date, B diffrent Product (almost 10)
    C Purchase Price D Sale Price.
    Daily sale / purchase enter one by one.

    I Want Prouductwise total (Purchase / Sale) in sepate sheet or column

  49. Mar says:

    I would like to create a formula that enable me to do the following

    If col A =Jack is selected (from a list of 10 names) and
    Col B=Cat is selected (from a list of 10 animals)

    Col C= result in a date (from a list of dates)

  50. Pritpal Singh says:

    SUB_LEVEL IN_DATE WORK_DESCRIPTION STATUS OUT_DATE
    A 17-07-2018 SKELETON MAKING #VALUE! 18-07-2018

    1).In STATUS Column i want "In Progress" when i enter date in (IN_DATE) Column.
    2). And i want "Pending" in STATUS Column if the SUB_LEVEL Column is empty.
    3). And i want "Finished" in STATUS Column if i enter any date in OUT_DATE Column.
    I have tried using the following formula but it returs an error, Please help:
    =IF($B4"","In Progress","Pending")*OR(IF($E4"","-Done",""))

  51. Ahsan Mohammad says:

    =IF(A4="GP","3",IF(A4="DMF","4",IF(A4="CARD","5",IF(A4="ORTHO","6"))))

  52. ANWAR BASHA SHAIK says:

    IF(A12.5,"1500")))). CAN YOU PLEASE GIVE ME CORRECT ONE...

  53. Marina says:

    Help!

    Column A Column B Column C Column D
    ABC DEF IF(I2:M2="ABC","ABC",IF(I2:M2="DEF","DEF",IF(I2:M2="","","BOTH")))
    ABC ABC

  54. Marina says:

    Help!

    Column A Column B Column C Column D Column E
    ABC DEF
    ABC ABC

    In Column E, I'm trying to find a formula that would populate ABC when it's only ABC in one or more cells in row 1; DEF when it's only DEF in one or more cells in row 1; if ABC and DEF are in the same row as it shown in row 1, then I want the formula to return "BOTH". IF statement doesn't seem to work IF(A2:D2="ABC","ABC",IF(A2:B2="DEF","DEF",IF(A2:A2="","","BOTH")))

  55. Bayasaa says:

    Hello,

    I want to create automatic calculation for impact assessment. It has 3 columns for criteria; A, B and C. So, when i enter criteria in these tree columns 4th column must automatically calculate date. for example: when enter A= local, B= temporary, c=medium, d column calculates "low"

  56. Bayasaa says:

    Hello,

    I want to create automatic calculation for impact assessment. It has 3 columns for criteria; A, B and C. So, when i enter criteria in these tree columns 4th column must automatically calculate date. for example: when enter A= local, B= temporary, c=medium, d column calculates "low". how can i enter the formula in d column?

  57. Doug says:

    Bayasaa:
    I think this will work for you:
    Enter this in D1:
    =IF(AND(A1="Local",B1="Temporary",C1="Medium"),"Low","Not Low")

    • Caroline says:

      No I have never used either one of them before.

    • Caroline says:

      Oh and that table is 8 x 8
      Up to 8 color prints and 8 price range

    • Caroline says:

      It’s excel online so I’m going to guess the most up to date one.. it just frustrating when I’m doing a quote at work that I have to keep looking at the sheet and there is more then one sheet like this so I wanted to put it in excel since that is where I build my quotes.

  58. Caroline says:

    I’m looking to do a formula . Having trouble with it. What I’m looking for is pricing. If the total units is between 56 and 144 and it is a one color print the price would be .75 but if the units is between 145 and 200 and it is a one color print the price would be 1.00. Can anyone help me with this?

  59. Caroline says:

    It’s like a table 1 color print between 36 and 66 is .72

    1 color print between 66 and 144 is .97

    2 color print between 36 and 66 is .97

    2 color print between 66 and 144 is 1.15

    And it keeps going but I would like to populate the amount in one cell. So if I had a 3 color print and it was between 68 and 145 the price would be 1.15
    But if I had a 2 color from and it was between 0 to 68 then it would be 1.20

  60. Melissa says:

    I need help in figuring out how to write a formula:
    I have four rows and twelve columns for my spreadsheet.
    Two of the rows will have whole numbers manually entered by me and my co-workers.
    I want to write formulas for the other two rows to do automatic calculations based on the data in the other two rows.
    Example:
    If the value of the data in cell A of row four is less than or equal to the value of the data in cell A of row one, then the value of the data in cell A of row two needs to equal the value of the data in cell A of row four.
    But, if the value of the data in Cell A of row four is greater than the value of the data in cell A of row one, then the value of the data in cell A of row two needs to equal the value of the data in cell A of row one.

    Numerical Example:
    Row 1 Cell A we manually enter the number 34
    Row 4 Cell A we manually enter the number 52

    Because (Row 4 Cell A) 52 is greater than (Row 1 Cell A)34, the value that should appear in Row 2 Cell A is (The value that is in Row 1 Cell A) 34

    or

    Row 1 Cell A we manually enter the number 42
    Row 4 Cell A we manually enter the number 36

    Because (Row 4 Cell A) 36 is less than (Row 1 Cell A) 42, the value that should appear in Row 2 Cell A is (The value that is in Row 4 Cell A) 36

    Once I figure out how to write this formula, I know how to write the formula for the cells in row three, which will be a simple subtraction formula.

    Can you help me please?

  61. Nathan says:

    Hi,

    I'm curious if it's possible to use an IF function to compare text values.

    For instance, I download emails from one report then enter them in a cell next to the emails from an original report...I'd like to simply enter an IF function of some sort to compare them (as an automatic QC) rather than visually compare.

    Any help would be greatly appreciated!

  62. Garth says:

    Hi,

    Below spreadsheet shows columns A-D and rows 1-9.

    I require a formula that states if A1 is blank & D1 contains text, insert a "B" in column A1 or leave existing values. Thanks
    A B C D

    1 3 K240 K240 - EARTHWORKS

    2 B K250 K250 - Clearing and grubbing

    3 K260 K260 - (a) Soft excavation

    4 3 K270 K270 - BASES 1 - 50

    5 K280 K280 - Blinding

    6 K290 K290 - Reinforcing

    7 K300 K300 - Formwork to sides of Bases

    8 K310 K310 - Concrete to Bases

    9 K320 K320 - Formwork to sides of plinths

  63. RATNAKAR says:

    IF I WANT GREATER THAN 10 DIGITS AND LESS THAN 10 DIGIT NUMBER IN ONE CELL ?
    HOW TO APPLY FORMULA

  64. RAJESH says:

    if a number 15 digits is correct but less then 15 digits number is wrong how to work out this

    • Doug says:

      Rajesh:
      If I understand your question you want to create an IF statement that checks the number of digits in a cell and returns one thing if it does and another thing if it does not. If that's what you're looking for the formula is: =IF(LEN(D35)=15,"Yes","No")
      This is for a digit count of exactly 15. Any digits more or less will return "No".
      Is that what you want?

  65. Rudy says:

    Hi.

    How can I make a formula who can do this
    content of cell A1 is 1 then display content of cell D1
    and if content A1 is 2 then display content of D2 etc.
    =IF(A1=1;D1) this works but only for A1=1 how can I put varios
    formulas together
    =IF A1=1 then D1, IF A1= 2 then D2 this one does not work.

    Thanks Rudy.

    • Doug says:

      Rudy:
      Are 1 and 2 the only values that A1 can hold?
      If so, would =IF(A1=1,D1,D2) work? It says, if A1 equals 1 then display D1 otherwise display D2.

  66. SAMRAT PAHWA says:

    Logically, I want an excel function to check the value of week number and return the text string from one of Quarter1, Quarter2, Quarter3, Quarter4.

    For this, I'm trying to IF function to return the text string.

    The formula that I have used is -
    (Note-A3 holds the value of week number.)

    =IF(A3=13=25=37,"Q4","Please Check"))))

    The IF function works fine for week number values less than 13.
    However, for values 13 and above, it returns the text string Please Check.

    Kindly help diagnose the problem. And propose if any other function can be used to achieve the same result in a simplified way!

    • Doug says:

      Samrat:
      I'm not sure what you're looking for, but it might be this.
      =IF(A3=13,"Q1",IF(A3=25,"Q2",IF(A3=37,"Q3",IF(A3=56,"Q4","Please Check"))))
      A3 will have to hold these numbers for the Q value to be displayed otherwise the cell will display "Please Check".

  67. tony says:

    Any help would be appreciated. I cannot figure out how to make this work.
    If C5 = Y and D5 = Y then E5 = L. If C5 = N and D5 = N then E5 = H. If C5 = Y and D5 = N then E5 = M. If C5 = N and D5 = Y then E5 = M.
    Thanks in advance

  68. Jerry says:

    I am trying to create a tracking form for specific tasks with SLA's. In one cell, I have a specific task and in another cell the SLA say is "2" days.

    I am trying to get the SLA's to populate when selecting a certain tasks.

    Any help you can provide will be greatly appreciated.

    Thanks

  69. Daniel says:

    Hi i am lookin to use the function below but it isnt working.

    =IF(B2 = I2,"YES","NO")

    Basically both cells contain text,

    So for example B2 would read "Dispatched" and I2 would read "Delivered" and the forumla would return the word YES

    If B2 would read "Dispatched" and I2 also would read "Dispatched" then the cell would return NO

    Its basically to see if the status of the booking has changed overnight and would be filtered to yer or no?

    Thank you for any advise or help?

  70. Nazanin Daneshvar says:

    I would like to ask a question :
    I have 4 text alternatives like ( Apple, Orange, Banana and pear).
    I want excel to check a cell and if that cell contains one of them do different sum calculation. for instance if it is apple sum up C1 and D5 and E3 and if it is orange sum C2 and D3 and E1 and put this sum in cell F1
    how can I do this ?

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!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite 2018.4 Summer Offer