Comments on: How to use IF function in Excel: examples for text, numbers, dates, blanks

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

Comments page 11. Total comments: 3001

  1. Hello all, I hope someone can shed some light on my issue.
    I am trying to calculate fuel consumption based on different MOVEMENTS of the ship from cell A1.
    Cell A1 will either be, LOAD, SEA, MANEUVER, DISCH. Each Movement will correspond to a different CELL with its corresponding fuel consumption rate. SEA corresponds to cell A20 (or 1.02). I tried the IF function, but could only manage to get one IF.
    ex A1 (SEA), B1=30 hours. FORMULA in C1, =IF(A1="SEA",(B1*A20)). This one works, but I cant seem to add multiple IF functions for LOAD, MANEUVER, DISCH, withouth it saying #value.
    Any help would be great! thank you

      1. Mary,
        Thank you so much! That was it! Merry Christmas!
        ~P~

        1. Pricilla,

          Thank you very much for your reply. I'm so glad to hear you found the solution in this article!

          Merry Christmas and Happy New Year! :)

  2. I am stumped, I am trying to tie 3 columns together with column 1 having a drop down menu. I need to be able to use the first column to select a city and the following 2 columns auto-populate with amounts.

    (IF D4=Norfolk, then E4=94 and F4=55) is what I am trying to get to work. Is this even possible?

    1. No Anthony that is not even a correct formula.

  3. 80% - 83% 1 mark
    84% - 87% 2 mark
    88% - 91% 3 mark
    92% - 95% 4 mark
    96% - 100% 4 mark
    help me to formula for this in excel

    1. =IF(L29<80%,"0",IF(L29=80%,"1",IF(L29<=83%,"1",IF(L29=84%,"1",IF(L29<=87%,"2",IF(L29=88%,"3",IF(L29<=91%,"3",IF(L29=92%,"4",IF(L29<=100%,"4","0")))))))))

      Note: L29 is reference cell

      1. =IF(L29<80%," ",
        IF(L29=80%,"1Mark",IF(L29<=83%,"1Mark",IF(L29=84%,"2Mark",IF(L29<=87%,"2Mark",IF(L29=88%,"3Mark",IF(L29<=91%,"3Mark",IF(L29=92%,"4Mark",IF(L29<=100%,"4Mark","0")))))))))

  4. Please help me.. I HAVE 2 CONCERNS
    1. I have a column of "Stock Quantity". i want the "Stock Quantity"automatically decreases when i insert a data in my delivery sheets. then,

    2. From my "Stock Quantity" let say i have 1000 stocks, the "REORDER LEVEL is 2,500. i want the row highlighted into color red if the 'REORDER LEVEL IS GREATER THAN THE STOCK QUANTITY'. what formula should i use?

    Thank you..

    1. i like to share my view here, if its use full i feel hapy

      For 2) conditional formating, then Highlight cell rules, then greater than option not required formle for RED colour

      For 1) In same cell its not possible, need to add one more column for stock quantiry for ( opening or closing )then its possible

      =B5-Sheet2!B6,share your mail id i try to send excel sheet directly

  5. Hi,

    I'm trying to write a formula to say that if cell F4 says 'yes', to then remove the fill colour from cell E4. I am having trouble doing this. Can anyone do this? Thanks in advance :)

    1. You don't need a formula - look at Conditional Formatting>Highlight Cell Rules

  6. How would I write the formula for if a range contains a value greater than 2, then true?

    1. Hi... i like share my view ..

      =IF(J18>2,"True", " ")

    2. Hi.. i like share my view..

      =IF(J17>2,"True", "False")

  7. Can we apply multiple logical test in IF function?

  8. I'm looking for a way to have the result of an IF be an equation, or rather the value to an equation. Anything I put inside the "" is taken literally and therefore I cannot enter an equation.

    For example, if the value in cell K11 is "yes" then the value in L11 should be the sum of I8+J6-J11.

    This is the actual formula I'm working on:
    =IF((AND(K11="yes", I8>=0)), "sum(J6+K6+K7+K8+J14-J11)", IF((AND(K11="yes", I8<0)), SUM(J14+J6+K6+K7+K8+I8), "0"))

    1. So I figured out my mistake, but now Excel is telling me I have too many arguments for this function. But I need to address all of these possible scenarios.

      Here's the new formula I have:
      =IF((AND(K11="yes", I8>=0)), SUM(J6+K6+K7+K8+J14-J11), IF((AND(K11="yes", I8<0)), SUM(J14+J6+K6+K7+K8+I8-J11)),if((or(k11="NO",L6<=0)), "$0.00"))

      Is there a way to simplify that Excel with accept, or do I need to find another way, like using two cells and separating my outcomes?

      Thanks!

  9. HOW CAN I HAVE A FORMULA FOR THIS1. If cell (E260) is less than 20, times it by 2,
    if it is greater than or equal to 20 but less than 40, then times it by 3
    if it is greater than or equal to 40 and less than 60, then times it by 4
    And if it is greater than or equal to 60, then times it by 6.

    I CANT SEEM TO ENCODE THE BUT AND AND FUNCTION

    1. Hello,
      Please try the formula below:

      =IF(E260<20, E260*2, IF(E260<40, E260*3, IF(E260<60, E260*4, IF(E260>=60, E260*6))))

      You can learn more about Excel Nested IF in Excel in this article on our blog.

      Hope you'll find this information helpful.

  10. If in the cell in column I there is a date, then the cell in column J needs to be one week later.

    ie.

    J10 = 9/18/2018 then K10 needs to = 9/25/2018

    This has to be simpler than I am making it, help appreciated.

    1. Chelsea:
      If cells J10 and K10 are formatted as Date, then the formula in K10 should read:
      =J10+7

  11. how create formula for if sr no 01 get 1500 ,2 for 1000 and 3 for 500

    1. Shashikanta:
      You'll need to enter different text if the value in SR isn't 1,2 or 3, but the formula is:
      =IF(SR=1,1500,IF(SR=2,1000,IF(SR=3,500,"Something Else")))
      where 1,2,or 3 is entered into SR.

  12. Chona:
    Because you have just five ranges to check, I think this will work. Where the value to check is in C29
    =IF(C29<49,0,IF(C29<59,"2%",IF(C29<74,"3%",IF(C29<89,"4%",IF(C29<=100,"5%")))))
    You can enter this formula in D29 and copy it down the column if you need to check values in C30, C31, etc.

    1. Thanks a lot, its working fine.

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

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

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

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

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

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

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

  14. Hi.

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

    Thanks Rudy.

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

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

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

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

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

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

    3. No I have never used either one of them before.

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

    1. SQL:
      What do you want to do with this data?

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

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

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

    How do I write the formula?

    1. Tomas:
      In C23 enter =IF(A13=50,95,"Out Of Range")

  19. Hi,

    Please suggest a formula

    A1 value is 50

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

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

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

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

      =IF(A10,"50"))

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

      I hope this helps.

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

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

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

          = IF ( A1 0 , "50" ))

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

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

            =
            IF
            (
            A1
            0
            ,
            "50"
            ))

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

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

              =IF(A1<0, 0, A1)

  20. Hi guys

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

    Example:

    Column A column B

    70591- Completed 80042-completed

    Column C

    Yes

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

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

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

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

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

    1. take out the quotes on P2

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

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

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

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

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

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

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

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

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

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

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

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

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

      1. Thank you very much!

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

    1. Can you post a sample?

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

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

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

    Andy Garcia 2015 60.1 2016 67.31 2017 79.23 2018 79.23

    Domingo Solis 2015 45.00 2016 46.35 2017 48.66 2018 56.24

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

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

    1. Tim:
      I believe this will work. If the data is in cell A1, enter this in cell B1:
      =IF(A1<0,0,A1)

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

    In simple terms, these formulas are like the following:

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

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

    Thank you.

    1. Scott:
      Can you provide the actual amount they charged and the formula they used to arrive at that amount?

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

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

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

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

    How can I combine this?

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

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

    1. you'll need to use a vlookup function.

  33. I am trying to do a calculation to get the AOV from my transactions and revenue. In my pivot table I have blank cells and cells with o in them that I need to ignore so I don't get errors and I have tried this but it is not correct, please help

    =IF(AND(ISBLANK(K5), "", K5/K22, IF(AND((K5,K5/K22,""))

    1. Amy:
      Are you trying to count the entries or sum them.
      Why aren't you using COUNT, COUNTA, COUNTIF, COUNTIFS, SUMIF, SUMIFS or something similar?

  34. Hi,
    I would like to know a formula for this example:
    If a current price in cell B10 is 2,00 and new price in cell C10 is 1,00 I will get a cost impact in cell D10 of -1,00. The price has decreased.
    But if both cells (B10 and C10) are empty I do not want 0,00 (zero) in cell D10, I need the cell to be empty.

    Thanks!

    1. Lina:
      I think this is what you want in D10:
      =IF(B10>0,C10-B10,"")

  35. Hi! I'm needing to add IF formula in TRUNC calculated numerical digit. Everything is OK but when i input in G16 double numeric digit (example: 25.20) small bag condition its not work properly. please solve the problem.

    G16 (input double digit numerical value 25.20 is not working or 7.20 is working)

    =G16-TRUNC(G16)
    =IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))

    1. Neetu:
      I don't know what is wrong on your end, but this formula works fine on my practice sheet.
      I can look at the other issue if you can tell me how H16 is related to the values in G16 and how H16 and I16 are related.
      For example, If G16 is 25.2 then H16 is .5 also if H16 is .5 then I16 is 7.

      1. I want to input in
        for examole
        G16 25.2 then H16 is 0.2 and I16 is 25.00 J16 is "small bag"
        G16 25.43 then H16 is 0.43 and I16 is 25.00 J16 is "medium bag"
        G16 25.50 then H16 is 0.50 and I16 is 25.00 J16 is "large bag"

        =G16-TRUNC(G16)
        =H16-I16
        =IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))
        this is the relationship but i am unable to establish the small bag relation that is 0.20. J16, H16 & I16 autofill
        Please solve the problem.

        1. Neetu:
          I think this should get you what you're after.
          Enter this into H16: =IF(G16=25.2,0.2,IF(G16=25.43,0.43,IF(G16=25.5,0.5,"-")))
          Enter this into I16: =IF(G16=25.2,25,IF(G16=25.43,25,IF(G16=25.5,25,"-")))
          Enter this into J16: =IF(G16=25.2,"Small Bag",IF(G16=25.43,"Medium Bag",IF(G16=25.5,"Large Bag","-")))
          I know the formula in I16 just repeats the "25" value, but if you need to change the values the formula is in place.

          1. Than you for cooperation but the actual count which tare is .20 will count as small bag, .43 as medium bag & .50 as large bag. On the other hand the gross will be as open mode and mentioned all three tare value would be fixed. Its mean gross will be be before decimal as flexible after decimal are fix (for example gross are may be 19.20, 23.2, 37.2, 28.2 28.43, 29.43, 29.5, 47.5 etc.
            If i input G16 then auto count H16 and finally J16 would be count on H16. please note that all calculation depends on G16
            G16 H16 I16 J16
            Gross Tare Net Bag Size

  36. Dear Sir,
    there are three words in data validation list( CSL,CST,CFL), I WANT TO USE A FORMULA TO WITH RIGHT FUNCTION TO SHOW(IF RIGHT TWO WORD 'SL' THEN SHOW 'SOLID', IF 'ST' THEN 'STRANDED', 'IF; 'FL' THEN FLEXIBLE.

    1. Veeresh:
      Can you clear this up for me? Does the data validation return CSL,CST or CFL or does it return SL,ST or FL? If not why not? Why not have one set or the other?
      If it returns SL,ST or FL the answer is: Where the return cell is A64 it looks like this:
      =IF(A64="SL","Solid",IF(A64="FL","Flexible",IF(A64="ST","Stranded","NOT IN LIST")))
      If it returns CSL,CST or CFL the answer is: Where the return cell is A64 it looks like this:
      =IF(A64="CSL","Solid",IF(A64="CFL","Flexible",IF(A64="CST","Stranded","NOT IN LIST")))

  37. Hi, good evening,
    I want to use IF function in TRUNC calculated function. If i input the numeric digit 7.20 it's OK but double numeric digit (ex. 25.20) is not work properly (only 0.20). Please solve the problem.

    Thanks & regards
    neetu

    G16
    =G16-TRUNC(G16)
    =IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))

    1. Neetu:
      Why do you want to use TRUNC? That function truncates a number to an integer by removing the fractional part of the number. So, TRUNC(4.3) returns 4 and it's 4 for ever more.
      Are you trying to display a number? Maybe round off a number? Can you use ROUND or format the cell that holds the end result?
      Is G16 formatted as a number or text? Maybe TRIM?
      What I'm suggesting is that there are a number of methods you can use to display a number in the format you want without removing the data. Because, once the data is gone, it's gone.

      1. actually I need to calculation on after decimal number in 3 fixed value 0.50, 0.43 & 0.20

        G16
        =G16-TRUNC(G16)
        =IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))

        Is this correct formula? please help.
        thank u.

        1. Neetu:
          The formula you have here works great. When you input the value, it returns the text you want. I'm still not clear on why you need to use the TRUNC function.
          I don't understand what you want to do with the fixed values. What type of calculation do you want to do with them?

          1. Good Morning! I just try to prepare local invoice in excel sheet. where tare value is fixed bag weight (0.50, 0.43 & 0.20). Actually I want to in the sheet input area only G16 (with double digit numerical value) other cell are should be auto calculated. this is the matter. if i use wrong formula then please help me with new formula. TRUNC is not important.

            G16 H16 I16 J16
            Gross Tare Net Bag Size
            17.20 0.20 7.00 small bag

            1. Neetu:
              The values in H16,I16 and J16 can be auto-filled if Excel knows the relationship between the cells.
              For example, when H16 is ".2" then J16 is filled with "Small Bag". This is working on my practice sheet now.
              I can help you with this question more completely if you tell me what the relationship is between these cells. For example, if G16 is "17.2" is H16 ".2" and if H16 is ".2" is I16 "7"? Show me the relationships. It doesn't have to be in a formula.

              1. G16 (input double digit numerical value 25.20 is not working or 7.20 is working)

                =G16-TRUNC(G16)
                =IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))

                this is the relationship but i am unable to establish the small bag relation that is 0.20.

  38. Hi can you solve this for me please.
    If(J4>J3,J4-J3,0) but If(J4=Blank of 0,0) and if(J3-blank or 0,0.
    it mean If J4>J3 calculate J4-J3 but if J3 or J4 = Blank or 0 return to 0.
    thank before hand.

    1. Sarith:
      It appears as if you've answered your own question. The formula is:
      =If(J4>J3,J4-J3,0)

  39. in a row ex. a2,a3,a4,a5,a6,a7,a8,a9,a10. out of this 1 cell contain a number "0"(a2=1,a3=1,a4=1,a5=1,a6=0). the formula i need is to know when one of the row is zero it need to pickup and tell there`s a zero in row a2___a10

    1. Sathi:
      Why not just highlight the cells that contain a "0"?
      Select the cells, go to Format, then Conditional Formatting.
      In the conditional formatting window select the "Cell Vale is Equal to 0" in the three cells then click the Format button and choose a pattern you like and OK out to the sheet.

  40. Hello, I am trying to update a spreadsheet that was created by an old director. They have an area within the spreadsheet that has a column for goals, and then the monthly figures to in the columns next to it. The monthly number has either a checkmark, an exclamation point, or an x before the number to indicate if it is within goal. For example: Goal = 94% but the April column for that specific goal is 89%, this would read an 'X 89%' in the applicable cell. But the characters are also in color, so it is not just a letter or symbol typed in. When going in the cell, i cant even see any what if statements or anything other than the attached data to another sheet with in the spreadsheet. I hope this makes sense. Does anyone know how to enter a formula into a cell in order to get these characters in there? Thanks so much.

    1. Megan:
      To start, it might be beneficial if you can see the formulas and their respective cell references. There is a good description of how to accomplish this in the various versions of Excel here on AbleBits.
      In the search box type "show formulas".
      Once this is done you might be able to figure this out on your own, but if not write back and someone can probably help.

  41. Sales 100% 90-99 89-80 70-79 <70
    Target
    <80% 2.10% 1.9% 1.7% 1.3% 1.1%
    <89% 2.40% 2.2% 1.9% 1.4% 1.2%
    <99% 2.70% 2.4% 2.2% 1.6% 1.4%
    100% 3.00% 2.7% 2.4% 1.8% 1.5%
    106% 4.50% 4.1% 3.6% 2.7% 2.3%

    i want to create a incentive sheet
    if sales target is less then 80% and avg selling price is <70% then value cell*with 1.1

    i am trying to calculate via this formula but the result shown false

    =IF(AND(E8<80%,E9<70%),D12*N15,IF(AND(E8<90%,E9<70%),D12*N16))
    plz correct it

    1. Faisal:
      I've looked at you question off and on for a couple of days and because of the formula you included it's still not clear to me what you're after.
      However, if what you want to accomplish is this:
      if sales target is less then 80% and avg selling price is <70% then value cell*with 1.1
      the formula would look like this:
      =IF(AND(A196<80%,A197=70%),A198*1.1,"Something Else")
      If this is what you want, replace the "A" cell addresses with the addresses where your data is stored.
      This formula says: If the value in cell A196 is "<80%" and the value in cell A197 is "70%" then multiply the value in cell A198 times 1.1 otherwise display "Something Else".

  42. I am adding 2 cells together. Sometimes one or the other cell is blank and the =sum(a1+b1) formula doesn't bring back a value. if cell b1 is blank then return cell a1 value,or if cell a1 is blank then return cell b1 value, otherwise sum a1+b1

    1. Leni:
      This should work: =SUMIF(A5:B5,">0")
      Where the numbers are in A5 and B5 and are greater than zero.

  43. Sir
    I want to use formula that if cell no menton RD then multiple of 5% and if cell number mention URD then no multiple. Please tell me sir.

    1. Is the data in these cells "TEXT and a NUMBER"? Or do they contain only text or only a number. Show an examples of some cell's contents.

  44. hi
    i am making price list
    i need to do in b column if number is same in b column so in in k column result will be plus in l column
    exam
    1 A B --------- K L
    2 1 --------- 25.50 38.25
    3 1 --------- 12.75
    4 2 --------- 05.75 17.20
    5 2 --------- 11.45

    SO WHAT SHOULD I WRITE IN L COLUMN....

    1. thanks you so much for reply Ekaterina Bespalaya

  45. if I want to find if C1 is between 200 and -200) put "Lab" and if G2 is "PD25088" put "GEO"

    1. Your question is unclear because the connection between the two outcomes is unclear. I can't figure out the logic.
      However, the first part will require =IF(AND(C1-200), "LAB", "No LAB").
      The second is =IF(G2="PD25088","GEO","No GEO").

  46. Hi! How can I create a formula for the following?

    =IF(H9="S4309","",IF(H9="S4310","",IF(H9="S4311","")=I9+365))

    So, if cell H9 equals either S4309,S4310 or S4311 then cell K9 will be blank. Otherwise, it will populate an expiry date based on cell I9.

    Thank you for your help!

    1. I believe this is what you're looking for.
      =IF(OR(H9="S4309", H9="S4310",H9="S4311"),"Blank",I9+365)
      I put the word "Blank" in there to test it. You can use "". I formatted I9 as Date and H9 as Text.

  47. Please ignore my first post, as I have worked out the formula, but realised I cant sum the values in the target Cell

    So this what I am trying to do now.

    If C27 = P then S27 = 1
    If C27 = DO then S27 = 0

    Is the above possible.

    What is the best way to combine into one argument.

    Thanks.

    1. Looks as if this will need a nested IF formula. Ablebits has a good description of how to use these as well as similar conditions. Search "Nested IF formulas in Excel" here in Ablebits.

  48. Hi, please help me whit a formula for my need

    I have a cell that can be text or number ( AH99). I need to make an IF AH99 is any text, then return 0 and if not, multiply that number from AH99 whit another cell ( AI99) and return result

  49. need a formula for , any hours over 8 in cells D thru H would be added to the overtime cell (K)

    D E F G H total reg ot

    8 8.75 8.00 9 8 41.75 40 1.75

    1. =SUM(IF(E197>8,E197-8,),IF(F197>8,F197-8,),IF(G197>8,G197-8),IF(H197>8,H197-8,),IF(I197>8,I197-8,))

  50. Hi,

    I want to use 'if' formula, I have 5 slabs 500000 to 999999 = 1%, 1000000 to 1499999 = 2%, 1500000 to 1999999 = 3%, 2000000 to 2490000 =4% & 2500000 to above = 5%
    so how to use IF formula, pls help me...Thanks

    1. =IF(AND(D193>=5,D193=10,D193=15,D193=20,D193=25,"5%","")))))
      please change the values accordingly.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)