Nov
26

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 the IF function 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")

    If you don't want your IF formula 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 if 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")

    If you put a comma after the value_if_true argument, your IF function will returns 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

    If you want 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. If you want your IF formula to return TRUE and FALSE as the logical values (Boolean) 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 to "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. Get IF to perform a math operation and return a result

    Instead of returning certain values, you can make 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.

IF function examples 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 IF formula for text values 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. IF formula for text values 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 functions 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 to 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:

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

  1. Anonymous says:

    =IF(A2=5, "OK", "Wrong number")
    BUT I WANT TO VALUE OF A1 CELL AT "Wrong number" POSITION

    • Hi!

      If my understanding is correct, you simply need to replace "Wrong number" with A1 like this:
      =IF(A2=5, "OK", A1)

      The formula returns "OK" if A2 is equal to 5, otherwise it picks a value from cell A1.

      If you want something different, please clarify.

      • Padraic says:

        Hi, I want to enter a formula that will give me a result only if there is a value > 0 in a row

        i.e. if A1=0 then give me B1. But if B1=0 then give me B2 etc.

        Can anyone help? I've got this so far, but it just gives me 0:
        =IF(R2=0,S2,IF(S2=0,T2))

        • sachin says:

          =IF(OR(A1=0,B1,B1=0),B2) u try this.

          • sachin says:

            =IF(OR(A1=0,B1),IF(B1=0,B2))

            • Kelz says:

              I have 2 cells within Excel that I would like to apply the if function to, but I am not sure how to setup it up properly. Right now it reads:

              =IF(T11=R13,"Ok","Check Total")

              But, I want the formula to take in account if T11 is less than or greater than R13 by "5 units or 5 increments" to indicate "OK" not just equals to. How would I setup my formula?

              Thank you in advance!

              • Kelz says:

                I have 2 cells within Excel that I would like to apply the if function to, but I am not sure how to setup it up properly. Right now it reads:

                =IF(T11=R13,"Ok","Check Total")

                But, I want the formula to take in account if T11 is less than or greater than R13 by "5 units or 5 increments" to indicate "OK" not just equals to. How would I setup my formula?

                Thank you in advance!

              • SubbaReddy says:

                Check Below Formula Once
                =IF(OR(T11R13),"5units/5increments","Ok")

              • SubbaReddy says:

                =IF(OR(T11R13),"","")

            • Taylor says:

              How do I get to extrapolate for if A#=0, B# without just typing =IF(OR(A1=0,B1),IF(A2=0,B2),IF(A3=0,B3),IF(A4=0,B4)) and on and on

        • janavi says:

          =if(L4,"Half Yearly,180,If(L4,"Quarterly",90,if(L4,"Yearly",365)))

        • Ali Khan says:

          I work at a school, and i manage the book shop over there, so i want to keep my record straight for my boss, i forget things so fast :) and also my purchase record, thats why i want to set a formula to fastly enter. i deal with notebooks, diaries and artpads, so I want a formula for text and numbers both, for example if i write 'D' in a cell, the formula will write the amount of '55' for a Diary, and so on for notebook, i mean if i write 'N' for notebook, the formula will write the amount '50' for me. I just want to set that formula. So i can deal with other customers also.

        • Atif Sheikh says:

          =IF(OR(A1=0,B10),B1,B2)

          This is your solution mate :)
          enjoy it

          • D says:

            How can I get a sum on page 1 cell D23 of numerical value from page 2 cells in column D if equal to specific text in column J?

      • Nora says:

        if only you'd be my professor

      • AJAY ARORA says:

        IF A2's value = O then take date from B2 And IF A2's value = D then take date from C2 And IF A2's value = K then take date from D2

      • Richard Cavalieri says:

        =IF((Data!$D$11="Concrete"),SUM((SUM($B$40*9)/1.3)*0.865/Data!$B$26),SUM((SUM($B$40*9)/1.3)/Data!$B$26))

        I am trying to use the above if statement to calculate a value based on the value selected in Data!D11. The choices in a drop down D11 are Concrete or Frame. My logic being if D11=Concrete then do the first formula but if false (Frame) do the second formula. When Concrete is put into D11 this works correctly but if Frame is selected the value is not correct. I can put the second false formula in another cell and it calculates correctly. Can someone tell me how to fix this.

        Rick

  2. Chandalynn Peterson says:

    I am needing the cell in sheet two to = a cell in sheet one. it is a date. But if there is not a date in sheet one,yet, I want the cell in sheet two to show as blank/empty and then when the date is put in the cell in sheet one i want it to show the date in sheet two.

    can you please help?

  3. Shannon says:

    I'm trying to figure out a formula that will compare two columns "dates" and then produce the greater value of the two columns in the third column. Can you please help?

    • Hi Shannon,

      Assuming that you have dates in columns A and B, the formula is as follows:
      =IF(A1>B1, A1, B1)

      • Naveen says:

        Hi,

        I Have a Data My software needs to work 99.99% availability when measured on Monthly basis If the downtime exceeds 60 min in a month for every 30 min Penalty should be calculated on Quarterly Payment of rs 10000 can u design a methodology with formula with calculated output.

      • Dale says:

        Hi,

        I read above that excel doesn't recognize dates unless wrapped in DATEVALUE.

        I had the above formula that you gave and it doesn't always work. In my case one of my columns could possibly have a 0 or be blank.

        My formula is in column A my date columns are D and G. So I had

        =IF(G2>D2, G2,D2)

        G is the column that sometimes could have a 0 or be blank (the data is pulled from another system.

        Also, my formula doesn't recalculate once I update the data in column G. Any thoughts? I have auto calc turned on.

  4. Gene says:

    Could you help with this formula?
    "J21" = "%" and "M21" = "%" "K18" is the result but the are 3 answer.
    J21 is > than 0
    M21 is > than 0
    J21 & M21 are > than 0 And needs to be
    / by 2
    Hope you follow what I am looking for
    Thank You

    • Hi Gene,

      I understand that you have some percentages in cells J21 and M21, but all the rest is befogged.

      Can you please try to formulate the task in the following way?

      If J21 >0, then we do what?
      If M21 >0, then we do what?
      If J21 and M21 >0, then we do what?

      • Anonymous says:

        If J21%>0 then put % in K18 as that %
        If M21%>0 then put % in. K18 as that %
        If M21 & J21 %>0 then devied by 2 then put
        That % in K18
        Only 1 of the 3 formula's can be true at a
        Time and it's % in put in K18
        Hope this is better for you to see what I want
        Thank you

        • Hi!

          It's not quite clear which value shall be divided by 2 if both J21 and M21 are greater than 0. The following formula divides the sum of values in the above mentioned cells:

          =IF(AND(J21>0, M21>0), (J21+M21)/2, IF(J21>0, J21, IF(M21>0, M21,"")))

          If you are looking for something else, then replace (J21+M21)/2 in the formula with the right value.

          • vivek kumar says:

            Svetlana i am having a data which is update by team daily.
            Now i want 3 things to get from that data
            1:updated
            2:Missed to Update
            3:Coming soon

            • Hi Vivek,

              Excel formulas are not able to respond to a data change. Probably this can be done by using a VBA macro.

              • Lee says:

                Hi,

                Is this function just for one by one ? how if i want all of the data from one column like i want the data of column B with more than 1000 will go for high, the range 500-999 will go for medium and range 1-500 to low. Do i need to copy the formula and paste it one by one or it can help me comes out everything in one time, i have 19k data from that column

        • Amit says:

          As I understand, it should be like this

          in cell(k18)=if(and(j21>0,m21>0),(j21+m21)/2,0)

    • pooja gupta says:

      in col write a formula to give rank to student based on below table(without using if condition)

      marks:=33 but less than 60,>=60 but less than 70,>=70 but less than 80,>=80 but less than 90,
      >=90

      grades:= <33,"Fail",<60,"pass",<70,"3rd div",<80,"2nd div",90,"distinction

      plz solve this question

  5. pavan says:

    Can you please look into the formule; where I am doing wrong...

    =IF(SUM(T7:AE7)=N7, "GOOD", "Check again")

    This formula is returning 'Check again' though the sum(T7:AE7)=N7

    Regards,

    Pavan

  6. pavan says:

    Thanks for your reply.
    I will try again.

    Thanks

  7. Rashedul says:

    Can you please look into the formula; where I am doing wrong...
    IF(A1,B1,C1>=85%,"Tk 300",IF(A1,B1>=85%,C1=85%,B1=85%,A1=85%,B1,C1=85%,A1,C1=85%,A1,B1<85%,"Tk 100","0")))))))

  8. Bob says:

    How about: =IF(SUM(T7:AE7)=N7, 0, F5*H8). Is there an excel command to make the calculation in the second value work?

  9. Jay says:

    i have 2 sheets, i want have the value 1 inputted on sheet two when/if it matches the date from sheet one. Basically i have set the work schedule up so that when an employee is put in a work spot, it adds 1 on the second sheet indicating the employee has work on that date. it's a daily schedule, so i am trying to automate the two sheets. so when i put someone to work it auto populates under the correct date on both sheets. if the are not working it should show 0
    this is what i have so far, but not working...just shows false
    =SUM(SUM(C2:C2)=1,IF('New daily - Table 1 - Guelph'!D6,D6)='Days worked test draft'!O1,O1) ="1"
    Column C2 shows if a person works or not, then the row to the right are the dates for the month

    any help would be greatly appreciated

  10. Stephen Lawson says:

    Hi Dome help with a formula

    I have a weight of 450 in cell a1, i need a if formula to say if a1 greater than 470.1 but less than 500 then pick up value in cell a2 on this occasion -.3.

    can you help

    thank you

  11. Summy says:

    Hi Svelana !!

    I have two excel sheet containing a list of peoples, but there is some error e.g. font, spelling etc.

    Pls help me to find the code of peoples from one sheet to other.

  12. Fo says:

    Hi
    I want to put in an IF function that returns different text depending of the numerical value of the cell next to it. e.g. if the number is less than 43 - "poor", 43-50 "average", 51-70 "above average" etc....
    I know I have to set up some cells with the lower value of each range and the text next to each one (pivot table?) but I can't remember the formula for saying - if it is less that A1 then print "textA" etc..

  13. Sami says:

    This was very informative, can you help with this formula please?

    If Cells in E:E = value "YES" AND Cells in F:F date older than 6 months then calculate result of Cell in B:B - (minus) a number "6"

    • Hi Sami,

      Excel cannot understand the "month duration" because the number of days in each month varies. So, here is the formula as per your conditions, except that dates in column F are older than 180 days rather than 6 months:

      =IF(AND(E1="yes", TODAY()-F1>180), B1-6, "")

      • Sami says:

        Thank you for the formula, It did not work, I have the date sat with date value but the input just blank... if I leave F1 blank I get the results I need but, F1 "date" is important.

  14. chris says:

    Hi I am looking for a forumlar to completed the following calculation in a cell:
    If A1 > 200,000 cell = A1-200000 but if A1>1,000,000 cell = 800,000
    Please can you help me out with this formula

  15. Gene Cole says:

    Could you help with this project?
    If J21's % is > then 0 then put that % in K18
    If M21's % is > then 0 then put that % in K18
    But if J21 & M21 %'s are > then 0 then devied
    By 2 then put the result in K18 as a %

    Only 1 of the 3 formula's can be true at
    a time
    Thank you

  16. Gene Cole says:

    J21's % & M21's % are added together then devide by 2 to get a % that can be used
    The this help

  17. Zach says:

    I need to make a an if formula that will tell me yes if something matches but then also has a text and amount for if the match is not correct. So I need a =if(A1=B1,"Yes" and then if A1 doesn't = B1 for the formula to say "Difference noted of (A1-B1)" to say how much the difference is while also having text in the same false side of the if function.

    Can you help me?

  18. Gene Cole says:

    That is great thank you Svetlana just what I needed

  19. Tahsa says:

    i need help with this formula

    "if d4 equal 0 display 0, if d4 is greater than 0 but less than 9 display 1"

  20. Sheldon says:

    I need an If formula that will provide me with a value that is deterimed by the data validations that are selected contain different text selections:

    for example: If cell D2 that has been selected from a data validation that has the text "No Show" or the text "Cancelled" then I want cell G2 to enter a value of 50 ... if it has any other text, then enter the value as 200 in cell G2

    Any way of doing this?

    Thanks

  21. Ganaa says:

    Hello,

    I desperately need help on below case. I am combining two reports, on sheets 12 and 13. With below formula I should be seeing "Required" in the cell because '12'!AU19 is not blank nor zero. Other cells with the same circumstances shows "Required" with no problem. When I evaluate this formula on '12'!AU190 it will actually say that it contains a constant.

    =IF('12'!CQ190,'12'!CQ19,IFERROR(INDEX('13'!$A$1:$ABG$1466,MATCH('Draft (2)'!$A19,'13'!$A:$A,0),MATCH('Draft (2)'!AU$1,'13'!$1:$1,0)),IF('12'!AU190,"Required","")))

    If you could help me on this it would be great. Thank you.

  22. Ganaa says:

    sorry, the last bit looks like this. IF('12'!AU190,"Required","")))

  23. Ganaa says:

    somehow I cannot put does not equal, therefore I will put it in text, in the last bit, the argument is AU19 does not equal zero.

  24. Rawle says:

    Can you help me with this:
    IF(D4="DSSM", "B4-210"), IF(D4="DSSM", "B4-180")

  25. Ticia says:

    Plead help, I need a formula to do the following:
    If A1=Y then both B1 and C1 should either be N or blank, or IF B1=Y then both A1 and C1 should either be N or blank, or IF C1=Y then both A1 and B1 should either be N or blank.
    What I mean is there should only be one Y in any of the cells A1,A2 and A3. Otherwise display error message if there is more than one Y.

    • Hi Ticia,

      The following formula displays "error" if there is more than one Y in any of the cells A1, B1 and C1, it returns an empty string in all other cases.

      =IF(AND(A1="Y", B1<>"Y", C1<>"Y"), "", IF(AND(A1<>"Y", B1="Y", C1<>"Y"), "", IF(AND(A1<>"Y", B1<>"Y", C1="Y"), "", "error")))

      Hopefully this is what you are looking for.

      • Ticia says:

        Hi Svetlana,

        Thanks for your prompt response. The formula does not seen to work, probably because of where I have placed it. Where can I put the formula to make it work? I placed the formula in data validation -> settings -> allow -> custom -> formula.

  26. TanMan says:

    looking for some assistance on a formula.

    I have the number 5 in cell a3

    I need in cell b3 if a3 is > 6.5 then I need a letter "c" inputted / if a3 is < 4.5 then I need a letter "a" inputted / and if the a3 is between 4.5 and 6.5 then I need a letter "b" inputted in cell b3

    I tried to do all of this and correcting the errors don't make sense
    any help would be appreciated

  27. Hi TanMan,

    Try this one:

    =IF(A3>6.5, "c", IF(A3<4.5, "a", "b"))

    • Daniela says:

      Hi, what if I want to do the opposite:

      =IF('Enter Scores'!B7="D", "3",IF('Enter Scores'!B7="B","5.10",IF('Enter Scores'!B7="C","4.50",IF('Enter Scores'!B7="E","1.50",IF('Enter Scores'!B7="F","0",IF('Enter Scores'!B7="A,"6"))))))

      Basically each letter has a number value, if A is entered to B7 on another sheet then the number 6 should show up on the current sheet in the appropriate cell.

      I've added this function to a number of cells and I can't use a =SUM(B6:B12) function because the numbers aren't really 'numbers' according to excel.

      I've formated the cells to recognise the number; but I can't seem to get them to add up; I only get a total of 0.00

      MAybe I need to use another function entirely

  28. Andrew Rose says:

    Please can you help me with this 1

    If a range of cells (A1:A100) have another cell's data(B1) I want it to return the amount of times that reoccurs into cell C1

    I need to be able to repeat the formula down C column for adding up different data in B1, B2, B3, etc column

    Any Help much appriciated!

    • Andrew Rose says:

      Column A is lots of different part numbers that repeat sometimes (purchases)
      Column B is the part numbers aranged in numerical order (stock list)and one of each
      Column C is the total of times it was Purchased
      Hope that is clear enough!

  29. Andrew Rose says:

    I Love You! spot on

  30. Blas says:

    Hi,
    I need to modify the following formula so that when cells B2 or C2 are blank then the output cell is also blank.

    =IF(OR(C2-B2>30,C2>DATEVALUE("12/13/14")),"No","Yes")

    Currently it defaults to "Yes" when they are blank. Can you help me with this? Thank you.

  31. LC3 says:

    i will try my best to explain......let's say we have 1,500 users in c25
    Heading:

    Login's per Month............................Fee
    e25 = 1-1999 ..........................f25 = $10.00
    e26 = 2,000 - 4,999....................f26 = $6.00
    e27 = 5,000 - 9,999....................f27 = $4.00

    if c25 is between 1 and 1999 logins how do we make c28 = f25 or $10.00
    if c25 is between 2,000 and 4,999 logins how do we make c28 = f26 or $6.00
    if c25 is between 5,000 and 9,999 logins how do we make c28 = f27 or $4.00

    We would like to plug the number into c25 and then c28 will change to what ever the fee is.

    thank you soooooooo much

    LC3

  32. LC3 says:

    is there a way to concatenate more than 3 IF functions

  33. Janna says:

    I am trying to come up with an IF statement that put a name only if the column to the left of it is not blank. However, there are formulas in the columns to the left that make some of them look blank, even though the actual cell is not blank. What formula can I use to return a blank if the cell has a formula, but is blank?
    I started with this, but I don't know how to exclude the formula.
    =IF(ISBLANK(B8)," ","Clarence")

  34. lefteris says:

    hi i am not sure if i will explain it correct but i need help i need a formula to say if c2>d2 then true 3 false 0 and if c2=d2 then true 1 false 0

    thanks very much

  35. Kumar says:

    I have following formula but it writes the result as only A even condition not satisfied

    =IF(F4="","",IF(ISERROR(VLOOKUP($D$4,Sheet5!D:D,4,0)),"A","B"))

    Please Help me

  36. Amanda says:

    Hi, Svetlana,

    I'm a bit stuck on finding a formula that will suit my needs. My spreadsheet has column A: first name; column B: last name; column C: type of training; column D: date of training.

    I need a formula that will look at column A, B, and C and if they match any other row in column A, B, and C I need it to select the most recent date.

    this will remove all duplicate training and keep only their most current training record.

    Any suggestions? i'm stumped on this one.

  37. jaisask says:

    A B C D
    1 a:5 b:4 a:26 b:3
    2 a:4 b:45 a:6 b:18
    3 M3 a:50 b:40 a:26 b:30

    how to use IF function on above data. i want to print:

    1) let's say, if the number after the colon [:] equal or more than 5, then just print "a" or "b"
    2)if the number after the colon [:] is less than 5 then print "X".

  38. shabtom says:

    if a1>20 and b1> 50 add a1+b1 if if a1<20 and b1< 50 do nothing

    please i need help on that

  39. Dr. Tayyaba Shaikh says:

    I want to calculate age between 0-14, 15-24, 25-59 and 60+ from single age registered over the months in input sheet to analysis sheet. I want to know the formula;

    the logic is if a person of 24 age is registered in January, 2014 then it should be counted in 14-24 age group in analysis sheet.

    Thanks

    • You can calculate it using the COUNTIF function.

      Here are the formulas to count age 14 – 24.

      If dates are entered as text, e.g. " January, 2014":
      = COUNTIFS($A$2:$A$100, "<=24", $A$2:$A$100, ">=14", $B$2:$B$100, "January, 2014")

      If the date is formatted like this 1/10/2014, please use this formula:
      =COUNTIFS($A$2:$A$100, "<=24", $A$2:$A$100, ">=14", $B$2:$B$100, "<=1/31/2014", $B$2:$B$100, ">=1/11/2014")

  40. Jen Watters says:

    In a specific cell I am wanting it to tell me If a Response is LESS than 28 days old it is Outstanding & If it is MORE than 28 days old it is Overdue (this I can do) BUT I also want it to tell me if another cell on the same sheet contains 'Yes' it overides the previous command and that cell needs to read Complete but if that cell is blank it will ignore the rule. At the minute I have this:- =IF((E3-B7)>28,"Overdue","Outstanding"),IF(M9="Yes","Complete","0")

  41. chacon says:

    Hi, I want to enter a formula for the following situation:
    If the cell contains specific text, then calculate x-y, otherwise x
    I was trying if(B1="text",A1-D1,A1) but it is not working

  42. Tarak says:

    Thanks...I got the solution................

  43. Jai says:

    Hi,

    I need to check the over all strength based on five cell values where each cells have different conditions to meet. It all pass the condition i would say 100%, if any four condition pass, then 75%, similarly for 1 & 2 & 3pass condition. if all fails it is 0%.

    For all pass my condition is If(and(a1>=n,a2="Yes", a3<=y, a4z, a5>=100%), 100%, ).

    I need to know how to get number of pass criteria and based on that allocating the strength percentage. Is it easy to nested if or need to any other function or method.

    Please Suggest.

    Regards,
    Jai

    • Hi Jai,

      If the Pass criteria can be used in any order, e.g. 1st, 3rd and 5th, then you'd better use the CHOOSE function:

      =CHOOSE((IF(A1>4,1,0)+IF(A2="Yes",1,0)+IF(A3<3,1,0)+IF(A4>=6,1,0)+IF(A5=100%,1,0)),0%,20%,40%,50%,75%,100%)

      Just change the conditions for each cell as you want.

      The principle is as follows: each IF statement returns either 1 or 0 depending whether the conditions are met or not. The sum of the IFs' results varies from 0 to 5.

      The CHOOSE function returns 0%,20%,40%,50%,75%,100% (you can also modify this part if needed) according to the sum of the IF functions' results.

  44. Bel says:

    Hi I want to include a value from a list in my IF formula.

    =IF(A18 ="Other" ), D18, (SUM((D18+E18)*F18))

    so basically if the value from the list in cell A18 = the word Other (that's a value in the List) I want to display the value in cell D18 if Other is not displayed in A18 then I want the Sum calculation to be done.

    Is this possible?

  45. NADEEM says:

    Svetlana Cheusheva,how can u do, i tell u when i come back..........soonnnnnnn

  46. giridhar says:

    I want a function which will solve my query as mentioned below.

    A B C D E
    1 71501 giri 1
    1 76597 sekhar 1
    1 71503 satish 1

    I want value of column C in column E If value of column A and particular value of column B match with value in column D

    for example my requirement is column E = "sekhar"

    please help.

  47. Amanda says:

    Hi, Svetlana,

    I'm a bit stuck on finding a formula that will suit my needs. My spreadsheet has column A: first name; column B: last name; column C: type of training; column D: date of training.

    I need a formula that will look at column A, B, and C and if they match any other row in column A, B, and C I need it to select the most recent date.

    this will remove all duplicate training and keep only their most current training record.

    Any suggestions? i'm stumped on this one.

    is this something you can help me with? i'd really appreciate it. I'm in a time crunch and I have no one else to ask for help.

    • Hi Amanda,

      You can simply sort your table by column D (most recent dates will be at the top), and then use the standard Excel Remove Duplicates tool to find duplicates in columns A, B, C and delete them.

      If it is not the case, then send us a small sample workbook with your data at support@ablebits.com including the expected result, and our support team will try to help.

      • Amanda says:

        Svetlana,

        I'm trying to avoid having to do this manually as the workbook is very large and will continue to get larger. I have emailed you a copy of the workbook as well as what i'm looking for.

        Thank you,

        Amanda

  48. William says:

    I have a cell that contains: Check # 0000

    In other words, Check # 0000 - 9999

    Can you recommend an If Formula. I have tried

    =IF(A1="Deposit","Deposit",IF(A1="Check","Check"))

    This works only if I have Check in the cell but with the number sign and numbers it returns a false value. Does anyone have a solution?

  49. sreedhar says:

    Case Qty (CQ) = 20
    Max Level (MQ) = 18
    Min Level (MiQ) = 8
    Quantity on Hand (QOH) = 5
    Reorder Level (ROL) = If(QOH<=MiQ,MQ-MiQ,0)
    Reorder Quantity (ROQ) = ?

    I want to have ROQ as.... If ROL is Zero then Zero, If ROL is Less than 60% of CQ then CQ, if ROL is greater than 60% of CQ then zero. Pl advice.

  50. eric says:

    hi,

    i have this check list with a checkbox, and i need to know how i can make the question status cell to display a text(done, pending) while changing the cell color (red to green),when i select one of the checkbox answer.

    ex. do you have a list of traces on PCB? []yes []no []n/a [question status]

  51. Trish says:

    Hi!

    I am trying to create a formula which will tell me when two dates in two separate columns do not match. Then if they do not match, what the earliest of the two dates is. Many cells in the second column are blank.

    something like =IF(J2=K2,"Yes","No"&J2-K2)

    But it only gives me #VALUE

    • Revan says:

      Create a 2 columns, one after each current date column. Convert the columns to a number, not a date. Divide one column by the other. If 1/1 = 1, then all rows with a value of 1 have no difference. If the value isn't one, there is a difference. I'm sure there's a formula, but this little trick works great! I call them "divchecks".

  52. Tahsa says:

    i am trying to write a formula. if d7 contains the word late display 1.

  53. John Rischmiller says:

    Hi,

    I am trying to create a purchase order that is tied into my existing estimating sheet. I would like the items that are selected in my estimating sheet to transfer over to the Purchase Order Sheet. The IF function that I am looking for will not only calculate $'s but also text in the same line. Is there an easy function that would work out for this.

    This is the example that I can add for your reference.

    (2) Work Sheets one labeled Estimating Sheet the other is Labeled Purchase Order.
    (4) Cells Needed Per Line which are: Quantity, Description, Unit Price, and Total.

    Please let me know if there is any thing more that you may need!

    Thanks!

  54. Vasavi says:

    Hi,

    can you help me with this formula?

    Currently it shows TRUE when I use this formula =RIGHT(A1,8)=D1 want to add below conditions as well accordingly it should show TRUE or FALSE.

    If A1 is not blank it should go for =RIGHT(A1,8) = D1 return values should be TRUE
    If A1 is blank D1 may be 123 or 456 when d1 falls in 123 or 456 return value should be TRUE when the condition falls out of this scope return values should be FALSE.

    Please help me.

    Thanks

  55. Bhanu says:

    Hi,can you get me a if condition to display the value as 0 with out disturbing the present formula = if(R12>15000,15000*12/100,R12*12/100)

  56. Robert says:

    I don't know VBA code. Just a little familiar with basic formulas. I need a simple condition formula that when specific data is entered by user in a cell of one worksheet it will open other worksheets within the workbook. Greatly appreciate any help.

  57. Abner says:

    i need to add today's date on one cell if i write a number o letter on another cell some like this. =if(F3=text, "(today()") but i can't make it work.

  58. Indra says:

    Hi can you help me with this formula, I want to create an "IF" Formula

    example :
    B C D E
    FinDate AgreeDate ActDate Delay Formula
    10 31.08.2013 22.08.2014 -356 C10-E10
    11 21.09.2013 10.03.2014 13.03.2014 -3 D11-E11
    12 30.10.2013 15.04.2015 76 D12-TODAY()
    13 30.10.2013 -456 C13-TODAY()

    Everything start with act date if blank, check Agree date, then check Fin Date. Final Result is delay ... ( Days )

    Thanks

  59. martin says:

    Hello,

    I need a formula to see if the same name is contained in each column

    for example a1 - z1 contains the word accounts receivable
    a2 - z2 the word cash
    a3 - z3 the word land

    and etc.

    Any help would be greatly appreciated.

  60. Heather says:

    Hi,

    I am looking to match data points along each row in a worksheet.

    One column contains values (0-3)

    The other column contains values (0-1)

    And the last column contains values (1-7)

    I want to find a formula that will find out how many times each of these scenarios happen:
    -Column one is 3/Column 2 is 0/Column 3 is 1 or 2
    -Column one is 2/Column 2 is 0/Column 3 is 3 or 4
    -Column one is 1/ Column 2 is 1/Column 3 is 4 or 5
    -Column one is 0/ Column 2 is 0/ Column 3 is 5, 6, or 7

    Thanks!

  61. brian says:

    i have a table basically column A date, column B cash, column c Check. I need to look at column B & C in same row and sum all based on whether column A's year text only, like 2015.so it would give me a total of all payouts in 2015 only. the date column is formatted 1-jul-2015 with multiple years being listed.

    date cash check
    1-jul-2015 50.00 0
    1/jan-2014 0 100
    1-feb-2105 0 75

    total for 2015 should be 125.00

  62. Cécile says:

    Regarding Example 3. "IF formula for text values with partial match."
    I am looking to get the same result but for text values in a RANGE of cells (not just one cell) with partial match.
    So if in a range of cells, containing several names in each cell, the name "Brigitte" appears, than I want excel to return an X, if not than I want nothing returned.
    I have been looking an dlooking and haven't found the solution so far.
    Thanks a lot in advance for your help.

  63. Cécile says:

    I tried this: but it doesn't work: =IF(ISNUMBER(SEARCH("Brigitte",'FY15 Germany & Benelux Projects'!K2:O2)),"X","")

  64. LOTRUK says:

    I'm need to get the following outcomes for multiple scenerios. Here's and example:

    If E2=1 then G2="NIC"
    If E2=2 then G2="CCM"
    If E2=3 then G2="HP/DP"

    I want to copy this formula throught a farily large spreadsheet.

  65. Zie says:

    Hi Svetlana, do you have a formula for words of which the column 3 is the final identifier from the given information from columns 1 and 2:
    Column 1 Column 2 Column 3
    Common Stock Common Stock = Common Stock
    Preferred Stock Preferred Stock = Preferred Stock
    Rights Common Stock = Common Stock
    Rights Rights = Rights
    Preferred Stocks Rights = Rights

  66. Adolfo says:

    Hi,

    Is it possible to get an if result that shows, in a single result, text and a math result?

    =IF(D20<100%,"Dropout to be recovered"(SUM(D18-D19)),"Over target")

    After "Dropout to be recovered" I would like to show the amount to be recovered.

    Thanks.

    • Hi Adolfo,

      Sure, just use the concatenation operator (&) - "Dropout to be recovered "&SUM()

      If you want the text and number to be separated with a space, type a space before the closing quote, like in the example above.

  67. Mark says:

    Hi,
    I've beentrying to fix this sheet i'm working on for a day now. i hope you can help me. what i'm trying to do is if my team will put their name choosing from a drop down list from A1, it should automatically put their employee ID in B2.

    please help...

    Thanks..

    • Prasenjit Kar says:

      Hi Mark,

      Assume that you have master base data where you have all the info about emp name and ID. So from there you can create a employee list by the data validation rule and then put it in A1..then at B1 put this vlookup

      formula = Vlookup(A1,Base data range,Column index no,0)...

      So when you will put their name choosing from a drop down list from A1, it should automatically put their employee ID in B2.

  68. Jack says:

    Hello, i am trying to evaluate between five different columns, in which one of the four columns has a date and two or three of the following columns may or may not have a value. I have tried Isblank but don't get the results. eg: subtract b-a, if b blank then use c-a, if b and c blank then d-a, if b,c,e blank then e-a.

    • Hi Jack,

      Try this one:

      =IF(AND(B2="", C2="", D2=""), E2-A2, IF(AND(B2="", C2=""), D2-A2, IF(B2="", C2-A2, B2-A2)))

    • Lijina Joseph says:

      Hey Svetlana

      I am dealing with 10000 calls and every call has a time (like 13:56:40)
      but I want to count only calls from 8:00:00-17:00:00

      I am using =IF(AND(B433>=08:00:00, B433=08:00:00, B433<=17:00:00), "1", "0")

      13:36:06 1

      18:56:07 0

      14:32:26 1

      I want it like this but I am not sure what is the mistake with my formula

  69. Williams says:

    I need a formula that will allow me to transfer dates from one page to another.
    The first page will be in date format but I need it to correlate on the second page in dated columns as a single X or check-mark.

    I would appreciate your assistance.

    Thank you,

  70. Bruce says:

    I'm working with a database the puts in new orders at the end of the spread sheet. What I'm trying to do is only bring the "new" entries into another sheet. I've made an identifer of using "1" for new and "0" for old. How would I only display the "1" entries on the workbook? Any help would be greatly apperciated.

  71. Kimberly says:

    How do you use an If function to count how many characters there are in a cell.
    The question is to ask if there are less than four characters in a certain cell?

  72. Steve says:

    I have a table with different tasks and Price of that task. I want to be able to put in a task code and get have a cell read "direct" or "indirect". Would if function work best?

  73. J.B. says:

    Hi,

    I am doing a project and I need to use the IF function in cell B14 to see if the average in B10 is > the value of each individual average in cells C10,D10,E10, and F10. I tried =IF(B10>C10:F10,"Yes!","No") and =IF(B10>C10,D10,E10,F10,"Yes!","No") both aren't working.

  74. Gracie says:

    Hi,

    I was wondering if you could help me. I am trying to format a worksheet where if there is something (in my case columns H, J, L, and N all contain dates) in column H and no value in column I for that same row, the row text turns a different color. Then the same for J to K, L to M, and N to O. Is there an easy way to do this? Maybe I am overthinking? If it's not possible to change the row text color, then just the relative column with the date (H, J, L, N). Thanks!

  75. vijay says:

    hi,

    I need a help in IF formula.
    I have a sheet column AA8 TO AA132 with different due dates, AB column has status as ND,F,WP. I want to change the ND to blank if due date is today.

    thanks

    • vijay says:

      hi,

      could you please help me on this formula
      =IF(AA8=TODAY(), IF(AA8= "ND", ""))
      if ND with due date works fine, but any other words LIKE F, WP,CO ETC RESULT SHOWS FALSE

      THANKS
      VJ

      • Hello Vijay,

        It is difficult to recommend you anything without seeing your data. If you can post your sample workbooks and the result you want to achieve on our forums, our support team will try to help.

        • Azeem says:

          I want to highlight one cell of any column When we move the cursor anywhere in the excel sheet. when we will move the cursor up & down then highlight position also move in column only.

          ex-
          let we choose B column and we work in A,C,D column.
          now if we will work in A1 or C1 or D1 cell then B1 cell should be highlight.
          if we will work in A3 or C3 or D3 column then D3 should be highlight.

          plz give the solution.

  76. Dave Edwards says:

    Hi, I am creating a spreadsheet to analyse UK horse racing. There are 60 courses under the "Course List" column. In the "Course" column I have listed the meetings taking place each day for the next 12 months, 1175 in total. I am using the following formula in the "Total Meetings" column to extract the number of meetings for each racecourse during the next 12 months
    Total Meetings:
    =COUNTIF($B$2:$B1176,$D2)

    I would now like to add another column which shows the number of meetings remaining after todays races have completed. e.g. if there are 25 meetings in total for a race course, each time a meeting is completed reduce the number of meetings remaining by 1 and display in "Remaining" column next to each course in the "Course List"

    RACE DATE COURSE Course List Total Meetings Remaining
    1-Feb-15 MUSSELBURGH AINTREE 21
    MUSSELBURGH CHELMSFORD CITY 18
    2-Feb-15 PUNCHESTOWN LINGFIELD PARK 54
    WOLVERHAMPTON SOUTHWELL 60
    KEMPTON PARK CHELMSFORD CITY 55
    ASCOT AYR 12
    BANGOR-ON-DEE 22
    MUSSELBURGH 7

    Any suggestions please?

    Thanks

  77. Amber says:

    i trying to put a formula which searches a specific word from the excell cell string and if ts there then then type "True" or false accordingly.

  78. RAJA says:

    HOW TO USE ROUND OFF COMMENT WITH SUM

  79. ERIC says:

    hi,

    i have this check list with a checkbox, and i need to know how i can make the question status cell to display a text(done, pending) while changing the cell color (red to green),when i select one of the checkbox answer.

    ex. do you have a list of traces on PCB? []yes []no []n/a [question status]

  80. Daniel says:

    Hello,

    Looking for an if statement which may not exist. At work we run an SQL that reports locations of specific items. The location field is always xxx-xxx-xx. For example 224-111-03 would be aisle 224, 111th spot, 3rd shelf. I am trying to figure out if I can write an if statement where if the value is between 200-000-00 and 299-999-99, then 1. Or really anyway to manipulate the "224-111-03" so I can work with the locations.

    Thanks

  81. francis says:

    Hi,

    Please i need a help, i want a formular for two dates intervals. A suming i have my start date and want to know the ending dates.
    For instance my start date of paying back loan is 08/15/2014 and i'm paying for 6 months, what will be the formular for the ending date.

    Thanks

  82. Imran Khan says:

    Hello, Please help me I need following formula.
    I need formula of column C and D
    C = matching part part will be colored.
    D = Matched or not, in sequence of column B.

    problem attached for ready reference with following link.

    https://www.dropbox.com/s/xbkh51pp9n9vt28/Formula.xlsx?dl=0

    • Hi Imran,

      The following formula seems to return the results you want:

      =IF(OR(NOT(ISERR(SEARCH($A$2,B2))), NOT(ISERR(SEARCH($A$3,B2))), NOT(ISERR(SEARCH($A$4,B2)))), "matched", "not matched")

      Or even better - the array formula (remember to press CTRL + SHIFT + ENTER to enter it:

      =IF(SUM(--(NOT(ISERR(SEARCH($A$2:$A$4,B2))))), "matched", "not matched")

  83. Wass14 says:

    I want to figure out a way in column A1 to column A1000 is equal to the word "Done" then enter the current date in the coresponding column B1 to column B1000

    1) Enter Done in A1 and B1 gets automatically updated with date and time
    2) Enter In Progress in A2 and B2 get nothing updated
    3) Enter Done in A3 and B3 gets automatically updated with data and time
    4) and so on....

    Status Timestamp for Completion
    A1 Done B1 02/06/2015 5:17:33 PM
    A2 In Progress B2
    A3 In Progress B3
    A4 Done B4 02/06/2015 5:17:34 PM

    trying:

    =IF(A1:A10="Done",=(NOW())
    or
    =IF(A1:A10="Done,B1:B10=(NOW()))
    or
    ?

  84. Michelle says:

    Hi just wanna ask help on this.I'm confused on how to exactly put many functions in the formula. The legend are the following:

    "B" - 74 below
    "D" - 75-79
    "AP" - 80-84
    "P" - 85-89
    "A" - 90 up

    For example:I actually tried to enter =IF(E16<75,"B","D")=IF(E16=80<85,"D","AP")=IF(E16=.....ETC...

    pls help me...

  85. Nancy says:

    Using an IF statement, can more than one action be included if the condition is met? For example, if A1=x then (B1 = "true" and C1=y) else (B1 = "false" and C1=z)

    • Hi Nancy,

      There is no way to do this in a single formula because a formula can return the result only in a cell where it is entered.

      You can simply use two formulas:

      For B1: =IF(A1="x", true, false)
      For C1: =IF(A1="x", "y", "z")

      If you need to change a lot of cells, then a VBA macro will be a better solution.

  86. Luxmi says:

    Hi ,

    I am using nested IF for age
    =IF(M7<39,"Cohort C",IF(AND(39<=M7,M7<48),"Cohort B","Cohort A"))

    however the age i am using DATEIF formula as below :
    =DATEDIF(K7,TODAY(),"Y") & " Years, " & DATEDIF(K7,TODAY(),"YM") & " Months, " & DATEDIF(K7,TODAY(),"MD") & " Days"

    which result as i.e 35 years , 2 months, 1 days

    How can i specify the date as above format in the nested IF statement ?

    • Hi Luxmi,

      It's difficult to give any advice without seeing the real data. It will be helpful if you can give more details about the value in cell M7 and other cells the formula refers to.

      • Luxmi says:

        sorry it is actually M7 = Age . However , for this scenario the age is purely calculated by the year only.

        How if i use below formula to calculate the (M7 cell) age & use this M7 cell to do nested if statement :
        =DATEDIF(K7,TODAY(),"Y") & " Years, " & DATEDIF(K7,TODAY(),"YM") & " Months, " & DATEDIF(K7,TODAY(),"MD") & " Days"

        which result as i.e 35 years , 2 months, 1 days

        how can i specify the age using this format ?
        =IF(M7<39 years, 00 Month 00 Day,"Cohort C",IF(AND(39 Years 00 Month 00 Day<=M7,M7<48 years 00 Months 00 Days),"Cohort B","Cohort A"))

        • Alexander says:

          Hello Luxmi,

          Please specify the age in the same way as you calculate data in cell M7, i.e. using this formula:
          =IF(DATEDIF(K7,TODAY(),"Y") < 39,"Cohort C", IF(DATEDIF(K7,TODAY(),"Y") < 48,"Cohort B","Cohort A"))

  87. azn says:

    I tried using the If formula for my data but I just could not get it to work.

    Example:
    I have set a target for John and if he meet the target the cell will display the Rebate (10%) percentage, if not it will display 0%.
    Rebate: 10&
    QTD: 17,275
    Target: 12,500

    How do i go about setting the formula for this?

    Thanks so much!

  88. Zakaria says:

    Hi,

    I have successfully used on A3 cell: =IF(ISBLANK(A2),"",IF(A21,A2<=2),"IMPROVEMENT NEEDED")))

    But Can't use on A4 cell: (A1 cell is a number i.e. 10000)
    =IF(A3="Unsatisfactory",A1*100%,IF(A3="Improvement Needed",A1*105%))

    it shows:"FALSE". how to make A4: if A3 is vacant as per above formula, A4 will be A1*1

    • Alexander says:

      Hello Zakaria,

      I am sorry, your task is not quite clear. Please post a sample workbook with some variants of your data (1 sheet – 1 variant) on our forums and include the result you want to see in A4. Our support team will look into your task and try to help.

  89. lara says:

    Hi there, i am trying to figure out a formula for not leaving cells blank. For instance, if B2 has the word "debit card" the C2 cannot be left blank. If B2 contains the word "cash" then C2 can be left blank.

    Does such a formula exist?

    Many thanks
    Lara

  90. Rachel says:

    Hi, I am trying to work out a formula that says:

    Work out the average of F5:F11 IF B5:B11 contains data

    Is there any chance you could help?

    Many Thanks
    Rachel

  91. Clarice says:

    Hi there,

    I'm using a SUMIF formula to return a required number.
    Now, i need a formula to return a required text.
    if i enter name in c1 and it matches with b1 i need an answer.
    for example:
    a1 b1 c1
    rey 502 rey
    may 502 may

    hope you could help me.

    thanks,

    • Alexander says:

      Hello Clarice,

      I am sorry, your task is not quite clear. For us to be able to assist you better, please post a small sample workbook with your data and the expected result our forums. Our support team will look into your task and try to help.

  92. Patrick says:

    Hi I am struggling working this out any help would be fantastic.
    Basically I have 3 businesses I need to keep track of the mileage, I have a spreadsheet set up for that. However I want to keep individual records on the sheet for each business.
    so the business is one of three "photography" "building" "design" in cell D6 I want to take the mileage from k6 and add it to a separate cell depending on the business displaying the output at g2, g3 or g4 and I want this to run through the sheet. so it checks column d for which business then depending on the business it takes the result from column k and place the sum totals in either g2,g3, or g4

    I am at a loss to sort this. not even sure its possible. but help is appreciated please.

    • Alexander says:

      Hello Patrick,

      A formula can return the result only in a cell where it is entered. Most likely, you simply need to place three IF formulas into cells G2, G3 and G4 that will show the result or keep blank according to the specified conditions. If you want to get a sum, then you may also need to use the SUMIF function.

      If you can send us a small sample workbook with your data to support@ablebits.com, we’ll try to assist you better.

  93. Dan says:

    Hello,

    I'm having trouble making a formula that will return a date or a value. i.e. I'm making a time off request form, and sometimes the cell will have a single date (2/11/15) and sometimes it will have multiple dates (2/11/15 to 2/12/15). I can get the single date to fill by formatting my formula cell to "date," but I cannot get the range to show up. I want the exact input from the cell to show up regardless of being a date or "text" as the multiple dates would be considered.

    My formula that I'm using is =IF(A7,A7,"").

    I've tried using a variety of arguments in front of the second A7, but I still haven't gotten the results I want. I don't want to format the original cell to text if I can avoid it.

  94. Debra says:

    Hi,

    I have a master sheet and individual sheets (with the details) for a survey.

    I want to be able to populate the individual sheets with simply putting a Y or N on the master sheet. Is this possible?

    ie A clients details and their consent to receiving marketing, research & volunteering are on the master sheet. Can I then automatically populate the individual sheets from the master?

    Thanks
    Debra

    • Alexander says:

      Hello Debra,
      You can enter the formula like the one below to the cells on your individual sheets:
      =if(MasterSheet!$A$2="y", MasterSheet!$C$2, "")

      That means that if cell A2 is in the master sheet contains "Y", copy data from MasterSheet!$C$2.

  95. shabbeer says:

    Thanks for posting If Function in a very easily manner.

    can you tell me the function to extract text from cell

    Eg: Paid to Sub cont Rafiq/against balance payment.
    Paid Sub cont Shafeeque/against part payment.

    In this I want to extract only from the text sub cont to /.

    • Alexander says:

      I am sorry, your task is not very clear. For us to be able to assist you better, please post a small sample workbook with your data and the expected result on our forums. Our support team will look into your task and try to help.

  96. Aengus says:

    Hi. I want to extract a number from a cell(B2) at an exact time. I have two times on my sheet, one is the start time of an event and the other is the actual time right now. When there is a differance of 2 minutes between both times, then i want to extract the number in cell(B2) and i want the value to remain there.
    My sheet is connected to a website and the sheet is updated every second, so the value in cell B2 is constantly changing.
    Is this possible???

    • Alexander says:

      Hello Aengus,

      I am sorry, but there is no way to fulfill your task using formulas. Since you want the value to remain in the cell, a VBA macro is needed for this task. You can look for it on http://www.mrexcel.com or http://www.excelforum.com.

      • Ramnath says:

        Hi,
        I have a two workbooks 1)Masterlist and 2)Saleslist

        Master list have columns description, unit and unit price. Fixed number of rows.
        Sales list have columns description, unit, unit price, saleqty and amount. Rows increases on ever sales.

        It will be wonderful some body can help with the code to fill the cell unit price in the workbook Saleslist when I enter matching description of the item as in Masterlist.

  97. tony (can someone help me rank Test 1 according to the Class column says:

    Class Name Test 1 Rank
    year 9A Student 1 20
    year 9A Student 2 30
    year 9A Student 3 20
    year 9A Student 4 21
    year 9A Student 5 22
    year 9A Student 6 52
    year 9A Student 7 54
    year 9A Student 8 14
    year 9A Student 9 25
    year 9A Student 10 26
    year 9A Student 11 21
    year 9A Student 12 24
    year 9A Student 13 22
    year9B student 1 23
    year9B student 2 54
    year9B student 3 52
    year9B student 4 52
    year9B student 5 21
    year9B student 6 22
    year9B student 7 12
    year9B student 8 22

    • Alexander says:

      I am sorry, it is not quite clear what result you want to get. For us to be able to assist you better, please post a small sample workbook with your data and the expected result on our forums. Our support team will look into your task and try to help.

  98. Shiva says:

    Hi, i want an output with two conditions. That is if A1=I OR II OR III and if B1<=5 then the output in C1 should reflect as 'yes' otherwise it should be 'no'.
    I typed as =if(and(A1="I", A1="II", A1="III",B1<=5),"yes","no").This is not working.
    Kindly let me know the error or is this formula totally wrong? Thanks in advance.

  99. James says:

    i am trying to set up an IF function where it searches a cell for a word that has the letters UCI if true gives the result 2.2 if false gives 2.8. i have done this but doesnt seem to work :(
    =IF(ISNUMBER(SEARCH(E3="UCI")),"2.2","2.8")

    please help

  100. James says:

    Ah brilliant much appreciated seems i fell at the last hurdle.
    cheers
    James

  101. Chris says:

    I have 2 cells that contains month and year. The first cell is date when qualification was received. Second cell is date when qualification expires. The 3rd cell below these 2 cells I want to say "Expired", "1 Month Left", "3 Months Left" based on these dates.

    Please help me.

    Thank you very very very much!!!

  102. SOFIA says:

    HELLO,
    i need a formula to show that if my first cell is greater than the average then to show me 0 otherwise to show the difference between the first cell and the average(some cells are 0) ,so i was trying =IF(D3>D$291;TRUE=0;FALSE=D3-D291) but doesn't work .THANK YOU IN ADVANCE!!!

  103. zarif says:

    i want to check if the data in one cell is contained in any other or not. what is the code??

  104. Aengus says:

    Hi. I have a word in cell A62. I want to search the range A5:A45 to see if it contains my word. If it finds the word, then i want to highlight that row and then insert a formalua, =IF(B41="a","yes","no") in the Q coloum of that row.
    Is this possible?

  105. Lokesh says:

    Using IF function write a formula if " Date" is Blank pick the Value from "System number", else name it as Date In.

    data
    system date
    d322 16/2/2015
    f3532 17/3/2015

  106. Carlos says:

    Hello Svetlana.
    Great Work.
    I need to find a two pairs of value in a cell, A/B OR C/D.
    A = 5 [value in cell C31]
    B = 5 [value in cell C31]
    C = 8 [value in cell C32]
    D = 8 [value in cell C32]
    blank cell, give me nothing.

    How can i do to find A or B ( same with C or D)

    =IF(C33="A OR B";(C$31);IF(C33="C OR D";(C$32);""))

    also one question, how can i make a addiction of various IF conditions? Like:

    =IF(C33="A OR B";(C$31);IF(C33="C OR D";(C$32);"")) + =IF(D33="A OR B";(D$31);IF(D33="C OR D";(D$32);"")) + =IF(E33="A OR B";(E$31);IF(E33="C OR D";(E$32);""))

  107. Htay says:

    Hello Svetlana,
    Your site is very helpful!
    Could you kindly help me with below:
    I need a formula where if the cell in particular row contain values from a table, then return value "trade", otherwise "unknown". The list contains over 100 values, therefore the ISNUMBER(SEARCH) function cannot be used.

    "Table" example:
    11 trade
    12 trade
    13 trade
    14 trade
    15 trade
    186 trade
    ...

    List example:
    13 trade
    13,14 trade
    15,2222 trade
    2882,2213 unknown
    14,11,75,2882,2213 unknown

  108. Deepika says:

    Hi,
    I have to find difference between two dates (Ex. A1 & B1). B1'=(now)' . I want results to display either one of these-"Minutes Ago","Hours Ago", "Days Ago","Week's Ago","Months Ago". whichever is the least.

    EX:A1= 2/16/2014 7:05:05 PM, B1=2/18/2015 2:29:13 PM.
    My formula gives me this result =1 years 12 months 367 days

    DATEDIF formula {=DATEDIF(A1,B1,"y")&" years Ago "&DATEDIF(A1,B1,"m")&" months Ago"&DATEDIF(A1,B1,"d")&" days Ago".
    This does not give Hours and Minutes. Can you help?And also is there a way to use If formula to display only 1 result i.e, "Minutes Ago" if this is the closest? Please help !

  109. Akhi says:

    Hello Svetlana,

    I need a formula that could generate email from from a given list of names.

    I need the formula to be in such a way that it could satisfy both the below condition's.

    For ex:

    if there is:

    Condition 1:

    ==>> First name + Middle name-1 + Middle name-2 + last name
    Ex : ( how ) + ( are ) + ( you ) + (today )

    to generate : how.are.you.today@gmail.com

    Condition 2 :

    ==>> First name + Middle name-1 + Middle name-2 + last name
    Ex : ( how ) + ( isblank ) + (isblank) + (today )

    to generate : how.today@gmail.com

    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

    looking forward to hearing from you soon.

    Thanks in advance.

  110. VIKRAM SINGH says:

    i need a formula for first date is less then 2nd date how to check

  111. Tar says:

    Hi Svetlana,

    I want to create the formula that select the cell P if the life time less than 10 years ,I put this formula =IF(S5<10,P5,"")but I also need to have same result if life time "1-5", (between 1 to 5) and I put this =IF(AND(S8<10,S8= 1 - 5),P8,"") didn't work, could you please help me.

    Thank you,

  112. Dogan says:

    Hi,

    1) I need a formula in I16 that can check cell B12 for # of servings (if any).
    example: B12="Sangría Rojo (2 glasses per guest). 10 servings."
    2) If B12=x servings then x*h12
    3) If B12 does not contain x servings then h8*h12
    example no servings: B12="Sangría Rojo (2 glasses per guest)."

    Thank You

  113. Henkli says:

    Hi,

    Building a transcation register. Currently using this formula =IF(D13>0,H12-D13,H12+F13).
    But, when I copy this to the rows below I'll get the last result shown in the rows below. I would like to check the cells for values, before using the above mentioned formula. So when there are no numbers in cell D14 and F14, it will return an empty cell in H14. If there are numbers in either D14 or F14 use above if formula.
    Thanks for your help!

  114. Darnella says:

    I am trying to add 1.5 to values higher than 3. Values 1-3, I need to remain the same. How can I do this?

    Ex. person enters 4 in a cell it is automatically calculates to 1.5
    Person enters 2 in a cell it remains the sames.

  115. ramesh says:

    Hi Svetlana Cheusheva and Alexander,
    could you please help in solving my problem
    my excel column contains -5:55 -2:26 -2:19 -1:38 1:06 1:16 1:17 2:20 like wise nearly more than 100
    my result column to have Neg Neg Neg Neg 1-2 HR 1-2 HR 1-2 HR 2-3 HR
    could you please provide me an IF formula
    am using 2007 excel
    Remaining-TAT(h:m) TAT
    -2:19 Neg
    -1:38 Neg
    1:06 1-2 HR
    1:16 1-2 HR
    1:17 1-2 HR
    1:20 1-2 HR
    2:02 2-3 HR
    2:05 2-3 HR

  116. conharis says:

    1. i have a table with 2 columns (A,B)
    2. column A has a list of control measures and
    column B has 2 options(yes or no)
    3. what i want is if the answer is "yes" to NOT extract the control measure in a new worksheet and if the answer is "no" to EXTRACT

    e.g A1 (warning signs in place?) B1 (no)
    A2 (workers are aware of local emergency procedures?) B2 (yes)

  117. Karina says:

    Cell A1 is a hand entered date. Cell B1 is a total dollar value. Cell C1 is # of months. Starting with Cell D1 and going right are 12 more columns for the months of the year. Need a formula in D1 that says this: If the date in A1 is between January 1st and 31st, then take B1 and divide it by C1. Once I can get that, I can nest it with ifs to cover all of the months. My problem is how to do the date portion. Can you help??

  118. danika says:

    =SUMIF(January!A5:A200,January!U4,January!B5:B200)
    The January!B5:B200 has names in it but it is coming up 0. i am sure that i am using the incorrect function

  119. HC says:

    Hi,

    I am using the =if(isnumber(search) to look for partial value. However, in my list I have 180 and 18. the end result will only pick up 18 instead of 180.
    What other functions can I use to ensure that both 18 and 180 will be pick up?

  120. Anonymous says:

    18,19,43 in a column with test date at next column. 18's validity is 1 year,19's 5 year and 43's 6 month how can we show the due date in excel at a glance.

  121. Mahwish Naseem says:

    Worker Type Emp # Days Worked Total hours Worked Over Time Over Time Charges Basic Pay Net Pay
    A 121 20 190
    B 122 19 200
    C 123 24 231
    A 124 21 210
    B 125 25 215
    B 126 28 180
    C 127 15 140
    1) Over time=Total hours worked-(days Worked*8)
    2) Using If Function calculates over time charges
    3) Over time charges=Overtime *(100 if worker type is “A”, 50 if worker type is “B” and 35 if worker type is “C”)
    4) Basic Pay =Days worked *(400 if worker type is “A”, 300 if worker type is “B” and 200 if worker type is “C”)
    5) Using SUM() function compute NetPay=Basic Pay+Overtime Charges

    please solve part 2 and 3 , i m just got mad

  122. Bagwis says:

    Hello!
    Thank you for this guide. It's very helpful. I do have one question though, I am trying to copy a date on a cell. Say, date is in A1 and a restriction is in B1. My formula goes like this - =IF(B1="0","",A1). C1 should have be a copy of the date in A1 right? Wrong. Mine shows 420417. It shows numbers T_T not the date. Please help. Thank you in advance.

    • Bagwis says:

      Sorry about this one. After a few trial and error, I managed to get it :D. Here's how I did it...

      Make sure that the cell you are copying is in a proper format. Don't let it automate to DATE, CURRENCY and etc. Then, before you create a statement, say =IF(X=A,"","B"), make sure you CLEAR FORMAT that cell. Good luck to you guys :D Thanks again.

  123. MIMI says:

    Column
    A B

    Company A
    Rpt1
    Rpt2
    Rpt3
    Company B
    Rpt1
    Rpt2
    Rpt3

    I want analyse the similar data above by puting the header name (company name) next to each sub item cells
    example I want to see like this.

    Column
    A B

    Campany A
    Rpt1 Company A
    Rpt2 Company A
    Rpt3 Company A
    Campany B
    Rpt1 Company B
    Rpt2 Company B
    Rpt3 Company B

    Please anyone to help on this formula?

  124. Nery O'Neil says:

    Hello dear friends

    I need help I have

    (column A is time in) (column B is time out) A1: 7:30 PM B1: 11:00 AM, I'm using the following formula to calculate the hours worked, less than 30 minutes lunch

    =MOD(B1-A1,1)*24-"0.5"

    BUT I only need to take the 30 minutes lunch ONLY if the employee worked more than 6 hours.

    "IF EXPERTS "HELP!", (THANK YOU) "" :)

    (employees are nurses so they work overnight and all kind of crazy hours). Can you help me to figure out an "IF" that works for something like that?

  125. Cha says:

    Hi. I'm trying to figure out how to make excel go to the next sheet if a particular cell value is equal to "true".

  126. Cha says:

    example: if cell a1 = "true" go to sheet 2

  127. Jan says:

    I need to display a cell from another tab row using today's date. How would I do this?

  128. Jan says:

    if row xx column xx = (todays date) display row xx in column xx (for example)

  129. Salah says:

    Please some one help...

    I need can enter the data if the nearest cell having only as "OK" otherwise should not able to enter any data.

    EX:
    E10=OK---> E11= Possible to enter data
    E10=asfa-----> E11= Not possible to enter data

  130. Akash says:

    I want a cell showing - , when i use it for calculation it's value should be 1.
    Please tell me how to do it?

  131. Dixie says:

    Is there a way to create an IF statement based on whether a number is a date? For example, If the value in A1 is a date, return the value of the date, otherwise, do nothing.

  132. yash says:

    How to check if there are 10 characters in a cell or not

  133. Miro says:

    Hi Svetlana ,

    I need help with this one. I have one column lets say A1 : A38 that have some values / text . Those values can repeat themselves through A1 : A38 many times . I would like to get in another row or even sheet only one of each of those values or records.
    For Example : A1 have TB231 ; A2 TB236 ; A3 TB555 ; A4 TB555 ; A5 TB231 etc , now in separate sheet or row i should get only one of those , it dosent matter how much it has same records i just need that i get value of all different in separate sheet or row.
    Results of example should be : D1 TB231 ; D2 TB236 ; D3 TB555 , i tried with IF function but for 38 or more rows would be huge so maybe there is other easier way.

    Thank you

  134. Scott says:

    Hi, I need some help with this formula. A1 = $25000 B1= if(A1>0,"Total (A1)","Total"). If A1 is greater than 0, I would like B1 to show [ Total ($25,000.00) ] and if A1 = 0, I would like it to show [ Total ]. Does anyone know if this is possible?

  135. Panfoua says:

    Assume that even numbered respondents (identified by RespondentID) are business travelers and odd numbered respondents are leisure travelers who travel infrequently. In column F on the surveyData tab, create a formula that identifies each respondent as either a Business traveler or Leisure traveler. You will need to use Excel’s If() function (see your Excel handbook or Excel help). Title this column travelerType. Ensure that cells F2:F501 are filled with the formula. Please help me create a formula.

  136. Prince says:

    Hello sir, I'm Trying to enter Data in Sheet in form of numbers
    for example:-

    Roll-No:- 801
    Maximum Marks
    20
    20
    20
    20
    20
    20
    20
    Sig. of Class Incharge

    Roll-No:- 815
    Maximum Marks
    20
    20
    20
    20
    20
    20
    20
    Sig. of Class Incharge

    but the problem is when i try to drag or paste the same cells in another line.......Rollno has changed 814 instead of 802. My all cells are refrence cells of sheet1.

    Now which formula is best during drag or paste the rows.If i paste the row after 14 Rows ..roll no show 815 instead of 802.
    please guide me what should i do?
    if u want i can send you the copy of Sheet.
    Thanks & Regard
    Gandhi

  137. chris says:

    Hello, I have a date in cell D4, let's say 1/2003 displaying as 2003 due to custom formatting to YYYY and a formula in E4(also set to YYYY) which is D4+4385 because I want E4 to display 12 years after the date in cell D4. This formula works great if I have data in cell D4. If I don't, it will display 1912 in E4. What formula can I add to E4 to make it blank if D4 is blank without deleting the formula =D4+4385? Losing hair as we speak :/

  138. Leif says:

    Is it possible to create an IF calculation that asks a "greater than this number, but less than this number" question? i.e., if E17 is greater than 1800 or less than 2400, than perform E17-540*60%, otherwise place a zero. I get errors when I try this example...

    =IF(E17>1800<=2400, (E17-540*60%), 0)

    Thanks

  139. Anonymous says:

    =IF(I10-K10, IF(I10="",N10-K10, IF(ISBLANK(N10),"2")))
    the first one will be formula then the other if blank of i10 IS BLANK USE FOR N10 THEN IF N10 IS BLANK IT SHOULD BE EQUAL TO "2" THANKS
    hi how to comine this formula thanks

  140. ajeem khan says:

    hello sir,
    I want to know how to add a1 value.in a2, a3, a4.....
    If i change value a1,all a2, a3, a4 ...value change auto.

  141. ajeem khan says:

    Sir if my output value is 12100, 12200,12300, 12400 all value change auto 12500.
    And my out put is 12600, 12700, 12800, 12900. That value change 13000.

  142. M P. says:

    Please help me check my error
    =if((Q339,"D"),(Q3>49,"C"),(Q3>59,"B"),(Q3>69,"A"))
    I am computing exam result

  143. Nick says:

    i am working on aproject. I need help with
    f2 contains both words and numbers. How do i display the text and numbers in 2 separate cells?

  144. pmenon67@gmail.com says:

    i need some help from you.

    =IF(M9="yes", C9," ") How can i put this command in sheet 1 for the result of sheet2

  145. Penny says:

    Need a formula to calculate following: if city =x zip =y. I keep getting a value error.Note I have approximately 10 cities and zips to include.
    Also need a formula stating: state always =CA

    Thanks for the help.

  146. Nader says:

    Hi Svetlana,
    Thanks for the great post, it helped me a lot. My case is a bit different. I want to use IF function in this way:
    IF xxx has the word "thanks" inside it, then "welcome" or "ok".
    I hope there is a way.
    Thank you.

  147. Raphael Garcia says:

    I need a formula to automatically display the current accrued PTO in a specific cell every time the excel sheet is open.
    The accrued PTO is 6.15 hours every two weeks. I have created a table with the dates and the corresponding accrued hours for reference.
    I tried IF and VLOOKUP statements but can't get the formula to work.
    Can someone suggest a formula?
    Any help will be greatly appreciated!

    Thanks,
    Raphael

  148. Craig says:

    Not sure if this can be done. Need to search a range in A1:A20 for a text string and then return the value in the same row but 2 columns right. So for example look in A1:A20 for "Find me" and if find me is in A4 return the value that's in C4. Any ideas if this can be done?

  149. Nikki says:

    Hello I need to use a formula that will display YES if the amount is $100 or more and remain blank if it is under $100.

    So far this is what I have =IF(B2>=100,"Yes")

  150. siva says:

    I request to you i make a time , using if statement =if(A110,"A-10")

  151. siva says:

    i was making employee salary sheet , i am using three rows,1st row is total hours, 2nd row is basic overs & 3rd second row is over time , our company basic hours is eight.
    Example :-

    total overs = A1=10 B1=12 C1=A .....Ext.
    Baic overs = A2=8 B2=8 C2=A ....Ext.

    Over time = A3=2 B3=4 C3=X...Ext

    iam using basic overs A2 =IF(A1=8,"8",IF(A1="A","A"))))

    A3=(=IF(A1=8,X,IF(A1=8.5,.50,IF(A1=9,"1",IF(A1=9.5,1.50,IF(A1=10,2,IF(A1="A","X"))))))

    iam using this function ,but Sundays also working overtime . i need help you Sundays hours u need over time row which function use .......... please give him the reply

  152. AJAY says:

    asskm..
    saya ingin membuat satu rumus if :
    jika A1 berisi desimal maka B1 adalah 1, jika A1 berisikan teks maka B1 adalah 0,
    mohon bantuannya untuk bisa sy membuat rumus excelnya. terimakasih..

  153. Enric says:

    Please help, I need a formula like when you subtract A1-B1 if the answer is 0.something the answer will be 0.
    Like this one: =IF (A1-B1<1,"0")

    Thank you...

  154. mina says:

    hi,
    i need help please,
    how can i add number if my result is less than 0
    example;
    =(P9-DAY(TODAY())) and i got the result -1 i dont need to see - so i wanna add 30 how can i do that

    thx

  155. Isaac Martinez says:

    Hello,

    I want to write an If then statement that takes cell contents and adds the same word to different cells. For example. suppose CELLS A3:A36 are words related to education (i.e. test, assignment homework etc...) Now suppose I want to add the word "calendar" to the words in cells A3:A36. Which would return "test calendar", "assignment calendar", "homework calendar" etc..
    How would I go about doing that?

  156. Kevin S says:

    =IF(F2:F152="Yes", "G3"),(F2:F152="No", "G3") returned #VALUE! What is the issue?The logic I am trying to achieveshould be if F2 thru F152 = Yes then return the name G3 and if f2 thru f152 = No return the name in column G3. Can you give me the correct formula to achieve this?

  157. Munafar Kamal says:

    i need to make a formula in excel to put bonus marks, for example if kevin got 25 marks he will got another 25 bonus, if he got 50 then another 25, each 25+ mark got bonus of 25... i will put only the mark on one cell and i need in the bonus mark to calculated by excel in another cell... please help

  158. mehbub says:

    Please Help me :
    =if(a1=2366,2355,2353 then b1 will be 120, if(a1=2377,2357,2354 then b1 will be 130)) how can i put the formula..

  159. Peter H says:

    I have 3 cells (L3,M3, N3) with either true or false as responses to earlier coding but need to highlight where I get answers that are True,False, True in that order . How do I achieve this?

  160. Calvin says:

    Hi,

    I am hoping to create a formula to use in conditional formatting that would allow me to perform the following:

    I have a list of employees in column A, date in column B, and number of sales in column C.

    I am wanting to conditionally format the sales cell (in column C) as new sales data is entered. I want to compare the new sales figure against the most recent number of sales for that particular employee. With a reduction in sales of >3 formatted red.

    Your assistance is greatly appreciated.

  161. Julie says:

    Hello, I am not sure if what I want done is possible. I am looking to say if "apple" is in cells a2:a36 and there is an amount in cells h2:h36 then I would like it to total those amounts. Is that possible?

  162. Bill says:

    I have two columns of numbers, side by side. New numbers are loaded into the left column each day (column 1). I want to save the historical high number in a cell beside it (in column 2). If the new number in column 1 is greater than the old historical number in column 2, I want the historical nigh number in column 2 to be changed to equal the new number that came into column 1.

    How do I do that?

  163. Monica says:

    I have a spreadsheet that has maximum number of locations in column B and and minimum number of locations in column C that a client uses as a reference when choosing services. the client seeing these number enters their desired number of locations into column D which is where the formula would be entered. Want them to be able to put in their own number that will show on the spreadsheet BUT...I do not want it to let them enter a number that is lower than the minimum or higher than the maximum. I also do not want it to say false or OK I would like the number they have added to show.

    Additionally if they try to go less than min or more than max, I would like a pop up box saying "You must enter a number that is less than the maximum and greater than the minimum number allowed"

  164. mohammad ali says:

    Hi - need some help in extracting xxxx from a column that has"router bgp xxxx". xxxx varies across cells. What function can i use to extract xxxx?

    Appreciate the help in advance!!!

  165. Billy says:

    Hi,
    I need some help, I'm new to the formulation.
    I need to create a sub-production plan link to another sub-plan
    but I don't know at all how to do it.
    Example sub-production have 2 shift opening stock,need create auto plan minus current balance 2 shift with quantity issue
    I need to create a sub-production plan link to another sub-plan.
    With opening 2 shift stock, current shift auto plan which minus stock issue from opening 2 shift stock to get return of 2 shift stock and 2nd auto shift will continue, once reach to one date which no plan, so auto plan automatically stop generate plan.
    Could it be done.
    Your deliberation on helping me is much appreciate.

  166. Amy says:

    Hi Svetlana,
    I would like the next cell to represent if it is higher, lower, or equal to the previous cell. Each cell represents a total of items for a month. Ideally, it would be an up or down arrow (in green or red) for the execs to immediately identify changes.

  167. Kita says:

    Hi,

    I am trying to figure out a formula for m aging. If its a negative number then its current, if it is between 1-29 days it's "30 Days", if it is between 30-59 days "60 Days",if it is between 60-119 days "90 Days" and if it is between 120-365 days its 120 days.

    Please Help!!!!

  168. Anonymous says:

    IN YOU TUBE I AM FEELING EASY TO UNDERSTAND ANY CONCEPT REGARDING EXCEL BUT IN GOOGLE IT IS LITTLE BIT COMPLICATED FOR ME AS BECAUSE THERE IS NO VIDEO LIKE YOUTUBE AND ALSO I AM A PLAIN GRADUATE.PLEASE DON'T MEAN IT WHAT I HAVE WROTE.I AM HAVING VERY EAGER TO LEARN EXCEL BUT I AM 35 YEARS OLD.PLEASE SEND EASY SOLVED SOLUTION OF EXCEL VIDEO IN YOUTUBE AND IN GOOGLE.

    THANKS & REGARDS

  169. Rob says:

    Hey guys - it seems as though I came to the right place...I am using excel 2007 and trying to work a formula to check cell A1 for a value of 1 and if the value of 1 is there, then insert the current date in cell A2. Please help.

  170. Geoff says:

    HI Svetlana,
    I'm trying to write a formula that returns me 'true' or 'false' depending on whether there's a £ sign in a cell with numbers, ie: £32 gives 'true', but
    €32 gives 'false.
    I've tried your:
    =IF(ISNUMBER(FIND("£",G7)),"true","false") and
    =IF(ISTEXT(FIND("£",G7)),"true","false") ,
    and both of them with SEARCH instead of FIND
    but they don't work, presumably because £ isn't text or number. I vainly tried various things like ISCHARACTER and ISSYMBOL as well....
    Hope you can help.
    Thank you.
    Geoff

  171. cyrus says:

    Hi,
    I want to have a formula whereby i am doing an itinerary costing. i have a start date and then dates with prices of accommodation...the dates with the price of accommodation i already have IF(B3=DATE(2015,1,1),"7500","0").....

    Now I want to have a formula where by I when the start date of the itinerary changes, then all the dates in the in the itinerary change and this change in starting date will also lead to a change on the prices.

    Thanks

  172. Jr says:

    Hi,
    I'm looking for a formula that will return the last value greater than 0 from a range if the cell initially referenced is equal to 0.
    Seems like it should be easy but I'm stuck on it.

    Thanks

  173. Jordan says:

    Hello,

    I have a due date column that is calculated at 30 days after a transfer date column. However, when I put the formula "=A1+30" in the due date column it returns a date of 01/05/1900 for rows that don't have a date entered in the transfer date column. How do I get this to stay blank unless there's a date entered in the transfer column?

    Thank you,
    Jordan

  174. Ussiels says:

    Hello,
    I ave a situation, i whish you can help me with: i need to work on a formula with the if funtion. i have to mark a range of values, for example: all the values are in the cell "B2"

    range: 1 - 100
    markA: 1 - 20 ( "a" )
    markB: 21 - 35 ( "b" )
    markC: 36 - 50 ( "c" )
    markD: 51 - 89 ( "d" )
    markE: 90 - 100 ( "e" )

    i have try the next formula:
    =(IF(B2=21,B2=40,B2=71,B291,"VFEL"))

    but it says the is a error, can you help out ?

    thanks

  175. Ussiels says:

    =(IF(B2=21,B2=40,B2=71,B291,"VFEL"))

  176. Ussiels says:

    it doesn´t paste all the formula.. sorry

  177. Bharath says:

    Hi Everyone,
    Nice Day !

    All medical representative send their reports in 2 excel sheets,
    1.sales Visit – with date,product,doctors list
    2.Office Word

    now i have to compare these 2 lists and make a new report that whether they have send report or not according to names and date.

    if they send 01 mar, then i put yes
    if they not send 02 mar, then i put no

    can anyone tell me which formula can use to compare these 2 list and make it automatically “yes” and “no” according to medical rep names and dates.

    Advanced Thanks for reply

  178. Lorna says:

    Hello everyone,
    I'm looking for a formula that looks at date ranges - so if the date of the line item is between 'date 1' and 'date 2' then display the line item in on a separate sheet.
    Similarly if the date of the line item is NOT within 'date 1' and 'date 2' then display the line item on another sheet.

    Any assistance would be greatly appreciated!

    • Hi Lorna,

      You need two different formulas like these:

      1. If between 2 dates:

      =IF(AND("line item date">date1, "line item date"<date2), "line item", "")

      2. If outside the date range (not within 2 dates):

      =IF(OR("line item date"<date1, "line item date">date2), "line item", "")

      If you can provide more details about your date structure (in what columns are dates and where are line items), I think we will get the real formula.

  179. JBirdy40 says:

    Hi there I am wondering if you can help me
    I have the following data across columns and the desired outcome would be merge the cells sideways into a new cell and delete the duplicates.
    Column J Column K Column L etc
    Row 2: Article sign up | City1 | City2 = Article sign up, city 1, city 2
    Row 3: Article Sign up | Article sign up | City 1 | City 1 = Article sign up, city 1
    Row 4: Article sign up | Article sign up | City 2 | City 2 = Article sign up, city 2

    Can you help?

  180. aidil says:

    =if(A1=12," ","12"),if(A1=8,"16"),if(A1=16,"8")

    can anyone fix it... it will a very helpfull for me...

    thank you very much

  181. S Khan says:

    Hi,

    in column A user will be entering date. In column B the month will get updated automatically with =Month(A1) function.
    Now I want if the cell of A column is blank, then in column B will be showing "-" & the moment user enter the date it will return the month.

    What would be the formula in excel... pl surrgest

  182. IS says:

    How could we change the color of the Cell if the logical_test is true or false?
    Thank you.

  183. S Khan says:

    Hi,

    How can I restrict the cell in excel, where user have to enter the date in "MM/DD/YYYY" format only.

  184. Lynn says:

    I am trying to populate a cell with the description "Current", "30 Days" and so forth.

    I know I can use this Formula

    =IF(L2>0,, "Current")
    =IF(L2>30,, "30 Days")
    =IF(L2>60,, "60 Days")
    =IF(L2>90,, "90 Days")
    =IF(L2>120,, "120 Days")

    How do I make a full sentence (Combining all) of this so i don't have to do this individually?

  185. Felicia B says:

    Hi,
    I'm trying to get my formula to say if I5=Y or N/A then 0 value, if not then 1 value. I'm using this: If(I5="Y""N/A",0,1) but I'm getting the value 0 no matter what's in I5. I5 is either Y, N, or N/A, and I only want it to populate when there's an "N". I thought the problem was that "N" and "N/A" both contain "N", but I typed in "fill" to test and still 0 value. Please help. Thanks!

  186. Jason says:

    Hi Svetlana how would you write this all in the same cell ... (If Cell A1 says “PUT” then Subtract cell B1 from C1 OR If Cell A1 says “CALL” then Subtract C1 from B1)

  187. THELLO says:

    HOW TO MULTIPLY ONE VALUE OF A CELL BY THE PERCENT OF ANOTHER CELL

  188. Sasha says:

    i need help putting this in the if function, if an employee has a taxable income of greater $739, then the federal income equals $110.85 plus the federal tax rate found in cell B19 multiply by the taxable income excess of $739. if an employee taxable income is $739 or less, the federal tax witheld equals the taxable income multiplied by the federal taxrate found in b18

  189. Jason says:

    Thank you Svetlana! To take this one step further and I tried couldn't get it to work

    =IF(A1="put",C1-B1,IF(A1="call",B1-C1,"")) then take answer of this and calculate the difference between the answer and D1 *100* E1

    • Jason says:

      This is more in line with what I was thinking all wrapped up in one formula - =IF(A1="put",C1-B1,IF(A1="call",B1-C1,"")) --- This is the first operation

      IF cell O25 is a positive number subtract U25 or IF cell O25 is a negative number add U25 --- This is the second operation

      How would you write them separate and within on formula sequence?

      Thank you again!

      • Hi Jason,

        Please specify if these are AND or OR conditions?

        For example, if A1="put" and O25 is a positive number, then what do we do?

        If A1="put" and O25 is a negative number, then... ?

        And the same for A1="call", please.

        • Jason says:

          What I believe I'm trying to do is calculate the positive or negative variance (on a non absolute basis)

          My apologies, so I have your formula =IF(A1="put",C1-B1,IF(A1="call",B1-C1,"")) ... in column "U"

          And in column "V" I'm trying to calculate the actual difference between cell O25 and U25(i.e. Add if a number is negative, Subtract if a number is positive)say if U25 Is positive and O25 in positive then O25-U25 or if U31 is negative and O31 is positive then U31+O31

          I was also would like to combine this formula with your =IF(A1="put",C1-B1,IF(A1="call",B1-C1,""))

          • Let me check if my understanding is correct. If values in both columns U and O are positive, then O25-U25. If a value in either column or in both columns are negative, then U325+O25. If so, I would suggest that you keep the formula in column U as is and put the following one in column V (it is actually for cell V25, but you can easily adjust the references:
            =IF(AND(U25>0, O25>0), O25-U25, U25+O25)

            Also, please note that the formula equates zero to negative numbers. If you want to treat 0 as a positive number, then change the logical test to AND(U25>=0, O25>=0)

            If I misunderstand something, please correct me.

            • Jason says:

              Nope you made my nonsense into sense;-) So in that last formula =IF(AND(U25>0, O25>0), O25-U25, U25+O25)... how would I make any negative result display 0 and any positive result show the full value of the calculation?

  190. Raymond says:

    If somebody can please help me.
    Mentioned below is my problem
    I have a time format and I am trying to put an If formula which should give me a yes or no if its greater than or equal to a specific time

    FOR EXAMPLE:
    If (04:07:47)is greater than or equal to (04:00:00)then YES else NO

    • Hi Raymond,

      If both times are entered in some cells, then you can use a simple IF formula like this:

      =IF(A2>=B2, "yes", "no")

      Where B2 is 04:00:00.

      If you want to specify one of the values directly in the formula, then you will need the TIMEVALUE function is addition to IF:

      =IF(A2>=TIMEVALUE("4:00:00"), "yes", "no")

  191. Rajendra says:

    I've two columns A & B, out of this i want to check against A column name if B column is not blank then it should show complete else Partial, for more understanding see below example

    C-A C-B Output
    A ABC12 Complete
    A 105 Complete
    A 13 Complete
    B 213 Partial
    B ABC12 Partial
    B Partial

  192. Raymond says:

    Thanks a lot Svetlana that worked as expected :)

    Cheers

  193. cheryl says:

    I have created a spreadsheet for working out the pay of our employees. The data starts in Row 14. Col A contains that week's pay date eg. 24/3/15 etc. Col B has the total hours the employee has worked that week. Column C then multiplies the hours from Col B by the employees pay rate to give me their gross pay for that week. Cols D-F contain various deductions relevant to that employee & Col G lists net pay. Following that there are 4 more rows containing the above info with the hours listed as 0 unless it is used for that week's pay, and the sixth row adds up the totals for that month & displays them as *??.??.

    In cell b3 I want a formula that tells me how many hours the employee worked in the last 4 weeks e.g..it will look down column B, find the last hours entry (ie. the last number that is greater than 0 & does not have an *), then add to this number the previous 3 weeks hours (ie. the previous 3 numbers greater than 0 & without an *). If it makes it easier I am happy to enter in the cell reference that contains the last hours entry (rather than Excel working that out) as I am not going to need this information on a regular basis.

    In cell b4 I want to do do the same thing as b3 except i want to know how many hours the employee worked in the last 52 weeks (or if they haven't actually worked 52 weeks yet, their total hours so far).

    I'm sure that this is not actually a difficult formula to write but excel is not my forte! Please let me know if you need further clarification.

  194. Ted Jerome says:

    Hi, Svetlana!
    I'm trying to make an Excel 2013 formula that will extract a person's last name from a cell that has their full name. Unfortunately, the fullname cell can contain any of these formats:
    First Last
    First M. Last
    F. Middle Last
    F. Middle Second_middle Last Jr.

    The formula to extract the last name form a "First Last" cell is easy (where A5 is the fullname cell):
    =RIGHT(A5,LEN(A5)-FIND(" ",A5))
    ...but I need to be able to find the last instance of a space character to lead off the RIGHT() function location. That would work for the first three examples, but not the last. Not sure what to do about that one, unless maybe an exclusion list of common suffix titles?

    I'd be most interested in your help.
    Cheers!
    Ted

  195. Sandeep Gupta says:

    How many times i can add IF in one formula.

  196. Ankit says:

    can you help with the below:
    i am creating a sheet for associates in my company. 1 tab has all details of the associate that a company database should have. column 'F' has the designation of the associate and column "A" has names
    what i want to do (in another tab in the same excel). wherever column "F" has designation as manager the cell in the new tab should show me the name of that associate.
    eg: if cell value f4=manager then value in new cell (in new tab) should be name as in A4.

    i want the same to be applied to every cell.

  197. istkar says:

    I created a quiz and used data validation. I want to get 1 mark for the answer of 42 otherwise 0. I used if(A4="42", 1,0).it give me 0. am I using wrong syntax

  198. JAS says:

    HI,

    I WANT TO CREATE IN EXCEL 2007 Which shows that in a selected cell (specifically Column K in spreadsheet in each cell downwards)if I will type a Value on it(numbers. 1-9) it will correspond to a word. say for example 1 means okay, 2 means not yet and etc. some sort of shortcut techniques in typing for that word. what kind of Formula should I use? Please let me know.

    Thanks Svetlana!

  199. S Khan says:

    hi,

    In my data all the values are in Indian currency in rupees. While I create pivot table summary, all the values comes with in rupees. But I needs those figure should come in Lacs.

    Thanks in advance.

  200. Jay says:

    I need to use IF function on below case;

    If one item is available from a column, then Yes or else No. Please advise the fuction.

  201. Givious says:

    i need help, i have data which i need to analyse, in a cell i have entered data for males and females with the figures of household size, e.g male 2 and female 3, so i need a formula to find the total of males and females separately.

  202. Rupesh says:

    ( >90 then 4, 80-90 then 3, 70-80 then 2 and <70 then 1

    i tried to put a formula on this but its not working.
    can you please suggest

  203. Hello Rupesh,

    You need a nested IF formula like this:

    =IF(A2>90, 4, IF(A2>=80, 3, IF(A2>=70,2,1)))

  204. Amarildo says:

    Hi, i am wondering if it can be done when i sale an item and lets say inventory is 20 so i sold one and inventory changes to 19 i want a column to go plus every minus i do in the inventory.

  205. Mel says:

    I am trying to run an IF formula this is what I was trying to do =IF(A1>0,[A1=B1],[A1=0])
    Basically if cell A1 is greater than zero I want the cell to be replaced with the data from cell B1, however if cell A1 is not greater than zero I want the cell to equal zero.
    Can someone please help?!!!

  206. Nadeem says:

    Hi I want to use this formula with =If condition
    =RIGHT(A2,LEN(A2)-FIND(" ",A2,12))
    i tried many time but it not fits in.
    =if(A2<len(9),"Write your full name,RIGHT(A2,LEN(A2)-FIND(" ",A2,12)))
    why this is not working????
    Please guide me

    • Hi Nadeem,

      Sorry, I cannot understand what your RIGHT formula does. Can you explain in words?

      Anyway, the IF function may look similar to this:

      =IF(LEN(A2)<9,"Write your full name", RIGHT())

      It returns "Write your full name" when A2 contains less than 9 characters. Otherwise, what is it supposed to return?

  207. Bismark says:

    i want a formula in excel that will return a value with it superscript Eg 1st

  208. Joe H says:

    I've got two columns of numbers to compare and see if the difference is + or - 5. How would I make a formula to do that?

  209. Brianna S says:

    I need a formula for:
    If A1=1, then B1:B4=1, otherwise B1:B4=0. If A5=1, then B5:B8=1, otherwise B5:B8=0.
    I can also do:
    If A1=1, then A2:A4=1, otherwise A2:A4=0. If A5=1, then A6:A8=1, otherwise A6:A8=0.

    When I try to copy this formula down to record 10,000 I am getting a loop error. Excel is returning:
    A1
    1
    0
    0
    0
    1
    0
    0
    0
    How can I fix this??

  210. Mahuma says:

    Hi,

    If you are give amount that between 6 to 10 is equal to 0.5 however I have to formulate it in such a way that below 6 it is to be number divided by 24 and above 10 it must be number divided by 24.

    If(A3>0=6=10,Number/24,"")))

    please how do I go about.

  211. Mora says:

    Hi, I wanted to know how can I get numerical value for 18 and below and 46 and above
    thanks

  212. Eddie says:

    Hi Svelana,
    How are you today. I appreciate the time you're taking to help me with my problem. I'm sure all these individual questions can be burdensome. My question is:

    I have the first 80 cells numbered 1-80 in sequence. I have this in every row as well, and then after that I have 20 columns each with a number 1-80. I wanna highlight one of the 1st 1-80 if one of the 20 cells has a number that matches it. Is this possible?

    Thank you,

  213. Mahendra Rathore says:

    Hi Guys,

    I am new to VBA. I need a VBA macro code that will convert HTML data into Text in my microsoft 2007 excel.

    one column A contain n number of rows data with HTML data I want to run a macro so it can be convert to Text data in just near by column that is B.

  214. Jeff says:

    Hello,
    I have a workbook with multiple sheets. In column A, of sheet 1, is a list of names. I have been able to get those names to populate on sheet 2 and three using ;
    =IF(INDIRECT("Sheet1!A" & ROWS($1:1))="","",INDIRECT("Sheet1!A" & ROWS($1:1)))
    This works well to a point. What I would like to happen is when a name is deleted from column A, sheet 1, I would like the data in sheets 2 and three in the corresponding row to be deleted as well. i.e. if row 17 (A-L) on sheet 1 is deleted, then row 17 A-L) on sheets 2 and 3 will be deleted.
    Thanks

  215. Krystalya says:

    I want to test the value of a portion of a cell. For example: The cell says Approved: 12345 where 12345 is a changing order number, the list has Declined and Refunded transactions as well. Is there a way to test only for the word Approved without consideration of the remaining data within the field?

    • Hello Krystalya,

      You have to use ISNUMBER and SEARCH function for partial match, like this:

      =IF(ISNUMBER(SEARCH("approved",A2)), "yes", "no")

      Where A2 says "says Approved: 12345".

      Please check out "Example 3. IF formula for text values with partial match" in this tutorial for full details.

  216. Laura says:

    I want to write a formula where if the value in a particular cell is less then 18 I want the cell to indicate the cost is $48 but if the value is greater than 18 then I want the value to be $48 plus the value greater than 18 (or the value +30)

    if A10 < 18 then = $48.00 but if A10 < 18 then = (A10 + 30)

    Can this be done?

  217. Fraz Rashid says:

    I need help with a if statement formulae

    The formula is:

    =IF(H18='Booking Date Log'!$J$13:$J$1048576, "Not Available", "Available")

    H18 contains a date e.g. 9/9/2015

    $J13:$J$1048576 contains a range of dates e.g. 9/9/2015 9/10/2015 ....

    True Value = "Not Available"

    False Value = "Available"

    what i need is that if H18 = any of these cells ($J13:$J$1048576) for it to come as "Not Available"

    and if H18 doesnt = any of these cells ($J13:$J$1048576) for it to come as "Available"

    Can this be done?

    thanks

    Fraz

  218. Doug says:

    I am doing an excel on monthly growth of values based on employees. Issue is some people start during the month and some quit. So I have some people with no value at the beginning of the month and other with no value at the end. I do not want those to show up as values.

    A B C D
    1 1/1/15 2/1/15 Growth Percentage
    2 225,749 248,324 200,471 88.8%
    3 420,284 420,284 0%
    4 48,744 -48,744 0%

    For D2, I did =IF(OR(A2="",B2=""),,(B2-A2)/ABS(A2)) which gives me the percentage of growth through the month and returns 0% if either is blank. Can I make column D actually be blank instead of showing 0%? Or, I want to show a total average growth at the bottom of column D without being penalized for all the 0%. If D5 was going to be the "average" cell, could I do =AVERAGE(D2:D4) but somehow tell it to not include the cells which have a value of 0%? Some people have a negative percentage and I need to include those.

    • Doug says:

      Well that table got all messed up after submitting it. Column A is 1/1/15's value, column B is 2/1/15's value, column C is the growth from 1/1 to 2/1 and column D is the percentage that they grew (or dropped) over the month.

  219. Anonymous says:

    if i5 is less then D5 value is zero and if D5 is greater then I5 value is D5 is show

  220. erin says:

    Hi Svetlana,

    I tried your formula here below to replace a text into a number and it works fine.
    But I do not actually know how to make it work for different datas on the same colomn.
    Let me quickly explain.
    I'm collecting in 1 sheet customers feedbacks from 7 different languages (sheets).
    So in the same colomn A, I have "Yes, Oui, Ja, Si, No, Non, Nein...." answers.
    The formula works fine for 1 language "=IF(A2="SI",1,0)" but do not know how to add the others..I would need number 1 to appear if it's an answers "Yes, Ja, Si, oui.. and a 0 if it's a "Non, No, Nain...".
    Any idea how can I compile all of this in one formula please?
    That would be much appreciated if you could help with this.

    Thanks a lot,

    Erin

  221. abbas says:

    first of all ,thanks in advance for the people whom create these very nice tutorials.I wanted to do math operation on the results of nested IFs.how could it possible with one formula??? my formula is as this:
    =IF(HOUR(D26)> 0; HOUR(D26)*45357; IF(MINUTE(D26)> 0; MINUTE(D26)*755.95; IF(SECOND(D26)> 0; SECOND(D26)*12.6; 0)))
    ---------I wana add a statement to above formula in order to sum the true values , it calculate the first but ignores others!!!! I wanna sum hour+minute+second

  222. TIMUA says:

    = IF(A10,A1, IF(A20,A2,IF(A30,A3,"BAD")))

    BAD is mean all 3 cells are blank

  223. Phantom0578 says:

    I have values in cells E6,H6,K6,N6,Q6 & T6 that I want added together. The values in these cells are from vlookups from other sheets in the workbook. Any of those 6 values (E6,H6,K6,N6,Q6 &T6) could contain a blank or have returned nothing as a result of the vlookup. I need to total these numbers but if I put a simple addition formula in I get an error because of the possible blanks. I am thinking it needs to be a complex nested if sentence but can't figure out how to tell it that I need it to add all the numbers and discount those that are blanks or empty cells.

  224. Ramesh says:

    Hi,

    i need the if condition formula for the below i need to allocate some of the qty so if total qty is less 5000 need allow 25 Pcs like details are below

    Qty Allow Qty

    12500 400

  225. erin says:

    Hi Svetlana,

    Could you please help me with my question below?
    I'm collecting in 1 sheet customers feedbacks from 7 different languages (sheets).
    So in the same colomn A, I have the answers "Yes, Oui, Ja, Si, No, Non, Nein...." or "Non, No, Nain...".
    The formula works fine for 1 language "=IF(A2="SI",1,0)" but do not know how to add the others..I would need number 1 to appear if it's an answers "Yes, Ja, Si, oui.. and a 0 if it's a "Non, No, Nain...".
    Any idea how can I compile all of this in one formula please?
    That would be much appreciated if you could help with this.

    Thanks a lot,

    Erin

  226. Alex says:

    Hi,

    I have a drop down selection in Column M with the choices: "China - UK", "China - EU", "China - ME"

    In column P I have a formula: =INDEX(Products!M:M,MATCH(C5,Products!$B:$B,0)) to automatically populate the duty rate for bringing into UK/EU - this is a variable percentage depending on the product.

    If in the drop down "China - ME" is selected I want to override column P with conditional formatting to show 5% which is constant for all products

    I tried to put the two formulas in priority order on conditional formatting

    =IF(M5="China - ME", "5%", "0") - selected stop if true
    =INDEX(Products!M:M,MATCH(C5,Products!$B:$B,0))

    Can you please tell me where I am going wrong?

    Kind regards,

    Alex

  227. Danish Abbasi says:

    Hi,
    i shall appreciate if you could please guide me as to how I can use IF function formula while making general journal (Accounts) to auto post entries into respective ledgers(account heads/expense heads) ?
    I look forward for a positive response.

    Thank you,

    Danish Abbasi

  228. Michelle says:

    Hi,

    How would I write the formula to get the following:

    If A1 3 but 6 BUT 9 But <=12 "Q4"

    THANKS IN ADVANCE FOR YOU HELP!!

    Michelle

  229. Michelle says:

    Hi,

    How would I write the formula to get the following:

    If A1 IS BETWEN 1 & 3 then "q1" however if between 4 & 6 then Q2, If between 7 & 9 then q3...

    Thanks!

    Mishaal

    • Hi Michelle,

      You can use a nested IF formula similar to this, beginning with the largest values:

      =IF(AND(A1>=7, A1<=9), "q3", IF(A1>=4, "q2", IF(A1>=1, "q1", "")))

      If there is no upper-bound limit, it can be simplified it to:

      =IF(A1>=7, "q3", IF(A1>=4, "q2", IF(A1>=1, "q1", "")))

  230. Stephanie says:

    Hi,

    Thank you in advance for your time. I am trying to creat an IF formula with a logic test that recognizes the most current date in the row and if the date is within 6 days of the todays date another cell will indicate NOT AVAILABLE

    So far I have IF(???>6;AVAILABLE;NOT AVAILABLE)

    I dont know how to create the date formula for the row only for a specific cell.

    Thank you

    • Hi Stephanie,

      You should use the MAX function to determine the most recent date in a row and then compare it with TODAY(). For me to be able to suggest the right formula, please clarify the conditions. I assume "within 6 days of the todays date" means within the next 6 days, right? And what shall the formula display if the most current date in the row is a past date? And what if it is more than 6 days in the future?

      • Stephanie says:

        Row A: Will show "Open" if the date entered in any cell in that row is more than 6 days from the current date or show "Closed" if ay date in the row is within 6 days of the current date (future or past).

        So far I have =IF(??-TODAY()>6;"OPEN";"CLOSED")

        Thank you

  231. Barry says:

    Hi Svetlana, It would be great if you could help, I think I'm nearly there but getting frustrated :)

    =IF(AND((E4-AG4>=5),50,0),IF(AND(E4-AG4<=-5),50,0))

    I'm building a sports sheet, whereby E4 is the actual spread and AG4 is the predicted spread. If the predicted spread is within +5 0r -5 then 50 points are awarded.

    Appreciate your help
    Kind regards
    Barry

    • Hi Barry,

      The correct syntax of your formula is as follows:
      =IF(AND(E4-AG4<=5, E4-AG4>=-5), 50, 0)

      And you can make it even more simpler by using the ABS function that returns an absolute value regardless of the sign:

      =IF(ABS(E4-AG4)<=5, 50, 0)

  232. erin says:

    Thank you Svetlana!!!

  233. Gayle says:

    Hi! I am trying to use an IF Formula to provide me a date result.

    =IF(H2=S, [Approval]+(7*[Estimated weeks from Start to Approval]),IF(H2=P, [Start]+(7*[Estimated weeks from Start to Approval])))

    I'm not sure what to enter at the end for the result to show a date.

    Thanks!

  234. Fred Odiboh says:

    =IF(AH5>69, AG5*5, IF(AH5>59, AG5*4,IF(AH5>49, AG5*3,IF(AH5>44, AG5*2,IF(AH5>39, AG5*1,IF(AH569) and so on.
    pls, can you help

    • Hi Fred,

      The formula looks good. You just need to finish it properly, by closing all IF statements. I.e. the number of closing brackets should match the number of opening ones. Also, in the value_if_false argument of the last IF, enter the value to display when none of the conditions are met - it's an empty string ("") in the example below:

      =IF(AH5>69, AG5*5, IF(AH5>59, AG5*4, IF(AH5>49, AG5*3, IF(AH5>44, AG5*2, IF(AH5>39, AG5*1, IF(AH5>29, AG5, ""))))))

  235. Ryan Lake says:

    HELP! :( Working on a sheet I have made a drop down in a cel and I would like the cel next to it to enter text if a certain word is selected in the drop down, is this possible?!?! I am not very good at explaining so I can try my best with whatever questions you have!

    • Ryan Lake says:

      For example, if the drop down menu is selected Oranges I want cel G to now have Bananas in it, but if from the drop down Grapes is selected I want cel G to now say Grapes, and so on and so on, honestly have 27 so far in the drop down and need an IF in G if something is in the drop down menu, is this possible?!?!

      • Hi Ryan,

        If you want a cell next to your drop down list to display the same value as currently selected in the drop-down, all you need is a simple formula like =$A$1 where A1 is the drop-down cell.

        If you want to display different values, then you should use nested IF functions, like this:

        =IF(A1="bananas", "bananas2", IF(A1="oranges", "oranges2", IF(A1="grapes", "grapes2")))

  236. Clarence says:

    hi , can you help with my sheet ? I can't translate it to codes in excel .this is how i read it "if e20 is less than j21 and d21 , i should get the cell with the highest value in f11 to f15 cells . thanks .

  237. Hi Clarence,

    Here you go:
    =IF(AND(E20<J21, E20<D21), MAX(F11:F15), "")

  238. Daniel says:

    =(D3/B3)if(B3="0"),D3,E3 I get error please help
    D3=hour B3=Sales E3=final number

  239. Roman says:

    Hi, im trying to place a formula that, according to a "word" I can get a number as true or false value.
    ex.: A1= Casa .......... B1= If=(A1Casa,"2","5"), something like that.

    Thanks in advanced. :)

  240. Pichan says:

    Hi,can you help me with formula if for this condition, if F3=A,D3=1,the result for H3=C3 & if F3=A,D3>1 the result for H3=-1 in 1 formula if. Thank you :)

    • Hi Pichan,

      Here is the formula for H3 as per your conditions. If nether condition is met, it will return a blank cell (empty string).

      =IF(AND(F3="A", D3=1), C3, IF(AND(F3="A", D3>1), -1, ""))

  241. JohnyG says:

    Hi,

    I have a formula that is returning "Recent" in cells that do not contain dates.

    =IF(C6>DATEVALUE("4/21/2014"),"Recent","")

    Cell C6 = "1 File(s)"

    What can I do to exclude cells that do not have dates?

  242. Donnie says:

    Hi Svetlana

    I would like to create in "IF" statement or an "ISBLANK" statement that says that if C2 is blank, populate from D2. If C2 has text, do nothing.

    =IF(C2 is Blank, Populate from D2) or =IF(ISBLANK(C2)=TRUE,D2,0)

    Thank you!

    • Hello Donni,

      You can use either of the below formulas:

      =IF(C2="", D2, "")

      If cell C2 is "visually blank" (including an empty string returned by some other formula, if any), the formula returns a values from D2, otherwise returns an empty string.

      =IF(ISBLANK(C2), D2, "")

      The second formula returns a value from D2 if cell C2 is absolutely blank (no value, no empty string).

      Please see the Excel IF examples for blank, non-blank cells section in this tutorial for full details.

  243. Adam says:

    Hi Svetlana,

    I need to create the following if statement.

    IF the member type in B6 = "TC" then take the date from B3 and + 10 Days, If the member type in B6 = "T2C" then take the date from B3 and + 15 Days.

    • Hi Adam,

      Here you go:
      =IF(B6="TC", B3+6, IF(B6="T2C", B3+15, ""))

      Please remember to change the format of the cell with the formula to the Date format so that is displays the date correctly.

  244. Derka says:

    Thank you for the article. I don't know if there is a function to do what I want, but here is basically what I am looking for.

    I have a list of items that I have set to display on a different sheet. I would like a large number of those items (those that start with a 4) to be combined into one cell with a different name in the same list (The 400 Series). Is there a function that will keep my list intact, but combine all the names that start with 4 into my new cell? I don't need a count of them by the way, just the list to transpose itself over as it is now with those specific cells pushed down into one.

    Thank you for the help.

    • Derka says:

      As a follow up and to complicate my question, I am hoping to establish a function to do this dynamically every day when I import new information. If I were doing it only once, I think I could just remove the "4" items and put in a new block, but this would become a timely process if I were doing it every day, because I have the list being dynamically generated, which would bring back all my "4" blocks with the new import each day.

  245. Faith says:

    What is the formula when my conditions are;

    If 0<= A < 10 the result is 3
    If 10< A < 15 the result is 2
    If 15 < A 20 the result is 0

    Assuming that the variable A is the value on a certain cell.

    Thanks

  246. Phillip says:

    Hi,

    I am currently working with a schedule, and I am trying to make an IF function that if I have a table that will show me who is working each shift, it will look at the schedule and pull the person that is scheduled at a certain time.

    An Example:
    If I have person A scheduled at 11:15 AM and I want there name to be pulled into the table which shows all the employees that work at 11:15 AM how would I do so. Currently the function I am trying to use (=IF(B13="11:15 AM", A13) is not working. B13 is representing the starting time of the shift, while A13 is representing the Employee name. I would greatly appreciate any help you can give on this matter, thank you!

    • Hi Phillip,

      When comparing dates or times in Excel, you have to use the DATEVALUE and TIMEVALUE functions, respectively, in order to convert a date or time unit in the text format to a serial number that Excel recognizes as a date / time. For example:

      =IF(B13=TIMEVALUE("11:15 AM"), A13, "")

  247. Toni says:

    I need help figuring out 2 calculations

    1.If cell is blank, then leave cell blank, but if cell has a value then add another cell.

    2.Total cells and then divide by cells that only have value. Example if there are 10 cells, but only 5 have value, you would divide by 5.

    • Hi Toni ,

      You can use formulas like these:

      1. =IF(A1="", "", B1)

      A1 is the cell you check for being blank/non-blank, and B1 is the cell you add if A1 is blank.

      2. =SUM(A1:A100)/COUNTIF(A1:A100, "<>"&"")

  248. Teame says:

    Hi There,
    I use this formula but nothing is coming out:
    =IF(B6>2),"3.86"
    what i need is if b6 is > 2 i need excel to put 3.86 and 3.99 if b6 is <= 2
    Thanks

  249. rukia says:

    I want to replace F2 with value in G2 if value in F2 is 0.

  250. KLEO says:

    how to create a subscript in an "if()" function? for example if(1<2,"FS","") the "S" should be in subscript. thank you!

  251. Nemar says:

    Please help me providing with encoding stating Week 1 Week 2 Week 3 Week 4 so on for the Year, to come automatically depending on Date in other cell.

  252. Tetay says:

    Hi can you help me formulate one formula for these situations:

    1. If the actual completion date (F9) is less than or equal to the target completion date (E9) then it's ON TIME
    2. If the actual completion date (F9) is greater than the target completion date (E9) then it's OVERDUE
    3. If the target completion date (E9) is greater than the date today (B5) and the actual completion date (F9) is unfilled it's NOT YET DUE
    4. If the target completion date (E9) is less than or equal the date today (B5)and the actual completion date is unfilled it's UNDONE

    My formula I have right now is:
    "IF(F9E9,"OVERDUE",IF(E9>B5,F9="","NOT YET DUE",IF(E9<B5,F9="","UNDONE"))))
    But it says I'VE ENTERED SO MANY ARGUMENTS

    Thanks!

  253. Khan says:

    Dear Svetlana Cheusheva

    Is this formula correct

    =IF(H2="#N/A", "No", "Yes")

    where #N/A is a result of vlookup which is applied in H2

  254. Olga says:

    Hello,

    I have the following formula so far:

    =IF(A1>=40,"40",""), If the value is less than 40 I need the result to be the exact value in the cell.

    For example if A1= 39.5 I need the result to be 39.5 not blank as I currently have it in the formula. What can I replace "" with to get this result?

    Thank you!

  255. Kea says:

    A B
    10 0
    Please help, B=A"Paid", B=0"Outstanding" and 0<B<A"Pending" Thank you!

  256. karthik regu says:

    if value entered B 1 40 to 45 the value C 2 36 and if enter 46 to 50 , the value C 2 37

  257. Anonymous says:

    Using the data in C16:C18, generate in Cell E16 the formulation that will check Cell C16 for a Y, an N or any other character and if this cell contains a Y the formulation will round the figure in Cell C18 (19.2589) to the number of decimals indicated in Cell C17 (3), if Cell C16 contains an N the formulation will not round the figure in Cell C18 and simply return the number that is in Cell C18 and if Cell C16 contains any other character, the formulation will return the error message “Invalid Character”.

    Would this be an IF function?

  258. Alynda says:

    Hi..I am currently working on sales report and using IF formula. However I encountered IF error message - "IF only takes 3 arguments but this is argument number 4". Appreciate your help on this....thank you

    • Hi Alynda,

      Yes, the syntax of the IF function allows for 3 arguments only. You can find the detailed explanation of each argument at the beginning of this article. If you provide more details about your formula, we will try to get it right.

  259. imran says:

    hi svetlana
    i am working as a planning officer in textile. i have to make/maintain multiple sheet. can you help me?

    imran

  260. ravi g. says:

    i want to make a mark sheet with result of different students, i want result fail if value=<34, then what will be the arguement, how can i write the cell range in formula. i have done it, but it is wrong! plz help

  261. Ernesto says:

    Hi Svetlana

    Im trying to make a dynamic sheet for car configurations according to track and weather conditions, not sure if possible specially if they are around 30 tracks or is limited to X number of tracks. The idea is like this

    A1= Track01, Track02, Track03, Track04, etc......
    B1= Dry, Wet

    C1= IF((A1="Track01" and B1="Dry";1;3)(A1="Track02" and B1="Dry";2;4)(A1="Track03" and B1="Dry";3;9)(A1="Track04" and B1="Dry";4;12)) etc.....

    Thanks in advanced :)

  262. Manolo says:

    I want to add the value of certain cells only if the value = 1.
    I have a list of guests to attend an event; I conditioned the cells so if they are attending and I type a 1 the cell goes green, 2= RSVP TBD and cell goes yello and 3= not attending and cell goes red.
    I want to know the TOTAL number of guests attending (I will have to add all the cells if the value = 1)
    I tried:
    =IF(C3:C18=1, SUM(C3:C18)) but that returns the addition of all the numbers in that range of cells, regardless if they are 1, 2 or 3)
    THANKS!

  263. Gibson C. says:

    Hi Ms. Svetlana,

    Can you help me on this?

    IF today's date, range from 1 to 15; my date input should be mm/15/yyyy in a cell.
    But IF 16 - 31; date input should be mm/30/yyyy.

    I have formula but not working properly:
    =IF(DATE(YEAR(TODAY()),MONTH(TODAY()), TODAY())>15, DATE(YEAR(TODAY()),MONTH(TODAY()),30), DATE(YEAR(TODAY()),MONTH(TODAY()),15))

    Hope you can help.

    Many Thanks!

  264. Jayant says:

    I wanted to use if function of changing last digit of 3 digit number.
    example: 102, 103, 104, 105, 106
    2 is admin
    3 is HR
    4 is Finance
    5 is research
    6 is field staff

  265. Harish yl says:

    If A1 in sheet1 contains "AAB00001-A3" and A5 has 45
    A2 in sheet1 contains "AAB00001-A4" and A5 has 54

    I want add 45 with 54 using IF function Comparing A1 and A2 with "AAB00001" which is there in other sheet and cell.

    Please help.

  266. Raghav Chaudhary says:

    dear all

    please clear the following detail.....

    stock Value RATE physical
    44 1872 43 44

    if physical and stock is equecl print the value 0 and physical value 45 into the rate value and physical value 43 into the rate, so clear the urgent basis.

    regard

  267. Kathleen says:

    Hi Svetlana,

    I have two problems I can't solve. I hope you can help me!

    I have a value in Column D. I want to subtract that value from 20. If the result is 0 or less, I want Column J to show 0. If the result of 20-D is greater than 0, I want to display that value.

    For example:

    The value in D4 is 22, 20-22 = -2, therefore J4 shows "0".
    The value in D4 is 14, 20-14 = 6, therefore J4 shows "6".

    Here is the formula I have in Column J: IF(SUM(20-D4)=0,),"0",(SUM(20-D4)

    It doesn't work.

    Also, I have a value in Column F. If the value in Column F4 is equal to 100, then Column L4 should show 5. If the value in Column F4 is between 90 and 99, Column L4 should show 4. If Column F4 is between 80 and 89, Column L4 should show 3. If the value in Column F4 is between 70 and 79, Column L4 should show 2. If Column F4 is between 60 and 69, Column L4 should show 1. If the value in Column F4 is equal to or less than 59, Column L4 should show 0.

    For example: The value in Column F4 is 87, therefore Column L4 shows "3".

    I don't understand how to write this formula either. I am new to formulas in Excel and I don't understand the syntax.

    • Hi Kathleen,

      Here is the first formula:

      =IF(20-D4<=0, 0, 20-D4)

      As for the second task, you need nested IF's here:
      =IF(F4=100, 5, IF(F4>=90, 4, IF(F4>=80, 3, IF(F4>=70, 2, IF(F4>=60, 1, 0)))))

      I hope these are the formulas you are after :)

  268. Julie says:

    Oh, dear! Can anyone please help?!?

    I have been working on finding a formula to work in a spreadsheet and can't find the correct one to produce the results I need.

    I want to say: IF C6=X,or C7=X, or C8=X, or C9=X, or C10=X, or C11=X, or C12=X, or C13=X, THEN C4=X.

    Every time I try using an IF statement or an OR statement, it doesn't work properly and it says my formula is broken.

    Does anyone know what the correct formula would be for this? I have spent hours using tutorials and working out formulas but still can't get this to work. ANY help would be greatly appreciated!!

    • Hi Julie,

      Try entering one of the following formulas in cell C4.

      If X is a text value:

      =IF(OR(C6="X", C7="X", C8="X", C9="X", C10="X", C11="X", C12="X", C13="X"), "X", "")

      If X is a number, say 1:

      =IF(OR(C6=1, C7=1, C8=1, C9=1, C10=1, C11=1, C12=1, C13=1), 1, "")

  269. Julie says:

    Oh, Svetlana! I am forever indebted to you!!! THANK YOU so very much for the formula! It worked perfectly!! I cannot tell you how grateful I am for your help!

  270. Kathleen says:

    THANK YOU Svetlana, this was exactly the help I needed!

  271. Nicolas Martinez says:

    I am trying to design a function to produce a "grade" based on a number of columns.

    There are three possible grades given: UNSAT, SAT, SUP

    There are eight graded sections(columns), each with one of the given grades above.

    I want the ninth section to produce an OVERALL grade based on the previous grades.

    SUP on 7 or 8 of the previous eight grades (columns)
    UNSAT on 2 or more of the previous eight grades (columns)
    SAT in all other cases.

    Thanks for any help you can provide!

    • Nicolas Martinez says:

      Never mind! I found the answer by using a mixture of IF and COUNTIFS Functions:

      =IF(COUNTIF(A24:G24,"SUP")<7,"SAT","SUP")

      This answer actually nullifies the requirement to have an UNSAT answer in the field, only because it was easier for me to count an UNSAT manually vice work the multitude of function. If i do find a better answer (to my own question haha) I will update it!

  272. Will says:

    Hi, looking to do the following:

    Cell C1 has mobile #
    Cell D1 has mobile # or is blank
    - we insert rows manually below each data entry which has values in C1 and D1 (throughout the sheet)
    - if there is an easier way, please advise

    I now want to have the value of the newly created row reflect the value of D1 if there is data in D1; but
    I want to be in a position to drag the formula through the whole sheet and there won't be rows inserted below data entries that doesn't have values in D1; thus
    I want those data entries to remain the same.

    My problem is that it creates a circular reference.

    Virgin sheet ex.

    A B C D
    1 Dave Scott 0845685841 0824865892
    2 Jill May 0725984287
    3 Jhon Snmit 0605493216 0915876431

    Manually insert row below person with 2 numbers
    A B C D
    1 Dave Scott 0845685841 0824865892
    2 xxx xxxxx xxxxxxxxxx xxxxxxxxxx
    3 Jill May 0725984287
    4 Jhon Snmit 0605493216 0915876431
    4 xxx xxxxx xxxxxxxxxx xxxxxxxxxx

    Thus I want to be able to do the following:
    Input a formula in column C to display the value of D in the above row or remain the same if D is blank

    What I tried, but gave me circular reference:
    In C2 =IF(D1"",D1,C2)

    The same will apply to the names, will also want to be able to populate the persons name in the newly created row for their second number:

    In A2 =IF(D1"",A1,A2)

    The result that we are looking for is:
    A B C D
    1 Dave Scott 0845685841 0824865892
    2 Dave Scott 0824865892 xxxxxxxxxx
    3 Jill May 0725984287
    4 Jhon Snmit 0605493216 0915876431
    4 Jhon Snmit 0915876431 xxxxxxxxxx

    Therein lies my problem, kindly advise

  273. Hardwareman says:

    I have a column of repeated names and a column of values against the names say C2:C101 (names) and I2:I101 (values). I have tried to work out a formula that looks for one of the names in column C and gives a total of all corresponding values in column I.
    I have tried variations of =IF(C2-C101="Walker, David","SUMI2-I101","0") but I just cannot get it to work.
    Can you help please.
    Thank You

  274. Hardwareman says:

    All is Ok, I have managed to solve the problem by using SUMIF.

  275. Hardwareman says:

    Thanks Svetlana
    I didn't see your reply but thank you for answering

  276. Claymore says:

    Hi I would like to ask how do I type in the formula box if for example I want it the multiplier to be like this:
    If the value ranges from:
    1 ~ 10 = 150
    10.1 ~ 50 = 120
    50.1 ~ 250 = 110
    251.0 ~ 500 = 100
    501.0 ~ 750 = 95
    751 ~ 1000 = 90

    If I have typed a value of 57, it should be 57 * 110 (since 57 is at range of 20.1 ~ 250 = 150 (150 as the multiplier))

    Please help.

    Thanks

  277. Parveen kumar says:

    Hii...

    I need a help, question is

    PF is 20% of Basic salary or Rs.1000 whichever is less

    Please give me answer as soon as possible.

  278. Mary says:

    I have an if function question I can't seem to find a similar question in your article. For example Tenant lease start D12 and lease end dates E14; has to give me the date in G14. The same date that is in D12. This tell me when I can start doing improvements. How is the if function written out?

  279. madhav says:

    hi I want to find a value if a1=less then 20 "80" more then 20 bt less then 40 then calculate(a1-20)*7)+80) bt a1 is greater then 40 then calculate (a1-40)*10)+230 please help me ma

  280. ZAFAR says:

    PER GRADE GRADE points
    66.5 C
    80 B
    90 A
    65 D

    if A=4 ,B=3.5 ,C=3 ,D=2 HOW CALCUATE (FORMULA)

  281. Abhijit says:

    Friends I want to a formula who auto calculate with given material with its value.

    Ex.

    If Material is Sand and its value 41....its divided by its net weight(12000)

    I want to just type different Materials Name like sand and its auto divided with weight.

  282. Phil' says:

    =IF(CV2<=28,"Poor", IF(28<CV2<=42,"Borderline","Acceptable"))
    I wrote the formula above but it's only selecting "Poor" and "Acceptable", leaving out "Borderline" Any help?

    • Hi Phil,

      Regrettably IF cannot understand expressions like 28<CV2<=42. You have to use an AND statement is this case:
      =IF(CV2<=28,"Poor", IF(AND(CV2>28, CV2<=42),"Borderline","Acceptable"))

      But in fact, checking for CV2>28 is superfluous and you can put it simply as:

      =IF(CV2<=28,"Poor", IF(CV2<=42,"Borderline","Acceptable"))

  283. Zhao rz says:

    =IF(B2="delivered", TODAY(), "")

    I use this formula to generate delivery date, but i found that the date will updated to the date of next day... So how to maintain the date there for me to check the delivery date few days later? thanks.

    • Zhao rz says:

      hi..
      i have just got answer from your previous post that

      "How to convert date to text using Excel TEXT function and no-formula ways"

      ...ha, it`s helpful, thank you very much, but i am not sure whether it will be updated tomorrow

  284. Fitz says:

    Hi, working on an excel spreadsheet for invoices.
    Column A is quantity, b & c are description, D is unit price and E total

    I need a formula that calculates A*D in column E but remains blank when column A is blank

    Thank you

  285. Eric says:

    Hi, I am trying to write a formula that pulls in a number of 1, 5 or 10. The numbers in cell A1 range anywhere from 1 - 25. I presently have it set up as follows:

    =IF(AND(A1>=1,A1=5,A1<10),5,10))

    The formula takes, but it always returns the number 10 in every instance. Can't figure this out. Hoping you can help.

  286. Eric says:

    Hi, I am trying to write a formula that pulls in a number of 1, 5 or 10. The numbers in cell A1 range anywhere from 1 - 25. I presently have it set up as follows:

    =IF(AND(A1>=1,A1=5,A1<10),5,10))

    The formula takes, but it only returns the number 10 in all instances. Help!!

  287. Eric says:

    For some reason, every time I submit my question, it cuts down on how I originally wrote my formula.

    =IF(AND(A1>=1,A1=5,A1<10),5,10))

    This will only return the number 10.

    • Hi Eric,

      Our blog engine often mangles formulas in comments, sorry for this. Could you please describe your conditions in words, so that I can understand the logic? So, the formula should return:

      5 if ?
      10 if?
      1 if?

  288. Zhao rz says:

    Dear Svetlana,
    I tried =TEXT(O2,"dd-mmmm-yy") and which (O2)=IF(B2="delivered", TODAY(), "")
    it workeed yesterday and (cell P2) shown 12-May-15 but it updated to 13-May-15 today morning.
    So how to lock the date(cell P2) at 12-May-15?

    thank you.

    • Hi Zhao,

      Regrettably, it's not possible to lock the date using formulas. You either have to replace the formula with its value each time manually (Copy > Paste Special > Value) or write a VBA script that automates this.

  289. Harish DL says:

    How to set if condition for following:

    =ROUND(VLOOKUP(G6,'2012 IAM Table raw'!A$2:D$122,4,FALSE)*((1-VLOOKUP(G6,'2012 IAM Table raw'!A$2:E$122,5,FALSE))^F6),3)/1000

    this round value will return some value, if values is not return need to set the field value to 0 (zero)

    • Hi Harish,

      You can try to wrap your VLOOKUP function with IFERROR like this:

      =ROUND(iferror(VLOOKUP(G6,'2012 IAM Table raw'!A$2:D$122,4,FALSE) * ((1-VLOOKUP(G6,'2012 IAM Table raw'!A$2:E$122,5,FALSE))^F6), 0), 3)/1000

  290. Siddharth says:

    Hi Svetlana,

    What is the formula to highlight non-date cell on particular future dates?

    e.g.

    A1 cell contains Name and I want to highlight this name between 20 May 2015 to 22 May 2015.

    Can you please, help me with the formula for this?

    Regards,
    Sid.

  291. Lindsay says:

    Hi

    Need to insert if formula where there are four dates (one in each column) and need a yes answer if any of the dates are greater than one date but less than another date. eg. if any of the four dates were greater than 01/06/15 but less than 30/06/15 I need the answer to be yes

    Thanks. LP

    • Alexander says:

      Lindsay,

      Please use this formula:
      =IF(OR(AND(A1DATEVALUE("01/06/15")), AND(A2DATEVALUE("01/06/15")), AND(A3DATEVALUE("01/06/15")), AND(A4DATEVALUE("01/06/15"))), "Yes","")

      Where A1, A2, A3, A4 are cells with the dates.

  292. Odirlei Moreira says:

    Hi Guys, how are you?

    I imported data from TXT file when the field BDATE came like below:

    09/09/2013
    09/09/2013
    09/09/2013
    09/09/2013
    30/12/2011
    14/03/2013
    24/09/2013
    24/09/2013
    30/12/2011

    This format is British style (DD/MM/YYYY), when I convert to American style (MM-DD-YYYY) the date that begin > 12 were not converted.
    Somebody knows what to do?

    Thanks!

  293. Chris says:

    Hi,
    I have two membership type groups; member and non-member (column A). I have registration start dates (column B) and registration end dates (column C). I need to confirm that the start and end dates for registration fall within the acceptable date range for both members and non-members. Members range (6/1/15 – 8/31/15); Non-Members (6/8/15 – 8/31/15).

    Thanks!

  294. James says:

    What if I want to say if cells F1, G1, and H1 are blank, I1 should be blank. If F1, G1 and H1 have numbers, then I1 should be F1+G1+H1.

  295. WENG says:

    I NEED YOUR HELP IN THIS FORMULA:

    =IF(K26="CHEMICAL TANKER",IF(AND(K26>=0,K26=5000,K26=10000,K2620000,550))))),IF(K26="GENERAL CARGO",IF(AND(K26>=0,K26=5000,K2610000,490))))

    THE ANSWER IS= #VALUE

    IS THERE ANY POSSIBILITY TO COMBINE MORE THAN ONE IF STATEMENT IN ONE CELL?
    I WANT TO ADD OTHER IF CONDITIONS IE,"BULK","CARGO" AND SO ON.

    IS THERE ANY LIMITATION FOR THIS CONDITION? TQ

  296. Cameron says:

    I have a tricky question...I want sheet 2 to return an if statement that calculates on sheet 1.: =if(MaterialBreakdown[SARA Title 313}="*YES", MaterialBreakdown[Material Name], NA. That is the formula I tried to use, but it gave me an error. I want all materials in column A to list in another sheet if they have yes in column C. I am really bad at explaining this!

    all help appreciated!

  297. Ana says:

    i cannot figure the formula out help!!!
    In cell F20, enter an IF function that tests whether the order quantity in cell E20 is greater than zero. If it is, return the the charge for this item, which is the value of cell E20 mulitplied by cell D20. Otherwise, return a space by entering " " (that is, double quote, space, double quote). Autofill this formula into the range F21:F25.

    • Hi Ana,

      Here's the formula for F20:
      =IF($E20>0, $E20*$D20, " ")

      Simply copy it down to other cells in column F:
      - select cell F20;
      - move the mouse cursor at the lower right-hand corner of the cell and you will see it changing to a plus sign (fill handle);
      - click the plus and drag it down to fill other cells with your formula.

  298. helpmekeepmyjob says:

    i need a formula to display a number (2) if the cell says "yes" and display a (4) if the cell says "no"....
    can anyone help?

  299. Lana says:

    Can you help me with this formula:

    =IF(AND(AE303="",AK303=""),Incident),IF(AND(AE303""),AK303="",(NOW()-(AE303))),(DATEDIF(AE303,AK303,"D")))

    I am trying to have the formula:
    (1) if cell AE303 and AK303 are both blank put in the word "Incident", but
    (2) if cell AE303 has a date in the cell and AK303 is blank insert number of days between AE303 and Today, but
    (3)if both cells have dates, subtract them and tell me the # of days between

    • Hi Lana,

      Try this formula:

      =IF(AND(AE303="",AK303=""), "Incident", IF(AND(AE303<>"",AK303=""), TODAY()-AE303, IF(AND(AE303<>"", AK303<>""), DATEDIF(AE303,AK303,"D"))))

      Just keep in mind please that DATEDIF requires the start date (AE303) to be always less than the end date (AK303), otherwise a formula will return the NUM! error. An easy workaround is simply subtracting one date from the other in the last value_if_false argument:
      AK303-AE303

  300. Monica says:

    I need help with a formula using dates:
    What I am trying to accomplish is if cell G2< 7/1/14 then insert 7/1/14 but if not insert the date that is in G2.
    =IF(G2<DATEVALUE("7/1/2014"),"7/1/2014",G2)

    Thanks for any help,

    • Hi Monica,

      Your formula is correct. If it returns a serial number rather than a date, you should simply apply the Date format to the cell (press Ctrl+1 to open the Format Cell dialog and select the Date format you want).

  301. Vinath Shetty says:

    Please advise how to use below formula in excel

    =if(L2>60,"Active"),if(L2<60,"Critical"),if(L2<0,"Overdue")

    • Hi Vinath,

      You'd rather put it like this:

      =IF(L2>60,"Active", IF(L2<0,"Overdue", IF(L2<60,"Critical", "")))

      Just pay attention, please, that the formula returns an empty string if none of the conditions is met, e.g. if L2=60.

  302. Jon Philipson says:

    I am trying to change a formula from =IF(H2="2-bdrm/2-bth",$K$3,IF(H2="2-bdrm/1-bth",$K$4,IF(H2="1-bdrm/1-bth",$K$5,))) which determined the average rental price to a formula that determines the what the highest rental price is for each size apartment. Anyone that can help I would greatly appreciate

  303. Brian says:

    I have my fantasy baseball spreadsheets. There are in two different files. File 1 has all of my palyers noted in column A and their positions in column B.

    I am looking to transfer the Info from Lets say "biils Row C" column A and B in my master file to "bills Row C" A and B in the updated spread sheet. I have the following formula that works on one line. but how do I get it to search the entire master sheet and pull that data over. The first C3 file is my updated sheet

    =IF(C3='[RE-ENTRY AS OF 5-5-15.xlsx]Hitting'!C3:C500,'[RE-ENTRY AS OF 5-5-15.xlsx]Hitting'!B3)

  304. ajay says:

    i have to do this in excel ,guyz help
    if b=250 value is 15
    if b=260 value is 12

  305. Eden says:

    Hi,

    I made a table for a high, medium,low with corresponding digits.
    Like for example,if the probability of failure is 1(rare) and the impact rating is low, then the result should be low.
    Now, my problem is how do i connect the table to my another sheet.

    please help

  306. Tiffani says:

    Please help me!

    For my job, I work a lot in excel. I have a spreadsheet to calculate per diem for drivers to receive on their paychecks. I have seven blank "date" cells. I'm wanting an if/then function so that if I write a date in one of the blank date boxes, it will automatically put $20.00 in the corresponding "per diem" box. Likewise, if the date box remains blank, the per diem box remains blank.

    Please help.

  307. Hi Tiffani,

    Supposing your dates are in column A, you can put the following formula in the "per diem" box:

    =IF(A1<>"", 20, "")

    The formula will return 20 if cell A1 in not empty. To format the returned number as currency, select the cell with the formula, press Ctrl+1 to open the Format Cell dialog, select "Currency" on the Number tab and choose the currency format you want.

  308. Tracy says:

    If Column A contains Column B then Yes if not then No

    Can the formula take into account the case sensitivity as well?

    Column A Column B

    \\CCBF0FP21\DCLabels Doejj
    \\CCBF0FP21\DCLabels$ Doejj
    \\CCBF0FP21\DCReports$ Doejj
    \\CCBF0FP21\Direct$ Doejj
    \\CCBF0FP21\Review$ Doejj
    \\CCBF0FP21\DEV$ Doejj
    \\CCBF0FP21\Shipto Doejj
    \\CCBF0FP21\shipto$ Doejj
    \\CCBF0UD2A\UD1$\DOEJJ Doejj

    • Tracy says:

      Please Help :)

    • Tracy says:

      Sorry - the sample data on here is not displaying as 2 columns. To clarify:
      Column A
      \\CCBF0FP21\DCLabels
      \\CCBF0FP21\DCLabels$
      \\CCBF0FP21\DCReports$
      \\CCBF0FP21\Direct$
      \\CCBF0FP21\Review$
      \\CCBF0FP21\DEV$
      \\CCBF0FP21\Shipto
      \\CCBF0FP21\shipto$
      \\CCBF0UD2A\UD1$\DOEJJ

  309. Tracy says:

    Column B
    Doejj
    Doejj
    Doejj
    Doejj
    Doejj
    Doejj
    Doejj
    Doejj
    Doejj

  310. Tracy says:

    There are also hundreds of different names in my data so Column B will not always be the same length. :)

  311. Tracy says:

    I figured it out in case anyone ever has the same question :-)

    =If(ISERROR(MATCH(B1,A1:A1,0)),"False","True")

    Thanks!

  312. lachlan says:

    hi, i am currently trying to generate a dashboard for my company and am having trouble inserting data into particular categories. there is a general data input sheet and then the dashboard. on the dashboard there are 3 separate categories with about a dozen free rows beneath for data to be inserted. i wish to insert data from the general data input section based on value of dates given etc. eg if the date is now then insert under category 1 if more than 2 years away, category 2 etc. could you please suggest any ways that i can do this the easy way etc, I'm trying to avoid inserting the data manually under each separate category as there will be a lot to enter. thanks in advance (:

  313. ronnie says:

    assit me with nested if statement of a traffic robot set up with out come or result of GO,Stop and prepare to stop.

  314. Roy says:

    Hi I am new to Excel and i am trying to format a mailing list that has been sent to me. basically the query is
    if f2 is blank then copy the contents from E2 into F2. blanking out E2 when done. i hope that is clear any help would be appreciated.

    Thanks

  315. Amanda says:

    Hello

    I am trying to work out a formula for a table of rental charges for rooms in a house.

    Column E is 'date from' and Column F is 'date to' and column H is number of days. Rent runs from the 19th of each month.

    I used the following formula to work out rent cost for the number of days they were there which is basically saying

    'if the 'to date' is 19/05/15 then return the monthly rental value as shown in cell C14, but if it is not 19/05/15 then use the number of days multiplied by the daily rental value to give me an amount'

    =+IF(F4=DATEVALUE("19/05/2015"),C$14,0)+IF(F4DATEVALUE("19/05/2015"),(H4*E$14),0)

    I realised though that this only works if the month starts on 19/04/15. I have one tenant that moved in on 08/05/15 and i therefore need to adjust the formula to say that if column F= 19/05/15 and column E=19/04/15 then return the monthly rent but if F doesnt equal 19/05/15 and e doesnt equal 19/04/15 then use the number of days multiplied by the daily rent.

    Is this possible as when i adjust the formula with an AND it comes back as invalid.

    Any help would be very much appreciated!

    Thanks in advance

  316. Paul says:

    Hello, Excel will not accept the following formula:
    =IF(F3= "yes", [G3*1.1], [G3])

    All I want it to do is return Cell G3 x 1.1 if cell F3 = Yes, else just cell G3. What is wrong with my format?

  317. b1n1 says:

    hi, how to do this, ex:
    22.7 auto increment to 23 or 22.3 to 22 ?
    if 22.5 till 22.9 to be 23 or 22.1 to 22.4 to 22 ?

  318. StephenP says:

    HI!
    I'm trying to use a certain cell name from a list in my IF formula, but it will not allow me to use the character within the name without trying to use the characters function. is there a way to override this? Thank you!

    ex. =IF(D5=3Y) it's wanting to use that Y as a YEAR function and not allowing me to use it simply as a Y.

  319. janay says:

    I am having a problem with the if function with this question9. cell C10 is equal to 0.
    a. If this condition is true, the current cell should be made empty (that is, equal to "").
    b. If this condition is false, the current cell should display the result of multiplying cell C10 (hours) by cell D7 (hourly wages). Use a relative reference to cell C10 and an absolute references to cell D7 so that the formula can be copied to other cells

  320. NIRAV says:

    Hello,
    i want to make one condition like
    party payment days are going between 70 days so that's mention in excle
    inv. date amount 30-60 days ,60-90 days, above 90 days
    1 1/4/15 50000 0 50000 0
    pls give me that type of formula ,
    i use one formula =if(now()-datevalue(b1)<30,+c1,0) but it's now working
    pls give me other formula

  321. NIRAV says:

    sorry it's not working

    • Hi NIRAV,

      I am not quite sure what +c1 means. Anyway, if B1 is a date rather than a text value, the DATEVALUE function is not needed:

      =IF(NOW()-B1<30,C1,0)

      If B1 is a text value, then the following formula works fine:

      =IF(NOW()-DATEVALUE(B1)<30,C1,0)

  322. Jon Philipson says:

    I am trying to change a formula from =IF(H2="2-bdrm/2-bth",$K$3,IF(H2="2-bdrm/1-bth",$K$4,IF(H2="1-bdrm/1-bth",$K$5,))) which determined the average rental price to a formula that determines the what the highest rental price is for each size apartment. Anyone that can help I would greatly appreciate?

  323. Raj says:

    Hi,

    I need a solution for below,

    Given data
    Name Subject marks
    Raj FFV01 70
    Vino FSO03 80

    In a desired cells, I need a formula that allows me to pick the Raj's "Subject" only and mark in the next cell. whereas if it's vino, Cell should be blank.

    Please revert if quest not clear.

    Thanks for your help in advance.

    • Hi Raj,

      You can use a VLOOKUP formula similar to the below one:

      Subject: =VLOOKUP("raj", A1:C100, 2, FALSE)
      Mark: =VLOOKUP("raj", A1:C100, 3, FALSE)

      Where Name is column A, Subject is column B and marks are in column C.

  324. Ross says:

    Hi,

    I am trying to create an IF formula on a date cell that states if the date is before or equal to 01/02/2015 then give me 'A' otherwise give my 'B' (ie. if the date is passed).

    I have used =IF(D7<=01/02/2015,"A","B") but it does not work and gives me 'A' for dates before and after 01/02/2015.

    Hope you can help.

    Many Thanks

  325. Madhu says:

    Hi,

    I need to compare the data between two xls sheets(cell to cell validation).

  326. Jack Henderson says:

    Hi Svetlana,

    I'm having difficulty with making a formula.

    What I want, is that if A1 is not Blank, that C1 Prints "N/A", but if C1 is not Blank, then B1 and A1 Print "N/A".

    I currently have: =IF(NOT(C1=""),AND(A1="N/A",B1="N/A"),IF(NOT(A1=""),C1="N/A","Error"))

    (If not possible please let me know as I've been having trouble with these recently, thanks)

  327. Anwaar Ashraf says:

    How to put a single formula for the following;
    If F31 is '0' then the value should be of H12...
    and if F31 is more than '0' then the value should be the value of F32.

    • Hi Anwaar,

      You can use a nested IF formula like this:
      =IF(F31=0, H12, IF(F31>0, F32, ""))

      • Anwaar Ashraf says:

        Thanks a lot!☺

        • Anwaar Ashraf says:

          Good morning!
          Now could u also help me out for the following:
          I have different ranges of a quantity and one single cost for that particular range.
          For Ex. 5000 to 10000 = Rs. 120000
          11000 to 20000 = Rs. 115000
          21000 to 30000 = Rs. 100000
          31000 to 40000 = Rs. 98000
          and same pattern continues for further more values...

          request for early reply...
          thanks in advance

          Anwaar

  328. Joe Chambers says:

    Hi,

    i want a formula that will not show data on another workbook if the column is blank eg

    yes no
    \
    \
    \
    \
    \
    \

    i only want it to show the yes column on another tab but dont want any space between the date. Is this even possible using this function? I know its possible from using filter but dont want to do this

  329. Doug says:

    Good afternoon, I am trying to create a simple spreadsheet calculating a price per square foot value based on an optional selection of option A (wood material)or option B (carpet, which would be translated to square yards). both options A & B will also need to be increased by 20%. If I have all the correct numbers in the spread sheet, why cant I apply an if then statement to the column / cell I want to create the equation for? I am trying the following.
    =IF(H7=Option A,"((F7*D27)*1.20))",(H7=Option B,"(((F7*.111111)*D28)*1.20))"
    F7 represents the square feet
    D27 represents the Price
    H7 would be the field I would like to type option A or option B into
    thank you for your help.

  330. JUHIL says:

    I WANT CALCULATE LIKE THIS:
    IF A1 Grater than date 31-05-2015
    than A2*12.36% otherwise A2*14%
    thats it but formula not giving me correct value
    so Plz help me on this
    IN ONE CELL
    =ROUND(IF(A1>"31-05-2015",A2*14%,A2*12.36%),0)

    AND I ALSO TRIED IN THE PLACE "31-05-2015" TO DATEVALUE("31-05-2015")
    BUT NOT WORKING.
    IT SHOWS #VALUE

    • JUHIL says:

      YES MY FRIEND I FOUND MY SOLUTION AND IT WORKS
      LIKE THIS
      =ROUND(IF(T8>=DATEVALUE("01-06-2015"),E8*14%,E8*12.36%),0)

      THANKS

      AND DOING GOOD JOB (GENIUS WORK)

  331. MARRIAM says:

    HI! I HAVE TEXT INPUT FOR A2 TILL A100, EACH TEXT INPUT IS EQUIVALENT TO A NUMBER, EX, IN A3 (LC) AND IN B3 I PUT THE EQUIVENT IS 0.75 , IF I PUT A FREE TEXT IN C1 (LC) I NEED TO DISPLAY THE EQUIVALENT IN D1.
    A B C D
    1/ BS- 1 LC 0.75
    2/ NC- 1.75
    3/ LC- 0.75
    4/ WL- 0.50

    EX ABOVE : I INPUT LC IN C1 , I NEED TO DISPLAY THE EQUIVALENT WHICH IS 0.75 IN D1 ( WHICH IS FROM B4) I WANT IT TO DISPLAY AUTOMATIC ONCE I PUT ANYTHING FROM COLUMN B IN C

    NEED YOUR HELP THANK YOU

  332. Marlene says:

    Hello, I would like to increase each numerical value by $100 in every cell in a very large table that has a number, but if there is text in the cell, then I would like to simply copy the text from the call. Can this be done with the IF function?

  333. Lynette says:

    Hi Svetlana
    =IF(A1="Active",AVERAGE(B1),0)
    The above formula works for one cell, of course, but I need to construct it to work for a range of cells.
    If "Active" appears in column A,(A1:A9), then I need to calculate the average of all numbers in corresponding cells in column B, (B1:B9). Do I need to nest a VLOOKUP into this IF statement? Either way, can you please help?
    Many thanks :)

  334. sonam says:

    I want to use IF to discriminate between individual turtles! I have drop downmenus describing each shell segment, e.g. box A segment one has a mark/ is white, box B the mark is single/double, Box C it is shaped like a circle, square, line, cone, other..then this combination of shell segment descriptions means the turtle is number 32. (All turtles have individual markings)
    How can I use if to return a specific individual identifier, rather than a yes/no, true/false answer?
    thanks for your comments in advance

  335. Lynette says:

    I found another way to find my average % completed:
    =IF(O5>0,(SUMIF(F9:F15,"Active",I9:I15)/O5),"100%") where O5 equals total active students. This one stumped me when there were no active students. That is O5 is zero. Anyway, it works!

  336. MattMN says:

    Hello Svetlana,

    I am not sure if I am doing this correctly. I would like to check in a row in the columns A through D for the keyword "PLAN". If it is present the result would be "Yes" and if not present for the cell to remain blank. I tried this function but it does not work: =IF(ISNUMBER(SEARCH("PLAN",A2:d2)),"Yes","")

    Thank you,
    Matt

  337. GarthL says:

    Hi there.
    I would like to highlight a row based on a certain cell being a value greater than 0. ie. if cell H8>0 "Highlight row 8 (or a range of cells)

  338. Agila Mary says:

    Hi,
    I wanted to know how to do the below scenario in Excel using if function eg
    I have mentioned no. of days worked in a month cell(K1) and no. of working days in month another cell (J1),if K1<j1 then there is value mentioned in I1 which is got using this formula =ROUND(L3*12/100,0) or else 1560

  339. Shankar Salgaonkar says:

    Hi,
    Cani use If function for different criteria,actually i want to do ageing of invoices i want to know since how many days the invoice is pending outstanding.There are different no of days for each invoice i want to know the days bracket. i want the formulae as per below for eg.If cell A1306090,"90-days"

  340. sharfi says:

    hi Svetlana Cheusheva,
    can you please guide me to make a farmula for this calculation
    IF A16 and 9 and <12 than * 450
    hope you understand what i want in result like i need to multipuly the B1 with 150 if A1 is less than 6 and if it is more than 6 and less than 9 than with 300 and if A1 is more than 9 and less than 12 than multiply B1 with 450 and if A1 is more than 12 than multiply B1 with 600
    please rply ASAP

  341. Josephine says:

    Hi Svetlana,

    Could you please help me on below ?
    I have 5 supplier, SupplierA have 10 purchasing order (eg. PO#0001 - PO#0010), PO no. 0001 have split to 5 transactions with different amount.
    How do i make a formula to find out total amount for each PO follow by each supplier ?
    Thank you in advance for your kind advise.

  342. Richard says:

    how about i need something less than 50 turn into yellow color word, less than 0 turn into red color word.

    Is that possible?

  343. Ahmadh says:

    Can u tell a formula for this, using if condition.. If grade is PP, credit point multiply to 1. If grade is CR, credit point multiply to 2. If grade is DN, credit point multiply to 3. If grade is HD, credit point multiply to 4.

    grade credit quality points
    PP 12 12*1
    CR 12 12*2
    DN 12 12*3
    HD 12 12*4

  344. April Burmeister says:

    I would like to create an IF formula to calculate the following and can't seem to get it right. If Cell L71 is empty, leave it blank, if it is Y, put Can Be Deducted in Cell M71, if N, put Can Not Be Deducted in Cell M71.

    I keep getting it to only say either Can Be Deducted, or Can Not Be Deducted, It wont do both for me. This is what I used =IF(ISBLANK(L71)=TRUE,"",IF(ISTEXT(L71="Y")=TRUE,"Can Be Deducted"=FALSE,"Can Not Be Deducted"))

  345. Patcha says:

    Hello,

    I am trying to see how many people made a gift last year that is less than this year, and the difference is greater than $500, then get the sum total of all those records. Any help is appreciated. Thank you!

    =IF(O1=N1,"Yes","Difference noted of "&O1-N1>500)

    • Hi Patcha,

      To output "yes" and "Difference noted of O1-N1>500" messages, you can use the following formula:
      =IF(O1=N1,"Yes", IF(O1-N1>500, "Difference noted of O1-N1>500", ""))

      To find the total of ">500" records, use the following array formula (remember to press Ctrl+Shift+Enter to complete it):
      =SUM(--((O1:O100)-(N1:N100)>500)*1)

  346. Raul Orezzoli says:

    Currently I have a formula:
    =If(k2"",k2,if(l2="",date92015,5,29)l2

    Need to add one more field = I2, that contains these five values
    Regional or
    Panama or
    Costa Rica or
    Colombia or
    Singapore
    Please advise of how to handle these additions

  347. Raul Orezzoli says:

    Currently I have a formula:
    =If(k2"",k2,if(l2="",(date92015,5,29)l2
    Need to add one more field = I2, that contains these five values
    Regional or
    Panama or
    Costa Rica or
    Colombia or
    Singapore
    based on these 5 fields date will be dirrent
    Please advise of how to handle these additions

  348. Wesley Warangi says:

    I hv worked out some formulas but need to get it right. I have data on the above 5 cells (a5 to a9) and in texts-company names.
    I have values at the bottom cells (b5 to b9). i have to get the lowest value in b5 to b9 and it is $1. it is found under company XXX iof cell a7 and is b7. Now i have to put the company name next to cell b11 because cell b10 is value of comp XXX in cell a7. for example, after i am thru entering values from b5 to b9 cell b10 will hav $1 while b11 should have company name XXX in it.
    Pls help me on this project.

  349. cheyenne toups says:

    i need a column to change based on the choice chosen from a drop down menu in another column.

  350. Dan says:

    Hi!

    I'm trying to find a formula that returns Yes or No if the cell format is a Date or text.Is there a formula that can identify if the cell is a date or text??

    Example:
    6/12/15 Yes
    Complete No

  351. Richard Ling says:

    Why i can't write this?

    =SUM(IF(AND(J1012),(M10-K10+(L10-J10)*60-60),IF(AND(J1016),M10-K10+(L10-J10)*60-15,M10-K10+(L10-J10)*60)),IF(AND(J1112),M11-K11+(L11-J11)*60-60,IF(AND(J1116),M11-K11+(L11-J11*)60-15,M11-K11+(L11-J11)*60)))

    there have another two need to add up.

    • Richard Ling says:

      =SUM(IF(AND(J1012),(M10-K10+(L10-J10)*60-60),IF(AND(J1016),M10-K10+(L10-J10)*60-15,M10-K10+(L10-J10)*60)),IF(AND(J1112),M11-K11+(L11-J11)*60-60,IF(AND(J1116),M11-K11+(L11-J11*)60-15,M11-K11+(L11-J11)*60)))

  352. Richard Ling says:

    L10 12 , L1016

  353. Raul Orezzoli says:

    I have work with formulas previously but I need to get this formula correct:

    =IF(K2"",K2,IF(AND(L2="",I2="Regional"),DATE(2015,5,29),""))

    How to write a nested if from above when :

    I2=Panama date(2015,7,29)
    I2= Singapore date(2015,6,8)
    I2= UK date(2015,10,8)
    I2= Mexico date(2015,6,10)
    I2=Italy date(2015,11,22)

    Thank you for your help

  354. mark says:

    A B C D E
    1 Date 3X5.5 6X5.5
    2 03-23 6X5.5 345 345
    3 03-24 3X5.5 580 580

    please help, thanks!

  355. Anwaar Ashraf says:

    Good morning!
    Now could u also help me out for the following:
    I have different ranges of a quantity and one single cost for that particular range.
    For Ex. 5000 to 10000 = Rs. 120000
    11000 to 20000 = Rs. 115000
    21000 to 30000 = Rs. 100000
    31000 to 40000 = Rs. 98000
    and same pattern continues for further more values...

    request for early reply...
    thanks in advance

    Anwaar

  356. Julianna says:

    How do I calculate days using info in 2 separate columns but tell the formula to only calculate it using the column that has a value in it?

  357. Alaine says:

    Good Day! Please help.

    I'm trying to get a total of the amount of days used in a month in a numerical form (to be used for a running total formula). The days are either full days and half days used, but the input is in mixed form.

    This is what I tired but it didn't work: =IF((OR(B5:AC5=F,"1",B5:AC5=0.5,".5"),SUM(AE5:AE5))

    Thank you so much for your help.
    Alaine

  358. T PATEL says:

    I AM TRYING TO GET A CATEGORY LIKE FOLLOWING.
    123 42 HR
    456 52 MR
    258 50 HR
    159 55 MR
    756 70 LR

    HELP TO SOLVE

  359. Cindy says:

    All I want is D33 to return 100 if B33 is Yes and 0 if B33 is No. So I tried =IF(B33="Yes",100,0) but I keep getting a #NAME error. What am I doing wrong?

    • Hello Cindy,

      The formula is correct, given that B33 is always either "yes" or "no".

      In Microsoft Excel, the #NAME error occurs when Excel cannot not recognize the function's name. Hardly someone can misspell IF's name, but maybe you are using a non-English localization of Office?

  360. Muhammad Umer Amin says:

    Hi,

    I am trying to use IF function with multiple conditions but I am not getting the results I want to. Some times it works on one or two values but as I change the values to validate the formula it start making wrong outputs.
    So far I made this formula;

    =IF(C7>=70,"In Range",IF(C7<=100,"In Range",IF(C7<=69,"Low Alkalinity",IF(C7<=59," Too Low Alkalinity Can damage Plaster, Vinyle, Grouting etc",IF(C7<=49,"Danger Zone can't control pH levels",IF(C7=110,"Scaling can happen any time",IF(C7>=150,"HIGH Alkalinity",IF(C7>=200,"Pool equipment damaging",IF(C7>=250,"Stop Bathing in Pool HIGHLY DANGEROUS"))))))))))

    safe range is between 70ppm to 100ppm and I am trying to get remarks according to findings if findings are getting as low as 70 it should show relative comment in remarks column and if finding are more than 100 it should show accordingly or else if its in range it should say "In Range" in Remarks... Please some one help I need this one. Thanks in advance .....

  361. dave says:

    I have two worksheet, in the first one it comes only one column with electronic component designators(example R3-resistor), in this column for each designator(resistor, capacitor or diode), corresponds a row. however in the other spreadsheet i have like more than one designator(the same designators as the first one) and it corresponded part number. what i want is a formula which compares designator and give me the part number in the first column. thank you

  362. Tien says:

    how can i right a formula if i want result of that cell is equal or less than zero to return zero value?

    Thank you,

    Tien

  363. Zali Qifty says:

    Hi Svetlana,

    i have this issue with my formula:

    D1 : due date
    E1 (value) : =NOW()-D1
    F1 : open or closed

    what i'm trying to do is, when i choose F1 as closed, i would like to have E1 value to be '0'. this is my formula which is still in error : =IF((NOW()-D1),(F1=Closed)),'0'.

    please help,thanks in advanced!

    • Zali Qifty says:

      i managed to find the error and fix the formula. Here is the fixed formula =IF(F1="Closed","0",NOW()-D1). =)

  364. Satish says:

    Respected Mam
    Actually we have some data base sheet in excel and we want to get a particular data from the sheet as we have "Ch. No. 000122 issued to Mr. Dilip I317018893 khargone(Cancelled)" in row and we want to get only "I317018893" form this data so please guide me the proper formula of excel so that I can easily do it.

    Thanks and regards
    Satish

  365. Jamil says:

    Hi Svetlana,

    Can you please resolve the issue with my formula. I am trying to enter multiple logics with multiple values.

    =IF(D1>22,"This is a VERY HIGH risk study"), IF(D1>17,"This is a HIGH risk study"), IF(D1>12," This is a MEDIUM risk study"), IF(D1<=12,"This is a LOW risk study")

    Thanks

    • Hi Jamil,

      When using nested IF's, all closing parentheses should be at the end of the formula, like this:

      =IF(D1>22,"This is a VERY HIGH risk study", IF(D1>17,"This is a HIGH risk study", IF(D1>12," This is a MEDIUM risk study", IF(D1<=12,"This is a LOW risk study"))))

  366. Dez says:

    Hey so I have a file with 5 Columns and 5000 Rows, there is data in each row, some rows are in Times New Roman, others are in Ahora.

    I want to create a statement which basically says,

    IF X is in Times New Roman then its a Yes, If not then its a No....PLEASE HELP ME!!!

  367. Shashikant Deshpande says:

    how do you count if a column has two words like phone, mail
    and if a filter is applied at another column , the formula should count as per the filter applied not all the cells of the texts of the first column

  368. Nijil says:

    If i put red as value in one cell,what is the formula for appearing the same color in next cell

  369. ostin says:

    thank u vry mch

  370. Tolga says:

    Hi,
    This is what I am trying do
    if value on A1 between
    0-154 A2 0
    155-462 A2 -1
    463-770 A2 -2
    771-1049 A2 -3

  371. Girish Suryawanshi (Pune) says:

    Hi,

    Thanks for above test eg.it lot me help to learn IF formula.

    Thanks.

  372. Aditya says:

    What if I want more than one logical test in my formula?

    eg: If I want "IN TRANSIT" and "DISPATCHED" to be covered by one formula only.

    Awaiting Response

  373. MJ says:

    Hi

    I am a total excel self taught newbie working on an Excel Workbook recording expenditure and wondered if there was an easy way to input the following in formula format:

    If a number is 50 to 100 minus 5% if a number is greater than 100 minus 10% in excel

    EG if the number in cell K20 is between 50.00 & 100.00 then 5% is deducted and if the number is 100.00 or more then 10% is deduced, I have managed to create a formula to deduct 5% if the number is 50.00 using the IF function =IF(K21>$M2,N2,)
    K21 = total
    M2 = 50.00
    N2 = % sum

    The above works fine however it is picking all amounts above 50.00 as I am unable to set the parameters so it only picks up amounts above 50.00 but less than 100.00

    Any suggestions?

    Many thanks

  374. NASCO says:

    Hi Svetland,
    Please help me write this IF formula correctly. i have a cell with different figures but some are missing data sets.
    i want a command that says IF F3>1 then the answer should be 1, IF F3<1 then it should be 0 but if F3 is empty (meaning there is no number there) it should be empty. meaning if F3 is empty, leave blank.
    Kindly assist.
    thank you

    • Hi NASCO,

      You can use a nested IF formula like this:

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

      Please pay attention that the formula will also return an empty string if F3=1. So, you may probably want to add one more condition to the formula or replace F3>1 with F3>=1.

  375. Monaliza Pena says:

    Hi Svetland,

    I am using this formula to find out whether 2 values exists in a column, if it doesn't, I want the result to indidate "NO", if it does, then "YES". However, it shows us "FALSE" for those that does exist. Can you check what I did wrong?

    Thank you!

    =IF(ISERROR(VLOOKUP("AP1902"&TRIM($J51)&TRIM($Q51),$C:$C,1,0)),IF(ISERROR(VLOOKUP("A80201"&TRIM($J51)&TRIM($Q51),C:C,1,0)),"NO","YES"))

  376. KIRAN S ANCHER says:

    Hi Svetlana Cheusheva,
    am just looking to put one formuls to calculate the tax value using IF function.the things are like follows....

    Suppose if there three columns f j and h
    if i enter the value in f as 5.5% then it should give me the value of the f*h in the column j

    ex: (in column j) =IFf=5.5% then it should calculate the value of f*h.
    could you plese help me out in this...

    • Hi KIRAN,

      If you need a formula exactly for 5.5%, then enter the following one in cell J2:
      =IF(F2=5.5%, F2*H2, "")

      The above formula multiplies F2 by H2 if F2=5.5%, and returns an empty string otherwise.

      If you are looking for a more universal formula that multiplies a value in column H by whatever percentage you enter in column F in the same row, then use this one:

      =IF(F2<>"", F2*H2, "")

  377. Adeel says:

    I want to know the formula as per following data:

    F G H
    1 50 100 (I want if G1 value is less than 100 so F1 multiply with 10 but if G1 value is greater than 100 and less than 200 so F1 multiply with 20 and if G1 value is greater than 200 so F1 multiply with 30 and answer came in H1)

    kindly provide assistance

  378. Ashley says:

    I want to link a document so that if it is greater than today it =A4 and if it is less than today it =A3. Say the document with A3 and A4 is called pizza. What would this formula look like?

  379. sharfi says:

    hi Svetlana, thnx for your help but i need it again. please guide me to make this farmula.
    A1="UA" i want cell A1 to be color in red
    how it will work?

  380. sharfi says:

    @Adeel wait 2 min bro miss Svetlana already given me this formula.. let me share it with you

  381. sharfi says:

    @Adeel
    put this formula in H1
    =IF(G1=100,G1=200,F1*30)))

  382. sharfi says:

    Try this @Adeel
    =if(G1=100,G1=200,F1*30)))

  383. sharfi says:

    hi Svetlana, thnx for your help but i need it again. please guide me to make this farmula.
    A1="UA" i want cell A1 to be color in red
    how it will work?
    like if i put the word UA in A1 i want A1 to be filled with Red color otherwise if i put anything else it stays normal

  384. Sharee says:

    Hi Svetlana, I need help with a formula. I need to compare E1(total sale) to F1(goal)and display 10% of E1(total sale) if the store made or surpassed its F1(goal) and 0 if it did not make its goal.

  385. Ruben C. Nayanga says:

    Can IF function for dates be used in a library system?
    I want that the book I've borrowed is in its due date, the date would turn to red or there will be notification that it's already due.