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

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

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

Excel IF function - syntax and usage

The IF function is one of 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 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

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 (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 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 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 IF statement to return TRUE and FALSE as the logical values (Boolean values) that other 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 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 IF function's syntax, let's look at some formula examples and learn how to use it in real-life scenarios.

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 examples for text values

Generally, you write an 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 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 with wildcard (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 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 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,900 responses to "Using IF function in Excel: formulas for numbers, text, dates, blank cells"

  1. Gabriel says:

    I need a simple formula, but cannot seem to figure out the answer. I need an IF statement. If there is text in either C8 or C9 greater than 0, then ((c8+c9)/c5) then show -. So far I have =IF(C8>0,((C8+C9)/C5),"-") which works if I have text in c8, but I cannot figure out how to include c9. I may not always have text in C8 and still need the percentage to calculate if c9 has text too, Thanks

  2. Thad says:

    I am in if assistance. This is what I am attempting to do. I need to calculate the total amount (dollars) that meet delinquency days. For example, Column A is the amount, Column B is the days late, and return the total in other cells based of a time span. "0-29" in Cell A7, "30-59" in Cell A8, and "60-89" in cell A9

  3. ENITY says:

    Hi Svetlana
    Please if I have a formula like this:G1=A1&B1&C1&D1&E1&F1 the value of the cells are in words not numbers.the formula actually gave me the my desired results but how can I give a demacation like " , " in between each words. Secondly, how can I make the results to centralized in the cell.
    Thanks.

  4. Nicola Churms says:

    Hi- am I able to get a cell to say “1 week” if a different cell says “Extreme”?
    If so can multiple choices and responses then be made to the same cell?

  5. Tom Terribilini says:

    If you are trying to match two different spreadsheet, one spreadsheet has a number that matches the other spreadsheet but you need to enter the code number in another cell on the other sheet can you write a formula for that ?

  6. Edgar says:

    Hi I would like to create a formula that gives out 100% if "Text1" or "Text2" but a variable percentage that I put in on my own if "Text3" or "Text4". I have at this point reached this formula which does not seem to work: = IF(OR(A3="Text1";"Text2";);100%;"")
    Excel complains and says that my conditions for OR formula needs to be a number, but my conditions are in text so I dont know what to do, so maybe another forumla similiar to OR but you can use text. Maybe some sort of If and Else formula needs to be used here but I dont know how.

  7. Paul-Simon MULDOWNEY says:

    I am trying to use =IF... If the text in C48 equals a picklist (five different products) I would like cell E48 to equal the dollar value associated with the picklist name.

  8. Ali Baba says:

    my question is that,
    my formula is accurate, but i want that if in my selected cell no any data in formula cell show that " - " character,
    mostly when your selected cell not have data , your formula cell show that " #value! "
    I want that show as like " - ", with out effected the formula,
    please help me in this task.

  9. ple hope says:

    I need an IF statement that determines the unfilled up cell.
    ex.
    if a number of cells like 05,P5,Q5,R5,S5,T5,U5,V5,W5 which belong to step 1, step 2 and soon are blank I need to specify which step is missing.

  10. David C says:

    Hi, i am so terrible with code, a little help with the following would be much appreciated.

    how do i write an if statement for, If Column 'N' has text 'Not completed' then Column 'O' must be a mandatory cell to fill(text required)

  11. Belinda says:

    Hi I am trying to create a formula that allows me to quickly match the same data that appears in 2 columns

    so if text in column A has a match with text in column b no matter what row it is displayed in, display OK can you please help?

  12. MURALI says:

    Hi,
    I am using a formula like "=IF(D49>=35,"PASS", IF(E49>=35,"PASS", IF(F49>=35,"PASS", IF(G49>=35,"PASS", IF(H49>=35,"PASS", "FAIL")))))"
    In this any one cell 35 also its coming "PASS". Please get me some solution in this and resolve with an appropriate formula.

  13. ANV says:

    Hi,

    I am trying to do an IF then formula but having trouble coming up with it. I have 4 different columns that I mark with an X dependent on which patient meets which column. However I want an additional column (if,then column) that will calculate if I have put in X in any of the 4 columns in that row and to mark it by yes if there is an X or no if there is not. Can you assist with this?? Thank you!

  14. Candice says:

    Hi, I am struggling to set the formula for the following:
    Column B needs to read May if the value in column C is between May 1 and May 31, 2020.

    • Hello Candice!
      Please try the following formula:

      =VLOOKUP(MONTH(C1), {1,"January";2,"February";3,"March";4,"April";5,"May";6, "June";7,"July";8,"August";9,"September";10,"October";11, "November";12,"December"}, 2,0)

      I hope it’ll be helpful.

      • Philip says:

        Hi,

        Similar Question with the dates. I have a source data and I want to get the dates based on cut-off.
        1. Row 1 . May 23 - June 20, 2019. Payments made was June 21 (Column C). I would like my Column D to read July.
        2. Row 2. April 23 - May 22, 2020. Payments made was April 25 (Column C). I would like my Column D to read May.
        Can I use "IFS Function" for this? Thank you in advance.

        • Hello Philip!
          I cannot recommend you which function to use - IF or IFS, since you did not say anything about the conditions under which the formula will determine the month in column D. If there is one condition, then you can use IF. But this is only an assumption. No information needed to answer.

    • Hi Candice,

      Assuming column C contains dates, you can extract month to column B with this simple formula:
      =TEXT(C1, "mmmm")

  15. Christa says:

    Good day,
    If a cell has no amount - in need to make it zero.
    I use to use this formula and now it is not working:
    =if(d4"",0,D4)

  16. Mahesh says:

    I select a date from cell Q33 ( 04-May-2020), and in cell R33 add some duration to it from either P34, or Q34 and or R34 (Year, month and day respectively). In this case 0 years (P34), 0 months (Q34), 200 days (R34) which gives me 16-Nov-20 in R33. The date so arrived at in R33 should not be greater than year end date i.e, 31-Mar-21. If it exceeds it should return FALSE in R33, if not it should return the correct date.
    I tried following formula, but it does not work. Please help.

    =IF(DATE(YEAR(Q33)+P34,MONTH(Q33)+Q34,DAY(Q33)+R34)>(DATE(YEAR(Q33)+(Q33>DATE(YEAR(Q33),3,31)),3,31),"FALSE",(DATE(YEAR(Q33)+P34,MONTH(Q33)+Q34,DAY(Q33)+R34))

    Thanks in anticipation.

    • Hello Mahesh!
      The formula you wrote does not work. I don’t quite understand why you have the end of the year - March 31. May 4 plus 200 days is November 20, and not November 16, as you have.
      I suggest using the formula

      =IF(DATE(YEAR(Q33)+P34, MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34 > R33,Q33,DATE(YEAR(Q33)+P34,MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34)

      where R33 is the end date of the year.

      • Mahesh says:

        Hello Alexander!
        This is used for calculating interest on investment starting and ending in same Fin. Year (FY).
        We have FY starting 1 April and ending 31 March e.g., 1-Apr-20 to 31-Mar-21.
        Thus tenure can be max 1 year or less than 12 months or less than 365 days or in months and days.
        Starting date (Q33) is always =TODAY() and cell is locked, but end date (R33) can not be more than 31-March of FY. Thus if tenure input (P34 Y, Q34 M, R34 D) given is such that end date goes beyond 31-March cell (R33) must return "FALSE".
        First part of formula has to add tenure to start date, > (THEN) part of formula has to find FY end date using start date, and (else) part has to either give end date or if more than FY end date return FALSE.
        Tried your suggestion, but it seeks >#REF! as follows: =IF(DATE(YEAR(Q33)+P34, MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34>#REF!,Q33,DATE(YEAR(Q33)+P34,MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34). This is because formula is being built in cell R33 so it cannot have input >R33
        Kindly suggest.
        Thanks.

        • Hello Mahesh!
          I copied the formula from a blog in Excel and made sure that it works. Perhaps the error #REF! caused by incorrect copying. To make the formula fit your needs, use

          =IF((EDATE(Q33,Q34+P34*12)+R34) > DATE(2021,3,31),FALSE,EDATE(Q33,Q34+P34*12)+R34)

          • Mahesh says:

            Hello Alexander.
            This formula works.
            But, I would request you to help make year change automatic i.e., there should be no need to change (2021,3,31) with every FY change in >DATE(2021,3,31) e.g. if date next year is 09-May-2021 it should become >DATE(2022,3,31). Please suggest replacement of Date(2021,3,31) with a formula to achieve it.
            Thanks.

  17. BHAGYESH SHIRUDE says:

    DEAR SIR,

    1. amount 15000 below amount fix 1800 amount 30 days but less days amount per day less
    2. 15000 and above amount fix 12% amount
    please share excel formula

  18. mae says:

    good day sir, I would like to ask, if there's a formula that when I put "paid" automatically a blank cell will pop up where I can input the date. Thank you

  19. winterstory says:

    hello,
    I wanted to put a date in one cell then it will populate in other cell. for example if I put a date in A1, whatever I put in A1 should populate in A3, A5, and A7. If I put a date in A3 then it will populate in A1, A5, and A7. If I put a date in A5 then it will populate in A1, A3, and A7. If I put a date in A7 then it will populate in A1, A3, and A5. Hope you can help me with this. Thank you

  20. Philip says:

    Hi,

    I am trying to write IF functions based on multiple criteria. I need a formula to demonstrate that IF cell V8 is a date that you put in, for example 20/04/20 and cell W8 is <= 16:00:00 (time), then cell Y8 can be either 20/04/20 or 21/04/20 (or whatever the next business day is) and cell Z8 can be equal to or less than 12:00:00 (time) the next business day, if true then pass, If false then fail.

    Thanks

    • Hello Philip!
      I’m sorry but your task is not entirely clear to me. I can only help you if you accurately describe all the conditions. For example, you wrote that cell Y8 can be either 20/04/20 or 21/04/20. So what date should it be there? Z8 may be equal to or less than 12:00:00 the next business day. Is it 10:00 a.m. or 11:00 a.m.? For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  21. Amy says:

    Hi,
    Wondering if someone can please help me with a formula?
    If column G contains the text "Yes", then I want column I to generate a sequential number, starting at 001. If G contains "No", then no number is to be generated and the cell will remain blank, and the next sequential number (002) will appear the next time column G contains "Yes"
    I understand the IF function, but am struggling with the numbering part.
    So far I have =IF(G:G="Yes","*","")- I need to fill in the * part!
    Thanks :)

    • Hello Amy!
      Please try the following formula:

      =IF(G1="Yes",COUNTIF($G$1:G1,"Yes"),"")

      After that you can copy this formula down along the column.
      Please go to Format Cells, choose Number -> Custom Format and set
      00#
      Or instead of the number in the cell, you can write text and not use custom format:

      =IF(G1="Yes",TEXT(COUNTIF($G$1:G1,"Yes"),"00#"),"")

      I hope this will help

  22. Penny says:

    Hi! I am trying to create a complicated If Then statement and haven't had success. Could you help?
    If the text in a cell says "Participant #1", then I want it to tell me the name which is found in cell C2. But if the text says "Participant #2" then I want it to tell the name in cell D2. And so on through to Participant #4. If there is no text then leave it blank.

    =IF("Participant #1",E2, IF("Participant #2",F2, IF("Participant #3",G2, IF("Participant #4",H2,"")))
    This is what I had, but is saying I cannot coerce my formula into a boolean equation... what on earth?

    Thank you so much for help!

    • Hello Penny!
      If I understand your task correctly, the following formula should work for you:

      =IF(B1="Participant #1",F2, IF(B1="Participant #2",G2, IF(B1="Participant #3",H2, IF(B1="Participant #4",I2,""))))

      Hope this is what you need.

      • Penny says:

        I ended up having to use this sequence, which I got from an excel whiz that I happened to connect with through a friend:

        =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2, "Participant #1", $E2 & " " & $C2), "Participant #2", $F2 & " " & $C2), "Participant #3", $G2 & " " & $C2), "Participant #4", $H2 & " " & $C2)

        K2 is the cell of the activity participants, E2 / F2, etc. are the Participant First Names, and C2 is the last name

  23. Larry Fernandez says:

    I'm having trouble using the IF logic statement- here it is =IF(C2=7592.00,1251.80,0. What Im trying to say is if the value in C2 is (7592.00) if true input 1251.80, if false input 0.
    C2 actually does not contain 7592.00. Its referencing and reading a value from another cell in a different sheet. ='DRP Implementation'!N127 Result is Im not getting the result I want 1251.80 if 7592.00 is present. Is it because C2 is a ref cell and not a discrete numerical value? How would you correct this?

    • Hello Larry!
      First off, please check your Excel settings: Excel - File - Options - Advanced - Update links to other documents. Also, please go to Data->Edit Links and make sure the autoupdate for each link is enabled.
      Check the automatic calculation in your Excel. Go to File -> Options -> Formulas -> Calculation Options and switch to Automatic option there.
      Note! In this case, you'll need to hit Shift+F9 to recalculate the formulas in any workbook you're working with.
      Hope it'll help, otherwise please do not hesitate to contact me back.

  24. vincent keever says:

    I am looking for a help finding a formula

    If cell A1 is a negative number it will turn to a positive number and if cell A1 is a positive number it will turn to a zero.

    • Hello Vincent!
      If a number is written in the cell, Excel can only change it using VBA. An Excel formula can only change the value of the cell in which it is written. But if a number is written in the cell, then you will not be able to write the formula into it.
      The formula in cell B1 might look like this:

      =IF(A1 < 0,-A1,0)

      I hope it’ll be helpful.

  25. vinod says:

    Hello,
    Can you please assist me to create formula for following scenario
    When I put patient's name in a cell, patient's UHID unique number should reflect automatically or vice versa
    Thanks

  26. Lord ace says:

    I have a row, the 'amount' row, containing numerical figures from about 100 to about 1000000. I need a formula that will put corresponding figures in another cell. For example if the figures In the 'amount' row are greater than 100 and less than 5200, 100 should go to the cells beside the "amount" row, which I'll call the 'charge' row. And if the cells contains figures greater than 5,200 and less than 10300, then 200 should be in the 'charge' row, so that at the end, I can calculate the total charge for every single amount figure.

  27. Dusty says:

    I am having trouble with this If Formula. =IF(K3/31*(X3-H3+1)+M3 >200, "$50"), 200, "$50)
    3.33 X 24 days = $79.92, M2 =150 (150 + $79.92 is more than $200 so I need the return to be $50. If less than $200 I need the total of (K2/31)*(X2-H2+1).

    ANY help would be greatly appreciated. Please and thanks

  28. Nic says:

    I have a row of cells which have values in for lab results - I can't figure out a formula to work out a minimum value where values are either numbers or come as say <0.05 etc. i.e where they are less than the laboratories limit of detection (<LOD). I want the minimum number so i want it to register that <0.05 is less than 0.06 either through the presence of a less than symbol or any other way. I tried to play around with any cell being greater than 0 calculate a minimum and for where cells do not to populate as"<LOD". i.e. 1.9 , < 0.05, 0.29 would return a minimum of "<LOD". 1.9, 0.06, 0.29 would return a minimum of 0.06 and < 0.05 < 0.05 < 0.05 would return a minimum of "<LOD". Any help would be greatly appreciated - I can't find any examples where you have an if statement followed by a function if the statement is true, and to present a text value if not ("<LOD").

    • Hello Nic!
      If I understand your task correctly, the following formula should work for you:

      =IF(NOT(ISERROR(MATCH("< *",A7:A15,0)>0))," < LOD",MIN(A7:A15))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      • NIc says:

        Hi Alexander,
        Thank you for your time - this seemed to work perfectly. Is there a way to do the same for the max, i.e highest number or <LOD if all are less than detection. I've tried playing around with the formula to try for max but can't get it to work for all cases. Please could you assist. Thanks again.

        • Hello Nic!
          I’m sorry but your task is not entirely clear to me.
          For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find. It’ll help me understand it better and find a solution for you. Thank you.

          • Nic says:

            Hi Alexander.
            So I have two columns in the spread sheet, one which calculates the minimum concentration from all the values from the lab results, which now works using your formula to pick the lowest concentration - <LOD (< limit of detection) or the lowest number if above that. The second column I need your assistance with is to work out the maximum value for all the cells in the row, as before some have < 0.05 for example, if all the concentrations are above this that's fine but it doesn't populate an answer for where all are <LOD. And the maximum in that case would be <LOD. Hope that clarifies things?

  29. Hernan says:

    I cant find what the problem is with this simple thing
    =IF(BASE!MONTH=ENGINE!B1,"yes","No"

    • Hello Herman!
      You did not indicate exactly which error occurs in your formula. However, MONTH is a function and cannot be a link. Therefore, BASE! MONTH does not make sense and is a mistake.
      The formula lacks the brackets at the end.
      If you give more complete information, perhaps I can still suggest something else.

  30. Teresa says:

    Hello,

    I have some dates in column K that I periodically add to or update, also some blanks that don't have dates added yet. I would like a formula for column L that gives dates 6 months in the future if there is a date in column K. However, if there is no date (blank cell) in column K, I would like the corresponding L cell to remain blank until a date in inputted into the K cell. Hope that makes sense! I appreciate any help.

  31. Matthew says:

    Hello,
    I am struggling with my nested IF formula. I am trying to return a "Y" or "N" if the given date meets certain requirements but sometimes strings of text can be found in the cell. If the string of text matches I would like it to return "N" or "Y" based off the requirements. Any help would be greatly appreciated.

    =IF(EDATE(A5,12)<TODAY(),"N",IF(A5TODAY()+30,OVERDUE,IF(SEARCH("OVERDUE",A5),"N","Y"))))

  32. PRABHAKR says:

    Hello Mathew
    How can I delete a group of numbers that have same last digit from a list.
    For eg: 10, 22, 31, 32, 35, 38, 40, 42, 44, 50, 51, 52, 62, 63
    From this list numbers have last digit '2' are to be deleted. 22, 32, 42, 52 and 62
    I appreciate any help.
    Thank you.

  33. Sarah says:

    Hello
    I am struggling with an IF formula, hopefully i have explained it clear enought.

    I want to return a value to cell V1 (the cell i am entering the formal into), if the value of cell U1 is greater than 0.1. The value i want to return is in cell T1, if the value is less than 0.1, i want to return a value of 0.

    Any help would be much appreciated

    Thank-you

  34. lakshmi says:

    i have "NEFT/BKIDN20137329397/GCHZ0000M12104/ " in one worksheet and "BKIDN20137329397" in other workbook with some values. how to use if formula to bring these values in first worksheet.

  35. priscilla says:

    Hi
    I would like to return a value to be 1 if the division of the two cell equals or greater than 1 and .5 if it's less than 1
    =if(c5/d5>="1","1",".5")
    I inputted the formula above but it's not showing the proper value..
    Hope it makes sense. Much appreciated, ty!!

  36. Asher says:

    Thanks for the article.
    I need help with writing out a formula that says, if column B = yes and column C = yes, then D should say True.
    So far I tried:
    =IF(B2="YES",TRUE(IF(C2="YES",TRUE),False))

    Looking forward to hearing from you.

    Rgs

  37. Cresia says:

    Hi, I've been reading your article and i must say it's outstanding. However though it was useful in some areas I still found myself stuck.
    I've been trying to insert an if function
    =If(G2>=0.5,C2,"APPROVE", "DISAPPROVE")
    But its not working
    Any Help is appreciated

  38. Praveen Dagar says:

    Hi,
    Good morning to all of you.
    Please suggest me a formula.
    =if(A1>=10,"Same Value of A1", if(A1<10, "Please Put One Zero Before Value of A1"))

  39. kasper says:

    Hi,
    what a good and clear explanations on the website, credits to the maker. however i am not an excel expert and still cant figure out how to move on with what i want
    i have 2 check boxes 1 in H4 (with comment 12hrs) and 1 in I4 (with comment 24hrs)
    now i want in the mergedbox below H/I 5 to appear a text when checking one of the boxes
    if box H4 is checked i want to see 1 x operator
    if box I4 is checked i want to see 2 x operator
    every time i try to make an if formula excel gives an error that i start a formula and i need to put an ' before the text. can you please help me :)
    thanks in advance,
    regards kasper,

    • Hello Kasper!
      If I understand your task correctly, the following formula should work for you:

      =IF(H5=TRUE,1*H9,IF(I5=TRUE,H9*2,H9))

      I don’t understand what “operator” is, so I replaced it with H9.
      Hope this is what you need.

      • kasper says:

        unfortunately its not working, 1x operator is just the text I want to show in cell H5
        so like they only need one person for 12 hours work or 2 persons for 24 hours of work

        • I’m sorry but your task is not entirely clear to me.You did not explain your problem in detail.
          For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

          • kasper says:

            Hi, sorry my story is not clear.
            in short i have 2 check boxes and i want to show a text in an other cell when checking the check box 1 and if i checking check box 2 i want to show an other text in a different cell

            regards kasper

  40. Bewar says:

    Hi,
    could you please help.
    I am trying to find a way to do this using an IF statement that would do the following: IF(A=B,C,do nothing), in other words if the condition A=B, is true, put the value C in the cell, else if the condition fails, leave the old value in the cell. There is a way to do this in Excel but I haven't been able to find. Instead I have to manually open each report sheet and do a copy and paste from where the report I receive.

    • Hello Bewar!
      If a value is already written in the cell, then it is no longer possible to write the formula into it. The Excel function can’t change values in other cells, it returns a value only in a cell where it is written. You'd better use VBA to solve your task.

  41. Doug C says:

    Would it be possible to use an IF statement, to say if a cell is still empty after a date has passed the "True" would be "OK" and "False" would be "Late"?

  42. James says:

    =IF(R3="CLower",V3*100%)
    How can I make the above formula repeatedly and give me different relevant answers without false or true

    • Hello James!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

  43. S&IP says:

    If A+ then "Outstanding"
    If A then "Excellent"
    If B+ then "Very Good"
    If B then "Good"
    If C+ then "Satisfactory"
    If C then "Acceptable"
    If D+ then "Partially Acceptable"
    If D then "Insufficient"
    If E then "Very Insufficient"
    For this condition I used "=IF(N7, IF(N7="A+", "Outstanding", IF(N7="A", "Excellent", IF(N7="B+", "Very Good", IF(N7="B", "Good", IF(N7="C+", "Satisfactory", IF(N7="C", "Acceptable", IF(N7="D+", "Partially Acceptable", IF(N7="D", "Insufficient", "Very Insufficient")))))))))" but it show "VALUE!".
    Pls Help me

    • Hello!
      If I understand your task correctly, the following formulas should work for you

      =IF(N7="A+","Outstanding",IF(N7="A","Excellent", IF(N7="B+","Very Good",IF(N7="B","Good", IF(N7="C+","Satisfactory",IF(N7="C","Acceptable",IF(N7="D+","Partially Acceptable", IF(N7="D","Insufficient","Very Insufficient"))))))))

      I hope it’ll be helpful.

  44. RONNI KOT WENZELL says:

    Hi Alexander!
    Thank you for a great and helpful site!! I have three cells (B13, C13, and D13) that can contain numbers (usually nothing or somewhere between 1 to 10). If one (or more) of these three cells contain a number (if none of them are empty), I would like to extract a name that is written in cell C7 - and with the sum of the three cells (B13, C13 and, D13) listed with an "x" before the name. Example: B13 [1], C13 [empty], D13 [5], C7 [Ronni] - would generate: "6x Ronni". Is this possible to make? Thank you very much for your help!!

    • RONNI KOT WENZELL says:

      Sorry, let me correct my question:
      I have three cells (B13, C13, and D13) that can contain numbers (usually nothing or somewhere between 1 to 10). IF one (or more) of these three cells contains a number, I would like to extract a name that is written in cell C7 - and with the sum of the three cells (B13, C13 and, D13) listed with an "x" before the name. Example: B13 [1], C13 [empty], D13 [5], C7 [Ronni] - would generate: "6x Ronni".

    • Hello Ronni!
      Please use the following formula

      =IF(OR(B13<>"",C13<>"",D13<>""),CONCATENATE(B13+C13+D13,"x ",C7),"")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      • RONNI KOT WENZELL says:

        Thank you very much, Alexander! It works perfectly! Now, I'm trying to pull different cells with the formula that you helped me with, to list the names into the same cell/sentence, though I can't figure out why I can't make that work. Your single formula works.

        =IF(OR(B14"";C14"";D14"");CONCATENATE(B14+C14+D14;"x ";C7);
        =IF(OR(B35"";C35"";D35"");CONCATENATE(B35+C35+D35;"x ";C28);
        =IF(OR(B56"";C56"";D56"");CONCATENATE(B56+C56+D56;"x ";C49);
        =IF(OR(B77"";C77"";D77"");CONCATENATE(B77+C77+D77;"x ";C70);
        =IF(OR(B98"";C98"";D98"");CONCATENATE(B98+C98+D98;"x ";C91);
        =IF(OR(B119"";C119"";D119"");CONCATENATE(B119+C119+D119;"x ";C112);
        =IF(OR(B140"";C140"";D140"");CONCATENATE(B140+C140+D140;"x ";C133);
        =IF(OR(B161"";C161"";D161"");CONCATENATE(B161+C161+D161;"x ";C154);
        =IF(OR(B182"";C182"";D182"");CONCATENATE(B182+C182+D182;"x ";C185);
        =IF(OR(B203"";C203"";D203"");CONCATENATE(B203+C203+D203;"x ";C196);
        =IF(OR(B224"";C224"";D224"");CONCATENATE(B224+C224+D224;"x ";C197);
        =IF(OR(B245"";C245"";D245"");CONCATENATE(B245+C245+D245;"x ";C238);
        =IF(OR(B266"";C266"";D266"");CONCATENATE(B266+C266+D266;"x ";C259);
        =IF(OR(B287"";C287"";D287"");CONCATENATE(B287+C287+D287;"x ";C280);
        =IF(OR(B308"";C308"";D308"");CONCATENATE(B308+C308+D308;"x ";C301);
        =IF(OR(B329"";C329"";D329"");CONCATENATE(B329+C329+D329;"x ";C302);
        =IF(OR(B350"";C350"";D350"");CONCATENATE(B350+C350+D350;"x ";C343);
        =IF(OR(B371"";C371"";D371"");CONCATENATE(B371+C371+D371;"x ";C364);
        =IF(OR(B392"";C392"";D392"");CONCATENATE(B392+C392+D392;"x ";C385);
        =IF(OR(B413"";C413"";D413"");CONCATENATE(B413+C413+D413;"x ";C406);
        =IF(OR(B434"";C434"";D434"");CONCATENATE(B434+C434+D434;"x ";C427);
        =IF(OR(B455"";C455"";D455"");CONCATENATE(B455+C455+D455;"x ";C448);
        =IF(OR(B476"";C476"";D476"");CONCATENATE(B476+C476+D476;"x ";C469);
        =IF(OR(B497"";C497"";D497"");CONCATENATE(B497+C497+D497;"x ";C490);
        =IF(OR(B518"";C518"";D518"");CONCATENATE(B518+C518+D518;"x ";C511);
        =IF(OR(B539"";C539"";D539"");CONCATENATE(B539+C539+D539;"x ";C532);
        =IF(OR(B560"";C560"";D560"");CONCATENATE(B560+C560+D560;"x ";C553);
        =IF(OR(B581"";C581"";D581"");CONCATENATE(B581+C581+D581;"x ";C574);
        =IF(OR(B602"";C602"";D602"");CONCATENATE(B602+C602+D602;"x ";C595);
        =IF(OR(B623"";C623"";D623"");CONCATENATE(B623+C623+D623;"x ";C616);
        ;""))))))))))))))))))))))))))))))

        Ideally, I'd also try to separate the strings with a comma and space, so it'd generate 5x Ronni, 2x Alexander, 1x Pedro - though trying to take small babysteps here, haha.

        Once again, thank you for taking your time to help!

  45. Christina says:

    There are payroll list for June 2020
    1) Fill June Payoff with this rule: if monthly Wage is less then 20 000, then pay 20 000. In other case pay just Wage itself.
    What value should put in Value _if_ false

  46. kadevi says:

    Hi,
    Let say i have text "export" in C2 and C20 . Can I use formula =if(C2=C20,countifxxx,0) ?
    So if I want to change the text 'export' I don't need change every formula. is that possible?
    thanks to advice.

  47. Rachel says:

    Hi,

    Could you please help me? I have column J as text - apple;orange;bananas;vegetables (spinach, fenugreek, etc); acai berry. I want to create Column K as 1 if there is orange in Column J and 0 if not OR 1 if there is spinach in Column J and 0 if not. How can I do that? Please advise. Thank you for your help!

    • The formula will return 1 if there is "orange" in column J.

      =IF(COUNTIF(J:J,"orange") > 0,1,0)

      Hope this is what you need.

      • Rachel says:

        Hi Alexander,
        Thank you for your help but unfortunately the formula did not work. Just to clarify each row in J has continuous text such as Row 1 = apple;orange;bananas;vegetables(spinach,fenugreek,etc);acai berry
        Row 2 = orange;bananas;vegetables(spinach,fenugreek,etc)
        Row 3 = apple;orange;bananas
        and so on
        Also there is no space between the text and semi colon and following text
        Please advise

        • Hello!
          The formula I sent to you was created based on the description you provided in your first request.
          However, as far as I can see from your second comment, your task is now different from the original one. Hence, the formula fails to work.
          I also assume that the phrase "each row in J has continuous text" actually means "every cell in column J contains text."
          Try the following formula:

          =IF(IFERROR(SEARCH("orange",J2,1),0)>0,1,0)

          After that you can copy this formula down along the column.

  48. Jeff says:

    Hi. I am trying to create a formula that says:
    If (a1 =1 then c1 = 50) ( a1= 2 then c1 = 60) (a1= 3 then c1 = 70) (a1 = 4 then c3 = 10)
    Thanking you in advance.

    • Hello Jeff!
      A formula can only set a value in the cell in which it is written. The formula in C1 cannot change cell C3.Therefore, I assume that C3 is a mistake. Change C3 to C1. The formula in C1 is

      =IFERROR(CHOOSE(A1,50,60,70,10),"")

      Or use the If function

      =IF(A1=1,50,IF(A1=2,60,IF(A1=3,70, IF(A1=4,10, "") ) ) )

  49. Joe says:

    Are you able to create an IF then statement in excel and embed another function formula?
    Example:
    Column A2 contains a start date and time: 5/15/20 14:15.
    Column B2 contains an end date and time: 5/16/20 08:09.
    Column C2 contains a formula to caculate the duration between columns A and B in minutes: TEXT(B2-A2), "[mm]".
    I am looking for a formula where blanks will appear in column C2 if column A2 is not yet populated. I want to include the TEXT formula throughout column C, in order to auto populate the column C once columns A and B have data. Currently, using the formula TEXT(B2-A2), "[mm]" yields 00 in column C2 if A2 is blank.
    I tried the following formula, but cannot get it to work: If A2>0, TEXT(B2-A2,"[mm]", " " Explanation: If nothing is in column A, column C should be blank (i.e. not show 00). Thanks in advance for your assistance.

  50. Joe says:

    I have three columns. Columns A and B contain date & time information and column C contains the difference (in minutes) between A and B. The formula being used in column C is TEXT(B2-A2), "[mm]". Currently if nothing is in column A or B the formula in column C shows 00. I would like for it to show blanks.
    Please help me understand why I am unable to use the following formula so as to not see 00 in column C:
    IF A2>0, TEXT(B2-A2), "[mm]", " ". Explanation: If columns A and B are not populated, column C should appear as blank and not contain 00.

  51. joe says:

    I believe I found the error in the formula. The parentheses were not placed correctly..... Correct formula: IF (A1>0,TEXT(B1-A1,"[mmmm]")," ")

  52. Chris says:

    Hi,
    My scenario is the following: if a date is more than 3 years old then Good...if is less than 3 years Not Good. For example if 04/17/2016 is more than 3 years old from 01/01/2020. Thanks.

  53. Thomas says:

    I have a data from an ERP system which gives the amount with a symbol suffix 'Dr' for debit balance, and a 'Cr' for a credit balance. I want to capture the debit balances as a positive value and the credits with a negative value. Please help

    • Hello Thomas!
      I do not know how your values are written. You did not say anything about this. The IF function will not help here. But I think that you’ll find this formula useful

      =IFERROR(VALUE(SUBSTITUTE(A20,"Dr","")), -VALUE(SUBSTITUTE(A20,"Cr","")))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  54. ronni kot wenzell says:

    Dear Alexander,
    You've been so kind to help me with the following formula: =IF(OR(B35"";C35"";D35"");CONCATENATE(B35+C35+D35;"x ";C28);"")
    Would you be so kind to also guide me on how to include a second IF-formula (just with following cells B56, C56 and D56 with the name from cell C49? Preferably separated with a comma between the formulas, so the output would look like e.g.: 5x Ronni, 2x Alexander
    I've tried with following =IF(OR(B35"";C35"";D35"");CONCATENATE(B35+C35+D35;"x ";C28);IF(OR(B56"";C56"";D56"");CONCATENATE(B56+C56+D56;"x ";C49);"")) but I can't get it to work.
    Once again, thank you very much for your kind help!!
    Ronni

    • Hello Ronni!
      You just need to add another formula with the condition

      =IF(OR(B13<>"",C13<>"",D13<>""), CONCATENATE(B13+C13+D13,"x ",C7),"") & ", " & IF(OR(B35<>"",C35<>"",D35<>""), CONCATENATE(B35+C35+D35,"x ",C28),"")

      I hope it’ll be helpful.

      • RONNI KOT WENZELL says:

        Dear Alexander! Thank you very much for your answer and time. I'm not really sure why, but the output of the cell ends up only calling the first IF formula (C7 without C28). I've checked all the cells, made sure that they aren't empty. If I change the order of the two formulas, it calls (C28 without C7). And I'm not getting any error messages on the formula. Do you have any idea why that can be? Once again, thank you for your time!!

        =IF(OR(B13"",C13"",D13""), CONCATENATE(B13+C13+D13,"x ",C7),"") & ", " & IF(OR(B35"",C35"",D35""), CONCATENATE(B35+C35+D35,"x ",C28),"")

        • Hello Ronni!
          In my Excel workbook, the formula works. There are no ideas other than copy error.
          Try a different version of this formula, where instead of the & operator, the CONCATENATE function is used

          =CONCATENATE(IF(OR(B13<>"",C13<>"",D13<>""), CONCATENATE(B13+C13+D13,"x ",C7),""),", ",IF(OR(B35<>"",C35<>"",D35<>""), CONCATENATE(B35+C35+D35,"x ",C28),""))

  55. Matea says:

    Hi,
    I could use some help please.
    I have 3 variables that depend on the last letter of the main word and I would like to separate them as follows:
    XXXXXXA Yesterday
    XXXXXXD, XXXXXXXE, XXXXXXF Today
    XXXXXXK, XXXXXXL, XXXXXXM, XXXXXXN Tomorrow
    If try IF, OR, ISNUMBER SEARCH AND FIND but I can’t figure it out.
    So I just want it to write ‘Yesterday’ if last letter A, ‘Today’ if D,E or F and ‘Tomorrow’ if K, L, M or N as last letter.
    Thanks

    • Hello Matea!
      If I understand your task correctly, the following IF formula with nested IF conditions should work for you:

      =IF(SUM(--(RIGHT(N1,1)={"K","L","M","N"}))>=1,"Tomorrow", IF(SUM(--(RIGHT(N1,1)={"D","E","F"}))>=1,"Today", IF(RIGHT(N1,1)="A","Yesterday","")))

      I hope this will help

  56. Nicholas SIM says:

    Hi,
    How do I, if condition met, the cell equals to the value of another cell?
    For example;
    Cell C4 is empty. If the formula in cell U2 generate a '1', the value of C4 will automatically be equal to the value stated in cell R2.
    By the way, the value in cell R2 is 'A,B,C,D'.
    Please advise and thank you.

  57. Hannah says:

    Hi!
    I've been using the IF function with this formula:
    =IF(C26-5*$G$25<0,"N/A","YES")
    Now that equation is putting "YES" in the correct cells where I want them to. Now I want to change "YES" with a new condition that actually follows a recursive formula; I want to copy the column E7:E18 to another table I'm working on, but only start copying if that cell no longer satisfy the first condition I set up. Is there a way to create the formula such that if the previous IF function is false, I will still start at E7? Is there another way to do this?
    I look forward to your response :D

    • Hello Hannah!
      I’m sorry but your task is not entirely clear to me. What does E7: E18 have to do with your formula? What does "start at E7" mean? What condition do you want to include in your formula instead of “Yes”? Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  58. Andrew Gardiner says:

    Could some one help, please? I am trying to analyse the sales data for a new housing development. I have set up a table with my raw data for the 126 properties, with the following columns:

    A = Street number
    B = Street address
    C = developer’s plot number
    D = developer’s house type name (in text)
    E = developer’s house type code number (mix of numbers and letters)
    F = number of bedrooms the house has
    G = whether the house is a flat/terraced/semi-detached/detached
    H = size of house (ft2)
    I = sale price
    J = date of sale
    K = sale price/ft2

    I am trying to consolidate the raw data into a second table in the same worksheet so that I can calculate the average selling price for the 22 different house types. I find that if I sort the raw data table on house type name to group each house type together and then use the AVERAGE function In the consolidated table, when the raw data is then resorted on a different parameter (say street name) the data pull through for the average is the new cell content rather than the original cell content (ie the cell reference remains the same rather than changing to follow the original data). I have tried to set up a logic test (=IF(D5:D130)=D135,AVERAGE(I5:I130), ) where the range D5:D130 is the range of house type names in the raw data table, D135 is the specific house type name in the consolidated data table, and I5:I130 is the selling price in the raw data table. All I get is an error, either #VALUE! or #NAME! if I try fiddling with the logic test set up. What am I doing wrong?! Can anyone help, please?

  59. Nisha says:

    Good day,
    I am trying to create an 'If' formula for tasks that picks up a range from another sheet and returns the result on a summary tab.I am not sure if 'IF' is the correct formula as I can't get it to work.

    If C38:C42 (on the detailed tab) status is completed then return the text 'Completed, if FALSE then return 'To Complete' on the Summary tab.

    I have tried =IF(Revals!C38:C39="Complete","Completed","To Complete") but it didn't work.

    • Hello Nisha!
      If I understand your task correctly, the following formula IF should work for you:

      =IF(IFERROR(MATCH("Complete",C38:C39,0) > 0,0),"Completed","To Complete")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      • Nisha says:

        Thanks Alexander. I tried it but when I change the status it still returns the same value as if completed. Meaning, if I then went to the detailed tab and changed the cells one to complete and the other to to be completed, it still gives me a result on the summary page that the tasks have been completed when it hasn't.

        On the positive at least it is returning a value, i could not get it to even do that :-)
        Thanks

        • Hello!
          I expected that you pay attention to the fact that my formula does not have such cell references as yours. I do not have your workbook, so the links may not be the same. I think you just copied the formula and now want it to work? I have given you a sample. Use your cell references in it.

          • Nisha says:

            Hi Alexander, yes I changed to my cell references to pick up from the separate tab. However with me putting one as Complete and the rest as To complete, it still returned a value of Completed

            • Hello!
              I wrote this formula based on the description you provided in your original comment. But now it’s clear that you didn’t say everything. How many cells in your range? 2 or more? There were 2 of them in your formula. But now, according to your words, I see that this is not so. Do you need at least 1 match or matches in all cells? The formula you wrote speaks of one coincidence. Now is this not so? Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.

              • Nisha says:

                Sure, thank you for you help.
                It will be ranges of cells in one column. In this particular example, there are 5 rows in the column. The user is required to change the status to either Completed or Not completed. This is on the detailed tab. There are about 5 different headings with a range of tasks in each one. So I wanted to create a summary sheet that will then Look at each area and then return a result. If all tasks are not completed, even if only one tassks is the summary sheet should say not completed for that particular area. Eg below

                Sales
                Column B Column C
                Row 23 Payroll Reval Not Completed
                Row 24 Orders Reval Not Completed
                Row 25 Bank Reval Completed
                Row 26 Tax Reval Not Completed
                Row 27 Interco Reval Completed

                I hope this clarifies a bit

  60. Habibur Rahman says:

    if have in one cell 196.513K, then i want another cell 196.513/1000. Can it possible?

  61. TUSHAR says:

    IF MY CELL CONTAINED #N/A FROM A VLOOKUP VALUE HOW TO PUT 0

  62. Matt says:

    Hi,
    i need help with a formula
    If column "M" is less than £3.50 then it needs to say £3.50 and if its higher than £3.50 it needs to return the value stated in column "M"
    =IF(M5<3.49,"£3.50","M5")
    My formula is this so far but the higher value is saying M5 instead of the values higher than £3.50

  63. Kathy says:

    Hello~
    I have a birth date in one cell (example 7/22/15), and I need to another cell to auto populate the age in years based on the date 9/1/20. The value would be 4, but what would be a formula to populate that automatically?

  64. Aubrey Ritchie says:

    I hope you're still able to respond to this thread! I'm creating a bonus tracker and I'm wanting Excel to add $50 whenever someone achieves $500 over their goal. For example, the goal is $1,000 and someone achieves $1,500, then they would get an additional $50 payout. Another example would be the goal is $1,000 and someone achieves $2,000 then they would get an additional $100 in payout. Thank you!

  65. Sera says:

    Hi,
    If my company is giving me salary on every mid month and month end basis (Eg. 15 and 30 or 31 (depend on month end date) / for Feb month 28 or 29).every fortnight i received the 10000 dollar.But suppose i am leaving the company on 12-06-2020 then how much amount i will get?
    I need advance formula where we can drag the formula and take it out the amount for other employee details also.
    E.g.
    Leaving date Amt
    12-06-2020 10000
    26-02-2020 10000
    please let me know if additinal details required.

  66. Jack Herring says:

    Hi,

    I have a spreadsheet which has one column of company names, and one column of numbers. There is a table at the bottom, what I need is for one line in the table to add all of the number cells for each company name? They are in date order so the company names are not in alphabetical order.

    How can I do this?

    Thanks,

  67. Kashif Pasha says:

    Dear Alex Hi,
    Small Query what to write today Date in cell:(A1) if i type "OK" in Cell:(B1).
    please note Date Should not be got changed as the day change.
    Thanks
    KP

  68. DO1986 says:

    I am working on a spreadsheet relating to points (for employees). Each employees late and/or sick is recorded and they are given .5 or 1 point depending on late/sick call. After a year the employees point is removed. To keep the spreadsheet updated, there are formulas in place to change the cell to red once a year has passed. We recently made a change that the employees late will now be removed after 6 months. I need a formula that after 6 months late calls (column E), date is highlighted (column C). For the late point to be removed.
    Columns are as stated below.
    B - Employee ; C - Date ; D - Status ; E - Actual Leave (sick, late etc.) ; F - Points ; G - Total points
    Thank you in advance for your help.

    • Hello!
      To highlight a cell with a date that happened more than 6 months ago, use the conditional formatting formula

      =EDATE(TODAY(),-6) > C1

      I hope this will help

      • DO1986 says:

        Thank you for the reply, Alex. I appreciate your guidance.
        This is a running spreadsheet that is tracked daily, with different conditional formatting rules, making it easier for dates to be tracked and points to be removed/added.
        I was hoping there was a formula to only track lates from column E, within a 6 month time frame for dates from column C. Would this need to be an IF formula inputted as a rule?
        I am hoping this makes sense.
        Thanks again for your help.

        • Hello!
          Your problem is not entirely clear to me. If you want to compare dates with columns C and E, then you can use conditional formatting as a rule

          =EDATE(C1,6) > E1

          This condition is TRUE if between dates C1 and E1 is less than 6 months

          • DO1986 says:

            Column C are specific dates the employee called in. Column E is what the employee called in (late, sick, FMLA etc.). I need only the lates (from column E), the dates (from column C) within 6 months to be highlighted in order to remove points the employee has accumulated.
            Again, your help is very much appreciated. Thank you for your time.

            • Hello!
              Each time you describe your problem in a different way.
              What does "I need only the lates (from column E)" mean?
              What do you mean by "lates"?
              What is wrong in the formula
              =EDATE(TODAY(),-6) > C1 ???
              What result do you get? What result would you like to get?
              Do you need dates for the last 6 months (you wrote "within 6 months") or older than 6 months (you wrote "after 6 months")?

              • DO1986 says:

                I apologize for being so frustrating/confusing. Nothing is wrong with =EDATE(TODAY(),-6) > C1, except it is highlighting ALL dates from column C with 6 months or older. The formula works (and thank you for that), it's just missing one part I need.
                I only need dates highlighted from column C that are labeled "LATE" in column E. Column E has no dates, only words with the type of leave the employee was marked that particular date, e.g LATE, Sick, FMLA(family and medical leave act), LOA(leave of absence), NCNS(no call no show).
                Thank for for your continued help (because clearly I am no excel wizard).

  69. Candyce says:

    I have 3 columns, one column showing actual costs, one row showing the estimated cost and 3rd column showing my forecasted costs. I want to write an if statement for the forecasted column that will default to the value in the estimated column if our actual costs are zero. However if we do have actual costs I want it to reflect those costs. For example
    Actual Estimated Forecast
    12 5 12
    0 10 10
    3 0 3

  70. SALEEM AKRAM says:

    i am calculating the rent payment alert, where in sheet where rent is 2000
    month sr due date due amount paid balance
    1 1-04-2020 2000 1000 1000
    2 1-05-2020 2000 3000 0

    so i need logic for the due amount once the date of due smaller than todays date

    • Hello!
      I hope you have studied the recommendations in the above tutorial. Please specify what you were trying to find, what formula you used and what problem or error occurred. What do your numbers mean? It’ll help me understand the problem you faced better and help you.

  71. Palesa says:

    Hi,
    Please assist as my formula does not seem to be working. It gives wrong answers even for correct checks.
    Formula currently in use: =IF(A2=B2, "Wrongly Charged", "OK")
    The idea is to confirm if NUMBERS in A2 MATCH THOSE IN B2. IF SO THEN ANSWER SHOULD BE "OK"ELSE "WRONGLY CHARGED".
    Formula returns "WRONGLY CHARGED" even for numbers that are the same/equal.

    • Hello!
      I do not see your data, so I can only guess. Possible reasons why the equality condition for numbers in the IF formula does not work:
      1. Some of the numbers are saved as text. This is indicated by a green triangle in the upper left corner of the cell and left alignment.
      2. Numbers do not match in decimal places, which are hidden by number formatting.

  72. Charles Buckley says:

    I have a Date in B6, I want it to show a date in M6 thats 90 days out and if there is words in B6 regarding status "In installation or In permitting" I want it to be blank or show 0. Is that possible?

  73. UTKARSH SHARMA says:

    Hi sir,
    Please advice me the formula
    I have three ccolumn in excel sheet - Today date ,City and Departure date.for today date i used the formula =today().but i need the formula in departure date i person is coming from same city then today date = departure date and if outside from my city then today date less one day i.e yesterday date

    • Hello!
      Yesterday's date can be calculated using a simple formula
      =TODAY()-1
      But to give more detailed advice, I do not have the necessary information. Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

  74. Soheb Pathan says:

    I need help to excel sheet formats can works auto print with data find as per order...
    Example :
    first name ()
    Second name (). Etc.
    I am working same method in word the mailmerge using for auto data print.
    Give me solution for excel. How can use to autoprint in excel.

  75. Aditya says:

    Hi Alexander,
    I have a column with some digital values. I am writing an IF statement on the neighbouring column to add paranthesis around the number in the previous column.
    Ex. =IF(D10>0,[D10],0)
    Hoping that if D10 ha a value 5, the IF statement returns [5] adding the square parenthesis. But it does not. How do I fix this? Thanks!

    • Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(D10>0,"["&D10&"]",0)

      But at the same time, the number will be converted to text.
      You can also use a custom format without formulas.

      Please go to Format Cells, choose Number -> Custom Format and set

      \[#\];-#,##0;0

      I hope this will help

  76. Harshit Aranya says:

    =if ((a1="g"),1,or(a1="mg"),1,0)
    i want 1 if a1=g and a1=mg also if a1=kg then 0 and so on.

  77. King says:

    Column A = Date 30-June-2020, Column B = Date 30-Sep-2020 (*Overdue morethan 2 months)

    How to condition formatting highlight the cell in Column B in red color

  78. UTKARSH SHARMA says:

    sir Please help me to findout the solution IN EXCEL
    if i write AAA1,AAA2....AAA999 and if IT IS MORE THAN AAA999 THEN NEXT VALUE WILL BE AUTOMATICALLY AAB000
    AND THEN AAB000 TO AAB999 AND THEN AACOOO..

  79. Murali says:

    Hi, i am trying for if function when the one of cell value is True i need list of drop down values when False it should be Null

  80. Ann says:

    I need an equation that does two things. I need it to count a range of cells that has any text value. Also if the count is greater than 5 I need the blank to say "no more off" if the count is less than 5 then cell is blank. This is one of the equations I tried in cell k4 I typed. =count(L6:L22,"*"),IF(k4>5,"no more off","") i tried several different variations of this concept.

  81. abhinav says:

    i just want a formula where, there is a number like 364 ,now i want to put formula to find if last 2 digit is square of some number or not , if yes thn give value true(like in this case in 364 -----64 is square of 8) nd if not no thn give value false
    example 2 :- 382 in this 82 is not a square of any number therefore valuse come should be false.

  82. Michael Ochieng says:

    I would like to build a formula for calculating loan payable. Interest chargeable is 10% of the principal but if no payment is done by 10th of the following month, the interest gets compounded, meaning, interest is calculated on top of the added interest to the principal. Is this doable?

  83. JUN says:

    I NEED A FORMULA THAT IF= E:E = COMPLETED, THEN A1-(B1,C1,D1), E:E MEAN WHOLE COLUMN

  84. Ranjith P says:

    I have data in a 16R x 1C format. As per the formula given by me, only 1 cell will display the value in number based on some calculation and rest of the rows will display "Not Applicable". Cell which shows the number may vary depending upon the inputs, but rest 13 will show "Not Applicable". I want to report the number to another cell, Whether it appears on 1st row, or 2nd row or 12th row etc. only the number must be displayed on the reporting cell. Can you please help me to do the same?

  85. Tomaz says:

    Hi!

    I need a help with formula: IF A2 is 1, then 1, if A2 is 3 or 2, then 0.

  86. RONNI KOT WENZELL says:

    Dear Alexander.
    You've been more than kind, to help me before, and if it's not too much to ask, I would like to ask for your guidance ones again. I'm having a total of 30 cells (F10, F39, F68, F97, F126 and so forth) that contains the same dropbox of 8 different payment possibilities (PAGO, CARTÃO, CREDITO, DEBITO, DINHEIRO, TRANSFERENCIA, BOLETO, ASSINATURA). I'm trying to generate a formula that shows if one of more of the following 4 payment options (cartão, credito, debito, dinheiro) are listed in one or more of the 30 F-cells. For instance, if F10=BOLETO, F39="", F68=CARTÃO, F97=DINHEIRO, F126=CARTÃO... the formula would generate: "CARTÃO, DINHEIRO". Do you think you can help me with this? Sincere gratitude in advance!! Thank you!! Ronni

    • Hello Ronni!
      Excel formula cannot work with non-contiguous cells. So you can use something like this formula

      =CONCAT(IF(INDEX(COUNTIF($G$10:$G$40,$H$10:$H$13),1)>0,"debito, ",""), IF(INDEX(COUNTIF($G$10:$G$40,$H$10:$H$13),2)>0,"credito, ",""), IF(INDEX(COUNTIF($G$10:$G$40,$H$10:$H$13),3)>0,"dinheiro, ",""), IF(INDEX(COUNTIF($G$10:$G$40,$H$10:$H$13),4)>0,"cartão",""))

      Where
      formula in G10 is =F10
      formula in G11 is =F39 ....... etc
      in H10 written "debito"
      in H11 written "credito" ... etc

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  87. Premji Nitwal says:

    =IF(B2="",A1,N(A1)+1)
    I am using this formulae, but I didnt understand why series in A showing multiple times
    for B merged cells? How to fix this formulae? I don't want series in A to show multiple times.

  88. Mariska Botha says:

    Good day,

    I need to calculate data into a worksheet from specific Columns on a Mastersheet where it links back to specific Unit (Column B:B), amount of Sessions (Column D:D), but then also just for specific month (Column I:I).

    I could only manage thus far: =SUMIF(Master!B:B,CEN!B6,Master!D:D), but then how do I just get it to consider data for a specific month (Column I:I)?

  89. cavbella1071 says:

    i need a formula that i want to equal first column numbers to second column texts for example firs colum 25 34 56 second column love like hate 25=love 34=like 56=hate and excel learn these equalities and i want to use them after for ex. text sheet 25034 25789 25678 34567 34897 56987 excel and i use left operation and next column 25 25 34 34 56(think that it is a column) and excel write ney column love love love like like hate

    if i use matlab it gets easier or struggle or should i use only excel
    please teach this trick
    (sorry for my bad english)

  90. Ode says:

    Hello there!
    Here is what I am trying to do:
    If text in Column D (from Workbook 1) matches text in Column E (from Workbook 2) then enter text from Column B (from Workbook 2) in Column F in Workbook 1.
    I cannot figure out how to use the vlookup function for this exercise...
    Help would be very much appreciated... thanks a lot.

  91. Amit Sharma says:

    I am not able to figure out while applying multiple If conditions. Seeking you help.

    Conditions-1 :
    If I type >10 but 5 but 2 but 25 but 15 but 5 but <10 questions in Biology/English/ in 1 Hr then I will be getting 1 points.
    Sincere gratitude in advance.
    Thank you
    Amit Sharma

  92. Amit Sharma says:

    Seeking help of below mentioned conditions.
    If I type >10 but 5 but 2 but 25 but 15 but 5 but <10 questions in Bio/Eng in 1 Hr then I will be getting 1 points.
    Sincere gratitude in advance. Thank You - Amit Sharma

  93. Amit Sharma says:

    Seeking help of below mentioned conditions.
    If I type greater than 10 but less than 16 questions in Phy/Chem/Maths in 30 minutes then I will be getting 5 points.
    If I type greater than 5 but less than 10 questions in Phy/Chem/Maths in 20 minutes then I will be getting 3 points.
    If I type greater than 2 but less than 5 questions in Phy/Chem/Maths in 10 minutes then I will be getting 0 points.
    If I type greater than 25 but less than 35 questions in Bio/Eng in 1 Hr then I will be getting 5 points.
    If I type greater than 15 but less than 25 questions in Bio/Eng in 1 Hr then I will be getting 3 points.
    If I type greater than 5 but less than 10 questions in Bio/Eng in 1 Hr then I will be getting 1 points.
    Sincere gratitude in advance. Thank You - Amit Sharma

  94. Kyle Way says:

    Hi i'm trying to put an IF statement into a cell to show the amount of a transaction under the right category heading. So far I have =IF(D10="Office Equip.",C10,"") so it inputs the value in C10 but I need one IF statement specifically to cover a range of columns from I:AH all with different headings. How would I type this IF formula?

  95. Aurel Berciu says:

    Hi. I am generating a document that:
    A1 B1 C1
    A2 B2 C2
    A3 B3 C3
    A4 B4 C4
    Column A has dates, Column B is auto populated from C1 on the today date, but at the end pf the day C1 resets and on next day B2 gets auto populated.

    Can anyone help?

    • Hello Aurel!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail.
      What does the phrase e mean "Column B is auto populated from C1 on the today date"?
      Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  96. Adrienne says:

    a. insert a formula using the IF function that tests whether the age of the invoice is greater than 30.
    b. If the age of the invoice is greater than 30, subtract the due date from the current date.
    c. If the age of the invoice is less than or equal to 30, display 0 to show that the invoice is not overdue.

  97. Mods says:

    Hi. I want to tag a certain person as "regular" or "probationary" based on the XX number of their tenure derived from a formula and formatted to " X yr, X mo". My condition is that if the tenure is equal or greater than "0 yr, 6 mo" it should be tagged as regular. My problem is that it returns "probationary" to other values that start with "0 yr" even if the "X mo" is equal or greater than 6 mo.

    Hope you could suggest a better formula. Thank you in advance.

    • Hello Mods!
      The information presented to you is not enough to give you advice. What format is “X yr, X mo” written in? Text or date? Please specify what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  98. Amit says:

    Hi Alexander,
    I am not able to formulate given below conditions with IF statements. My query is associated with number of questions, which I need to bind with time. I have no idea how to do formulate with IF conditions.

    Condition-1:
    If I type (greater than 10 questions but less than 16 questions) in 30 minutes then I will be getting 5 marks.

    Condition-2:
    If I type (greater than 5 questions but less than 10 questions) in 20 minutes then I will be getting 3 marks.

    Condition-3:
    If I type (greater than 2 questions but less than 5 questions) in 10 minutes then I will be getting 1 marks.

  99. Luis says:

    Hi Alexander,
    I'm trying to write a countif formula to only count the "PO-B" positions I have for my department and exclude the open ones or if they don't contain "PO-B".
    DATA
    PO-B-1 - Eisenhuth, Rebecca
    PO-B-17 - OPEN
    M-B-1 - O'Banion, Ruth Ann
    QC-B-1 - Thioune, Omar

    Thanks in advance

  100. Rizawandri says:

    well i can't change some number like 1-1.000.000.000.000,00 into a text like:
    234.567.891 into (Dua Ratus Tiga Puluh Empat Juta Lima Ratus Enam Puluh Tujuh Ribu Delapan Ratus Sembilan Puluh Satu - indonesian) or (Two Hundred thirty Four million Five hundred Sixty Seven Eight Hunred Ninety One - english).
    will someone help me?

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)