Comments on: Excel IF statement with multiple conditions

For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading →

Comments page 16. Total comments: 2534

  1. HI
    i need guidance regarding following problem.
    I have data in three columns,First column contain ID, Second contain Disease name and third also contain disease name, now one ID is in multiple rows with data, so how can i combine data of same IDs in one row, and then in one cell.
    Thanks

  2. Hi,
    Svetlana

    Can you please assist me, the same cell if a positive value should be 'Dr' if negative should be 'Cr'

    =IF((AND(G5>=0.1,G5<0.1)),"Cr","Dr")

    1. Hi!

      I think you've overthought it :)

      =IF(G5>=0.1,"Cr","Dr")

  3. Hi, Could someone help me to create a formula for:\

    "IF D1 = A2 return the value in B2 or IF D1=A2 return the minus value in C2

    Thank you!

    regards

    1. Not sure what you are asking unless you want B2 or C2 to be returned as the If False condition.
      If D1=A2 the formula will either return B2 or C2, but not both.
      Try this and see if it works for you: =IF(D1=A2,B2,C2)
      If the value in D1 is equal to the value in A2 then return the value in B2 otherwise return the value in C2.

  4. how to Caluclated Dr is +, cr is -
    DR 10
    CR 20
    DR 20

    1. Hello there,
      If I understand your task correctly and you'd like to turn numbers for CR to negative and your data are in columns A and B, the following formula should do for column C:
      =IF(A1="DR",B1,-(B1))

  5. Can someone help, need an excel formula.

    I have two columns.
    Column A contains dates (just month and date) i.e. September 09
    Column B contains texts either "Complete" or No"

    I want to compare cell A1 if it contains a date to Cell B1 if it contains text and give results "Done" or " Not done"
    Results: If cell A1 = date and cell B1 = text complete then "Done"
    If cell A1 = no date and B1 = text completed, then "Not Done"

    1. Sofy:
      There are a couple of ways to accomplish what you want to do. Here is one that doesn't require VBA.
      =IF(AND(CELL("Format",D24)="D2",E24="Complete"),"Done","Not Done")
      Where the date is in D24 and it is in the dd/mmm as in your sample.
      Where "Complete" is in E24 enter this formula in an empty cell.
      So, it says, if the format of the contents of cell D24 is a date in the format 9-September and the contents of E24 is "Complete" then display "Done" otherwise if one or both of these are not true display "Not Done".

  6. Hi,

    I need help with an "if,then" statement. I want to say the following:

    If C3 is 50% of B3, then D3 should be 0.
    If C3 is not 50% of B3, then D3 should be 50% of C3

    1. Hi Donterrio,

      Here's the formula for B3:

      =IF(C3=B3*0.5, 0, C3*0.5)

  7. Hi there. I am trying to produce a formula for the following:
    IF A1 contains Receivables, copy b1 as a positive number in c1.

    Any ideas?

    1. Hi Casey,

      Here's the formula for C1:
      =IF(A1="Receivables", ABS(B1), "")

      1. Thank you! Youre a wiz! One more question: The word receivables is in a sting of words in A1. How do I write this into the formula?

        1. Hi Casey,

          In this case, you can use the SEARCH function to find it :)

          =IFERROR(IF(SEARCH("receivables",A1), ABS(B1)), "")

  8. great article -thank you!

    I am stumped on one that I would love your expert opinion on....I want to combine and AND with a formula, so basically if cell E3>0 then H2*E3 (I have to cost out the cost of an employee to fly out for a certain travel day, but only of that travel day is required. By placing a 1 in cell E3 I am saying that we need a travel day, so want it to activate the formula H2*E3).

    Know this is complicated but thank you for reading this one anyway!

    1. OK, I'll bite.
      =IF(E3>0,H2*E3,"Stumped")

      1. Thank you Doug!

  9. Help, I need to make a formula for cells for the following. If between 2-5 then 1, If between 6-10 then 2 and so on. Please help

    1 first aid attendant for 2-5 employees per floor at all times
    2 first aid attendants for 6-10 employees per floor at all times
    3 first aid attendants for 11-50 employees per floor at all times
    4 first aid attendants for 51-100 employees per floor at all times
    5 first aid attendants for 101-150 employees per floor at all times
    6 first aid attendants for 151-200 employees per floor at all times

    1. Hello, Rhea,
      If we understand your task correctly, the following formula should work for you:

      =IF(A1 >= 151, 6, IF(A1 >= 101, 5,IF(A1 >= 51, 4, IF(A1 >= 11, 3, IF(A1 >= 6, 2, IF(A1 >= 2, 1, ""))))))

  10. Hi
    please am trying to put a formula that would select the best six results out of nine result for an assessment, any help

  11. Dear,

    I am planning to write an if function for 3 criterion.

    IF(AND([In Progress - Late]1 >= 1, [At Risk]1 >= 1),[In Progress - On Track]1>=1,) "Red","")

    I know I am writing it wrong. Can anyone help me structure the formula?

    Cheers!

    1. Dear Michael,
      Thank you for contacting us.

      For us to be able to help you better, please describe the conditions you have in more detail. It will help us modify your formula to make it work.

      Thank you.

  12. Hello,
    I am working on project timeline. Client requirement is:
    Don't count Sunday & holidays. Now I am facing difficulty when there is a holiday, How I can increase the completion time that was wasted because of that holiday. Formula I used is:

    =IF(WEEKDAY(F$3)=1, "holiday",IF(AND(F$3>=$D4,F$3<=$E4),"WORK DAY",""))

    1. Hello, Ibrahim,

      If you need to calculate workdays with custom holidays, the NETWORKDAYS function should help you. It returns the number of workdays between two dates, excluding weekends and, optionally, the holidays you specify. If this is what you are looking for, please take a look at this web-page for more details.

      If it is not exactly what you need, please specify. We'll do our best to help.

  13. hi
    Good after noon,
    I want to show a cell content as debit or credit depending up on previous cell value
    Eg: if the cell value is 55 then next cell will shows credit if the cell value is -50 next cell will show debit.Can you please help me to solve this problem.

    1. Syamlal:
      Are the only two possibilities "55" and "-50"? If so, then use
      =IF(A2=55,"Credit","Debit") where the data is in A2.
      If the data is dynamic and is stored in cells A2 and down the column then just copy the formula down the column and it will reflect the results from examining the cells A3, A4, etc.
      If the data is dynamic and always shown in the same cell then something like =IF(Cell Address=50,"Credit","Debit").
      Or maybe the situation might be If the data in a cell address is less than zero then debit.
      There are a number of possibilities for this scenario. You'll have to be more specific with your request to get the answer to your question.

  14. This formula is exactly what i need and i appreciate for sharing.
    However with me did not work
    I have only one column the Result if less than 28 is Fail if Above 28 is Pass
    I went in Manager Formattin Conditional create a rule but it did not give me the Result Pass or Fail
    I believe is because i used only one value the médium value of score.
    If i6>28 , "pass") other rule
    IfI6<28," fail")
    If you can help me on this I appreciate. Maria

  15. Hello, Jessica:
    I think this will work for you:
    =IF(COUNTIF(E28:E43,"TRUE"),"Yes","No")

    1. Jessica:
      I should add that the "TRUE" value is case sensitive. So, with your data it might be "true".

  16. =IF(OR(AND(M5>0,M5=4,K5=5,M5=14,K5<20)),0.05769)

    Please help! Not sure why I'm getting an error on this. looking to provide an IF statement based on multiple conditions that include an "OR" condition as part of multiple ranges.

    Thanks!

    1. =IF(OR(AND(M5>0,M5=4,K5=5,M5=14,K5<20)),0.05769)

      Please help! Not sure why I'm getting an error on this. looking to provide an IF statement based on multiple conditions that include an "OR" condition as part of multiple ranges.

      Thanks!

  17. I am trying adding a formula whereby if one scores 0 to 39 the other cell displays U, if scores 40 to 49 the other cell displays E, if scores 50 to 59 displays D,, it goes loke that..

    1. Hello, Andrew,

      If we understand your task correctly, the formula below should work for you:
      =IF(A1 <= 39, "U", IF(A1 <= 49, "E", IF(A1 <= 59, "D", "")))

  18. Please help me to solve this problem.......
    If A1=0 then B1=Y and if A1 is not equal 0 then B1=Cell value of A1

    1. Hello, Rakibul,

      Please try to enter the following formula in B1:
      =IF(A1 = 0, "Y", A1)

      Hope this is what you need.

  19. I am trying to make general comments on students performance if series of column have better or bad mark with distinction, credit, pass or fail. But my formula returns error how can I make better?
    =IF(AND(B4>=70,"Distinction", IF(B4>=55,"Credit", IF(B4>=40,"Pass", IF(AND(C4>=70,"Distinction",IF(AND(C4>=55,"Credit",IF(C4>=40,"Pass", IF(AND(D4>=70,"Distinction",IF(D4>=55,"Credit",IF(D4>=40,"Pass", IF(AND(E4>=70,"Distinction",IF(E4>=55,"Credit",IF(E4>=40,"Pass", IF(AND(F4>=70,"Distinction",IF(F4>=55,"Credit",IF(F4>=40,"Pass","Fail"))))))

    A B C D E F G
    1 1 90 80 70 85 70 Distinction
    2 2 72 66 85 90 65 Credit
    3 3 70 75 55 63 50 Pass
    4 4 34 80 70 39 35 Fail

    1. Hello, Kasozi:
      A couple of things. First, the logic used in this formula is going to return an error. For example it says: "B4>=70,"Distinction", IF(B4>=55,"Credit"", etc.
      B4 cannot be greater than or equal to 70 and 55. Also the conditions cannot trigger two different returns.
      Secondly, I don't think you need to use "AND" for the reason I gave above. The value in B4 cannot be two different conditions to return two different words.
      I think I see what you're trying to get at, but I would recommend you read the explanation and then start by using the example shown in the discussion in the article. Start with the simple example and then build the formula from there. Each time you add another condition check to see if it returns an error. If it doesn't add another and so on.

  20. I would like to make the following formula larger (placed in cell J9):
    =IF((I9)="poor";"0";IF((I9)="adequate";"1"))

    by adding another two factors:
    =IF((I9)="poor";"0";IF((I9)="adequate";"1");IF((I9)="good";"2");IF((I9)="excellent";"3"))

    But Excel then states that I've entered too many arguments for this function. Does anyone have a solution?

    I need to be able to turn the 4 words into a scoring.

    Thanks for your help.

    1. I think It's very simple, please try below formula in same Formate ...

      =IF(I9=0,"poor",IF(I9=1,"adequate",IF(I9=2,"good",IF(I9=3,"ecellante",IF(I9=4,"very excellatent")))))

      Note: Total no of closing bracket is equal to total no of IF used in the formula.

    2. try This formula given below

      =IF(I9="poor","0",IF(I9="adequate","1",IF(I9="good","2",IF(I9="excellent","3",""))))

  21. Hello,

    I'm looking for an If formula with times and days

    If cell is greater than 01:00 and equal to or less than 04:00 return 0.5 (half a day). but if the cell is greater than 04:00 return 1 (1 day).

    Is this possible?

    1. The one formula I have used is:

      Start Time Finish Time Total Hours Worked Days to claim

      12:00 15:00 3:00 0.5

      Formula
      12:00 15:00 =F11-E11 ???

      I would like days to claim to pull back 0.5 if the sum in "Total Hours Worked" is 3.75 or less. Or pull back 1 if it's over 3.75.

      I'm guessing the formula would be a "sum if" but I'm not sure how to write it

      I'd be grateful for any suggestions please :)

      1. Day Date Name Start Time Finish Time Total Hours Worked Days to claim

        Thursday 11/10/2018 Test 9:00 10:00 1:00
        1
        Friday 12/10/2018 Test 2 17:00 22:00 5:00
        1
        Saturday 13/10/2018 Test 3 7:00 22:00 15:00
        1

        Example formula:

        =IF(G22>3.75,"0.5",IF(G22<3.75,"1"))

        G22 is the total hours (i.e.1, 5, 15). 1 should pull back 0.5. 5 and 15 should pull back 1
        but this formula is not working properly.

        I would be grateful if you could please help me fix the formula

        1. Since you want 3.75 or less = 0.5, you should start the equation with > 3.75. Else, excel will read 3.75 as 1.

          The basic if equation is like this:
          =if(condition, result if condition is met, result is condition isn't met)
          So the equation should be like this
          = if(A2>3.75,1,0.5)

          With A2= working hours

          Note: you only need " " if the option are non numerical.

  22. I need a formula to get the values

    Cell C1 and C2 have four Different values:-
    "Yes" "Yes"
    "No" "No"
    "Yes" "No"
    "No" "Yes"

    I need the result for all the four conditions as:-

    If C1 & C2="Yes" result is 1
    If C1 & C2= "No" result is 2
    If C1="Yes" & C2= "No" result is 3
    If C1="No" & C2="Yes" result is 4

    Kindly help me on this.

    1. Hello, Akash,

      Please try the following formula to get the result you need:
      =IF((AND(C1="Yes",C2="Yes")),1,IF((AND(C1="No",C2="No")),2,IF((AND(C1="Yes",C2="No")),3, IF((AND(C1="No",C2="Yes")),4,""))))

      Hope it will work for you.

  23. I need a formula where if i select YES the score should be 2 and if select NO the score should be 0 and if i select NA the score should be 2

    1. Hello, Jebin,

      If we understand your task correctly, the following formula should work for you:

      =IF(A1="YES", 2, IF(A1="NO", 0, IF(A1="NA", 2)))

  24. Hi friends,
    I tried this formula but it does not work. Can anybody tell me where is the problem ? How can I correct?

    =IF(G2>=60,"A",IF(G2>=50,"B",IF(G2>50,"C")))

    1. Ismayil:
      I think you just need to change the ">" in the last IF to "<".

  25. How to apply IF condition if the days fall in this category

    >45 Days
    31-45 Days
    16-30 Days
    0-15 days

    1. Rajesh:
      Where the data is in A2
      =IF(A2=<15,"Something",IF(A2=<30,"Something",IF(A2,45,"Something","Something Else"))))

  26. Amir:
    Where the dates are in the range M11:M16, in an empty cell I would enter the label "Number of workdays in Jan". Then in a cell directly to the right or left of the labeled cell I would enter this formula:
    =COUNTIFS(M11:M16,">=1/1/2018",M11:M16,"=2/1/2018",M11:M16,"<=2/28/2018")
    You can expand the range to include the addresses you'll need as the year progresses say from M11:M34. Alternatively you can use the absolute reference from the get-go by using the dollar signs like $M$11:$M$34. This would work if you knew ahead of time what the year's range would be.

    1. Hi Doug
      but your formula counts every dates which reach the condition. for example if I have two records in one day of Jan, the formula will result in 2 but the correct answer is 1.
      do you get what I mean?

  27. Hi Doug

    thanks for the reply.

    I'm currently using:

    =IF(AND(U20<0.06,Y20<0.06,Y40<0.06,V60<0.06),"YES","NO")

    This works but I'd rather be able to use the range:

    U20:AA25
    U40:AA45
    V60:Z66

    1. Paul:
      It seems as though the formula you're using works.
      Why do you want to use the ranges?

  28. I have three ranges of cells. Each range individually must return a value of <0.06 to be true.

    U20:AA25
    U40:AA45
    V60:Z66

    1. Paul:
      OK, so you've got some data in these rows.
      Do you want an IF/Then Statement to check to determine if the value in a range is less than .06.
      Is that what you're saying?
      Or are you asking for an expression that will determine the value of these ranges?
      Are you currently using an expression to produce the value of each range?

  29. I have three conditions but I have not getting the results

    Please help.

    =IF(A5="Dist","((D5<=106,'10.90','7.90'))","((D5<=106,'9.20','6.20'))")

    1. Amol:
      I think it would help you to put into plain language what you're trying to do. Something like, "If the value in A5 is "Dist" then display something, otherwise display something else."
      Try that and see if it helps. If you can do that and it's clear to you what you want to happen and you still can't figure out how to do it, post that sentence and we'll try to help you.

  30. hi... i tried to use an if function it didnt work. the sceario is that we use 2 different weight scales metric and imperial. i just wanted to excel to calculate and show me every weight in grams rather both. i tried this formula "=IF(L4="Grams", "K4*O4", "K4*O4/2.2046")" and unfortunately it didnt work. can u please help me?

    1. Hi Adnan,

      And you should not enclose cell references and arithmetic expressions in quotations marks. For example, this formula calculates just fine:

      =IF(L4="Grams", K4*O4, K4*O4/2.2046)

      1. Good catch, Svetlana:
        Not sure why, but I didn't see the double quotes around the expressions inside the parenthesis. That would definitely not work.

    2. Adnan:
      What about your formula didn't work?
      What's in L4?
      What's in K4 and O4?
      The expression K4*O4/2.2046 should probably be written K4*(O4/2.2046) or (K4*O4)/2.2046 depending on what's in O4.

  31. Hello, Ben:
    I think what you're looking for is a nested IF statement.
    It should look like this:
    =IF(B25<=0.5,"Unfit",IF(B25<=0.65,"Level 1",IF(B25=0.85,"Level 3"))))

    1. Hi Doug - I will try it - thank you!!!!!!!!!!!!!

  32. Column A contains a set of values
    Column B contains another set of values.

    In column C formula is:=IF(A1>B1,"No","Yes")

    Another formula is needed in column D if column C is No , then it must state Yes and visa versa.

    Can somebody please help?

    1. Linette:
      Wouldn't the formula in D be the same thing as in C?
      Formula in D =IF(C1="NO","Yes","No")

  33. If Column A = “Y” and Column B = “5” Pull the detail from column D

    1. Dinesh:
      You want to use and IF AND statement. In general they look like this: =IF(AND(Something is True, Something else is True), Value if True, Value if False)
      So in your case where the data is in A2, B2 and D2 it looks like: =IF(AND(A2="Y",B2=5),D2,"No Match")
      The "No Match" text can say what you want. Just keep it in the double quotes.

  34. HELP ME ON GRADING OF MARKS:
    IF GRADE =a, mark is 4, etc.I TRIED =IF(C10=A,"4",IF(C10=B,"3",IF(C10=C,"2","1")))

    NOT WORKING.

    1. Makesh:
      You were close. The double quotes should be around the text, not the number. The formula looks like this:
      =IF(E38="A",4,IF(E38="B",3,IF(E38="C",2,1)))
      If you wanted to enter text for the grade it would be:
      =IF(E38="A","Four",IF(E38="B","Three",IF(E38="C","Two","One")))

  35. My problem is..
    I have a machine in comany, I want diaplay it's maintenance due due dates as follows.. More than or equals to 31 days- Ok, less than or equals to 30 days-Not Ok, less than or equals to 5- Need attention, less than or equals to 0- Emergency.

    Pls help me to write formula for above condition

    1. Herald:
      I believe the formula you want looks like this:
      =IFERROR(IF(N14>=31,"OK",IF(N14<=30,"NOT OK",IF(N14=5,"Needs Attention,IF(N14<=0,""Emergency"))),"More Than 31 Days")
      The date is in N14, but you can change that address to suit your situation.
      The IFERROR is there to catch dates greater than 31 days.
      If you don't need it you can remove it and the parentheses that contain it. I was using =DATEDIF(StartDate,TODAY(),"D") to give me some dates to work with in days. This generated some negative dates. For example, if one of the StartDates was after TODAY() it would throw a #NUM error.

  36. Hi I am trying to combine several IF formulas into one - the formula used to calculate what I want depends on the value of the variable. For example:

    If BW12 is <=1 then the formula is 1-BW12^2/2
    If 1<BW121.7 then the answer is 0.05

    I know what I have currently:

    =IF(BW12<=1,(1-(POWER(BW12,2)/2)),IF(1<BW121.7,0.05)))

    is wrong so I would appreciate any assistance,

    Thanks

    1. Lily:
      What is the meaning of this piece, "1<BW121.7"?

  37. Hi. When there are no amounts entered on a sheet, what formula do I enter so this, #DIV/0! is recognised as a 0 amount as my calculations will not work with this symbol present?
    Thanks

    1. Jim:
      It depends on what version of Excel you're using but,
      after Excel 2007 you can use =IFERROR(A2/A3,0). This tells Excel if your formula evaluates to #DIV/0!, then return 0, otherwise return the result of the formula.

  38. Help with coming up with a formula!!

    COUNTIFS('EMPLOYEE Detail'!E:E,"TWIN LAKES MEDICAL FOUNDATION INC",'EMPLOYEE Detail'!H:H,"EMPLOYEE")+COUNTIFS('EMPLOYEE Detail'!H:H,"EMPLOYEE",'EMPLOYEE Detail'!G:G)

    It keeps giving me an error. I am trying to get the twin lakes medical foundation and the employee information which are from two different columns to give me a $total from another column.

    1. I am working off of 3 columns to try to get the data I need, I was able to get it to count how many employees have selected the product I am just stuck on trying to get it to calculate as to which company the employee is with to get me the $ they have elected.

      Column E Column G Column H
      Stoutco 14.18 Employee
      Cedar 22.54 Employee + Spouse
      Stoutco 22.54 Employee + Spouse
      Cedar 40.14. Employee + Family
      Cedar 22.54 Employee + Spouse

      I came up with this formula to get me the total for each COUNTIFS(E:E,"STOUTCO",H:H,"EMPLOYEE) to get me a total of 1.

      I just need help with coming up with Stoutco, Employee = Column G for the whole column to pull how many people elected the products.

      Please and Thank you,
      Stephie

  39. How to apply IF condition if the days fall in this category

    >45 Days
    31-45 Days
    16-30 Days
    0-15 days

    Is this correct :=IF(H9<=15,"0-15 Days",IF(H93045 Days")))

    1. Manish:
      Can you make your question clearer?
      What does "IF(H93045 Days") mean?

  40. Can I use this for sorting out carry out in result computation? If not help me out please

    =IF(AW9<40,"TSL 102",IF(AR9<40,"FOT 213",IF(AM9<40,"FOT 212",IF(AH9<40,"FOT211",IF(AC9<40,"CME 122",IF(X9<40,"WPT 216",IF(S9<40,"WPT 214",IF(N9<40,"AGT 231",IF(I9<40,"GNS 201",IF(D9<40,"FOT 214",))))))))))

    1. Fadele:
      I don't believe the nested IF statement you included in your post will provide anything useful.
      Try breaking it down to smaller pieces that help answer a question.
      Put the IF statements in separate cells to check the logical value in each cell.
      Check here on AbleBits and read the articles about IF and nested IF statements and I believe you'll see how the IF statement should be written.

  41. Hi. I have 3 columns. a credit column, a debit column, and a total. if there is a value in the debit column, i want it to deduct from the total. if there is a value in the credit column, i want it to add to the total. there con only be a debit or credit for each record.

    1. James:
      If you have the Excel version that allows you to create tables, consider creating a Table for this data. The Table would have six columns. They would be from left to right:
      Item, Credit, Running Credit, Debit, Running Debit, Running Total.
      Where the first data cell is B6 The Running Credit would have =SUM($B$6:$B6) this would create a running total of the credit.
      The Running Debit would have $D$6:$D6 in its top cell. This would create a total for the running debit.
      The Running Total would have in its top cell =SUM($B$6:$B6-$D$6:$D6) which would create the running total.
      At the bottom of each column in a Table you can quickly create a sum or various other stats.
      You can insert a row in the Table to include a new item and the totals will be updated as soon as you enter the value.
      You can sort the data quickly and easily while maintaining the relative relationships.
      You can also format the Table with nice looking formats.
      AbleBits has an article that explains Tables and their advantages.

  42. Hello, i am seeking a better way
    currently i have a data list (down)with
    name,acquisitionday,units, across the top of my excel file i have a data list (across) with name,rateday,rate.
    my current formula =if(name across matches name down,(if((acquisition date<rateday,round(units*rate,2),0)),)

    1. continuation,
      seeking to have 1 tab with down data, and 1 tab with across data
      on the down data tab, seeking to insert a nested if function to calculate results
      down tab
      abc,9/19/17,100 result to be 25, as the last rateday is after the acquisitionday

      across tab (rates and dates can be different)
      abc, 6/1/17, 0.10
      abc, 8/1/17, 0.15
      abc, 10/1/17, 0.18

  43. Hi,
    I am looking to create a formula that looks in different cells (for example: A1, B1, C1, D1) and if these cells have "no" then it skips until it finds something different than "no" and put in the cell where the formula was written whatever text was in that cell.

    thanks

    1. Oren:
      I believe this will produce the result you're after.
      Where the list is structured as a vertical list in column A cells A1:A100, enter this in B1:
      =IF(A1="No","",A1) and copy it down the B column.
      This formula says, If A1 has "No" in it then leave B1 empty, otherwise enter what is in A1.
      You can type in whatever text you need by typing the text between the quotes holding No or the empty quotes.

  44. Again its dropped the middle, not sure what is up but if(d7<=59,"1" is missing again

    1. Stewart:
      Not sure what is happening on your Excel sheet, but the formula I sent works fine on mine. As to the other issues you're seeing, I don't know how to help with that.

  45. Hi

    I can't get this to return.

    =IF(D7=0,"0",IF(D7=60,"2",(IF(D7>=60,H7*5,IF(D7<=59,H7*2))))))

    Thanks

    1. Hello Stewart:
      Try this:
      =IF(D7=0,"0",IF(D7=60,"2",(IF(D7>=60,H7*5,IF(D7<=59,H7*2)))))

      1. Thanks Doug

        But now my 60>= doesn't return my 2.

        This is what I am trying to do.

        if sheet >= 60mins then 2 points, if less than 60 mins 1 point, if 0 mins then 0 points + if clean sheet =1 then if cell with minutes >= 60 mins then 5 points, if less than 60 mins 2 points and if 0 mins 0 points.

        Hope this helps, I'm really struggling to get the 3 if's + another 3 if's to work together.

        On there own they work
        This is the clean sheet one
        =IF(D7=0,0,IF(D7>=60,H7*5,IF(D7<=60,H7*2)))
        This is the mins played one
        =IF(D7=0,"0",IF(D7=60,"2")))

        I need them to work together if possible

        1. Sorry minute one is this
          =IF(D7=0,"0",IF(D7=60,"2")))

  46. hello
    thanks for your help
    how to do this formula ???
    if one column is bad d5 =0
    else if another column is good d5 =10

    1. Hi Ali,

      if(d5=0,” Bad”,”Good”)

  47. I keep getting a #N/A when the cells that use this formula are blank. I want the formula cell to show blank when there is no information to complete the calculation. Here is the formula that is in the cell.

    =IFS(W6=1,T6/AD8,W6=2,T6/AD9,W6=3,T6/AD10,W6=4,T6/AD11,W6=5,T6/AD12,W6=6,T6/AD13,W6=7,T6/AD14,W6=8,T6/AD15,W6=9,T6/AD16,W6=10,T6/AD17)

    I have tried multiple ways of using "" but I can't figure out how to not get either the #N/A or the FALSE to show in the formula cell.

    1. Callie:
      I found this solution on the web. The solution was for a question like your's concerning the blank cell issue.
      You should replace the HLOOKUP parts with your IFS pieces. As the above article explains you can also trap that #N/A error with IFNA or ISNA.
      As an aside, the number of IF variables in your formula makes your situation a very good candidate for another approach. There are way too many IF then parts to keep up with.
      Anyway, I believe you're using Excel 2007 or newer, but I included the first part for the folks on here who are using older versions.

      Excel 2003 or older:
      =IF(ISERROR(HLOOKUP($D$6,Benefits,2,FALSE)),"",HLOOKUP($D$6,Benefits,2,FALSE))

      Excel 2007 or newer:
      =IFERROR(HLOOKUP($D$6,Benefits,2,FALSE),"")

  48. IF CELL ADDRESS B5

    I WANT TO APPLY IF FORMULA FOR TWO/THREE CONDITIONS FOR THE SAME CELL ADDRESS

    EXAMPLE
    B5>500000,"B5-500000",IF(B5>=1000000,"1000000", "0")

    KINDLY GUIDE ME WHAT KIND OF FORMULA SHOULD BE IMPLEMENT IN SUCH CONDITIONS AND HOW?

    1. You have applied a wrong formula, please find the correct formula given below-

      =IF(B5>500000,B5-500000,IF(B5>=1000000,1000000,0)

    2. Shivaji:
      I think what you're looking for is:
      =IF(B5>500000,B5-500000,IF(B5>=1000000,1000000, 0))
      If the numbers are actually text then you need to enclose them in quotes, otherwise you leave them as shown here.

  49. Hello

    I've read your guidance and created the following formula to achieve the following. Unfortunately it doesn't work. Any advice would be greatly appreciated.

    If L3 is empty return 'No Appt',

    If the date is L3 is in the future return 'Future Appt'

    and If both R3 is empty AND the date in L3 is in the past return 'No Report'

    =IF($L3="","No Appt", IF(L3>TODAY(),"Future Appt", IF(AND($R3="", L3<TODAY(),"No Report"))))

    1. Jonathan:
      Start building this IF And Statement from the inside and see where it breaks.
      Begin by putting IF($R3="", L3<TODAY(),"No Report", "T") in a cell. See if that will calculate.
      Then continue to build the formula piece by piece and you'll see where it breaks. When it does this may help you to determine what your goal is and how to accomplish it in another way.

  50. I have Two columns one for Ps1 and k12 , I want to calculate the value of k12 to comply with these three conditions:

    1- if Ps1<=10 then k12=1
    2- if 10<=ps1=20 then k12=200/(ps1)^2

    Thanks in advance for any help

    1. Nazar:
      The first condition looks to be something Excel can determine.
      The second condition is unclear.
      If PS1 is equal to 20 it can't be equal to 10 at the same time.
      Do you mean "IF PS1 is between 10 and 20"?
      Can you clarify the conditions?

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 :)