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 7. Total comments: 2534

  1. Hello There,

    I am trying to create an if statement where if two cells are not equal a calculation using cells within the same sheet is provided.

    if M12-L12=0, give me cell E12, however if they are not equal give me (M12-L12)*E12.

    Are you able to assist? It would be very much appreciated.

  2. Hi

    I have a formula and sometimes gives me the right answer, but in some cells give me (FALSE)

    =IF((BN10>BV10),IF(AND(BN10>BW10),IF(AND(BN10>BX10),IF(AND(BN10>BY10),IF(AND(BN10>BZ10),IF(AND(BN10>CA10),"Not Aligned","Aligned"))))))

    1. Hi!
      I can't validate the formula because it uses data I don't have. Also, I can't guess what conditions it should check.

  3. Please help.
    my journal
    A B C D E
    L or S Entry TP SL No of units
    (IF A = "S" then B - C and if C = 0 then D - B) IF A = "L" then C - B and
    if C = 0 then B - C)
    I hope Im clear.
    Thank you.
    Art

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question

  4. Hey there,

    I have a table with two columns I would like to compare:

    Column A: On account (either Yes or No)

    Column B: Deposit (if account status is Yes, then this should be blank, but if account status is No, there should be a deposit amount)

    I would like to highlight the cells in column B that are blank AND account status is No. This will highlight all of my non-account files that require a deposit.

    Hopefully, this makes sense!

  5. What's wrong with my formula.

    =IF(G2>=104.53,"1.2",IF(G2>=95.82,"1.1",IF(G2>=87.1,"1.0",IF(G2>=78.4,"0.9",IF(AND(G2>=1,G2<=69.69,"0.8"))))))

    1. Hello!
      Please try the following formula:

      =IF(G2>=104.53,1.2, IF(G2>=95.82,1.1,IF(G2>=87.1,1, IF(G2>=78.4,0.9, IF(AND(G2>=1,G2<=69.69),0.8,"")))))

  6. I'm trying to track task priority based on due dates

    IFS(OR(H4=TODAY()+21),"High","Low","Medium")

  7. Hi I'm trying it do an If AND Or formula
    Situation If carrier1 is James and Mode is water then Yes otherwise Jo But if carrier is Harry and Mode is Road then Yes. Otherwise No. Harry will only ever be Road but James can be different modes.

    My formula is showing
    =IF(AND(OR(W7="James", AG7="water"), OR(W7="Harry")), "YES", "NO")

    1. Hello!
      Please try the following formula:

      =IF(OR(AND(W7="Harry", AG7="water"), W7="James"), "YES", "NO")

  8. Hello Im creating a excel worksheet i need a little help,

    Exchange Rate
    K1=1
    K2=1.30

    Result in: E1
    IF A1 dropdown "CAD" and B1 Dropdown "CAD" - then Cost C1*K1
    IF A1 dropdown "USD" and B1 Dropdown "USD"- then Cost C1*K1
    E1= Calculation + D1

    Result in D1
    IF A1 dropdown "USD" and B1 Dropdown "CAD" - then Cost C1*K2
    IF A1 dropdown "CAD" and B1 Dropdown "USD" - then Cost C1/K2
    D1=Return with calculation amount else return with 0 (if above conditions aren't met)

    How can i do this please help

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(A1=B1,C1*K1,IF(AND(A1="USD",B1="CAD"), C1*K2,IF(AND(B1="USD",A1="CAD"),C1/K2,0)))

  9. If I have
    column A Column B
    25
    25
    25.25
    24.5
    54
    54
    54

    and in column B I want to calculate 'If A2="25,54", then plus 2 otherwise +6. I want to isolate specific number to plus 2 and the rest plus 6.
    I tried = IF(A2=25, A2+2, A2+6) but it only applied for 25 to plus two, but the rest of the number is plus 6. How do I get the 54 to plus two too?

    1. Hi!
      Your conditions contradict each other. What is "the rest of the number"? What result do you want to get exactly?

  10. Trying but not succeding....
    Trying to make a calculation based upon a letter in a cell. I have a single calculation figured out, but i want to add another condition.
    Here is my original formula: =IF(M35="Y",F35*70%,F35*65%)

    Now I want to add if M35=D, then multiply by 100% and keep the rest above also.

    2. OK, after relentless searching online, I finally figured it out.

      This is what I put: =IF(M35="Y",F35*70%,IF(M35="D",F35*100%,F35*65%))

      It works, so I'm happy.

  11. Hello!

    how can I get the formula for this,

    I want to show the output of this available,discontinued,N/A
    become like this available,Out of Stock,Not available in daily update,

    The discontinued will be Out of Stock and the N/A will be Not available in daily update.

    Thankyou for helping me.

    1. I think what you wanna say is to change the way a formula express such as:
      Discontinued-->Out of Stock
      N/A-->Not available in daily update
      You can customize your own expression by using "things that you wanna say" in the formula.
      example were in the content above.

      hope this helps.

  12. Good evening,
    Would you please help me with Formula? i will greatful.
    value A (input number)
    value C (standard value)
    Value B 100.

    if value A is less than 55% of value C then output 0
    if value A is between 55% to 99% of vale C then output (80% of B)
    if value A is 100% or 110% of value C then output (100% of B)
    If value A is 100% to 120% of value C then output (120% of B)

    1. Hi, Sir Alexander! I am new to excel. Just wanna ask if how will my IF function go if for example, there is one class session per week. One session costs $100. And there are 5 students at max. In the left row, the teacher will indicate Present or Absent. But there are times in a session where not all 5 students are present. Eg only 3 are present, $100 will be divided to only 3 who are present. The cells on the right (after Present/Absent column) should be in numerical form. Thank you, Sir! Hope you can help me with this case..

      1. Hello!
        Use the COUNTIF function to count the number of students present. Then divide 100 by that number.
        I hope it’ll be helpful. If something is still unclear, please feel free to ask.

    2. Hi!
      The tutorial above contains answers to your question.
      You can use this formula:

      =IF(A1/C1 < 55%,0,IF(A1/C1 < 99%,B1*80%,IF(A1/C1 < 110%,B1,IF(A1/C1 < 120%,B1*120%,""))))

  13. Hello thank you for this article and the further examples provided in the comments.

    I have a Bonus with 3 criteria (KPIS) attached. They get full bonus if all three criteria/ KPIs are met. The bonus is reduced if any of the 3 criteria are not met. Criteria 1 and 2 are worth 10% and criteria 3 is worth 5%

    e.g.

    if target is met bonus is £500

    I will enter the data against the 3 KPIs that show if each one is met or not. if all three are met bonus would remain at £500 if KPI 1 is not hit the bonus will reduce by 10% £50 and be £450, if KPI 2 is not hit the bonus will reduce by 10% (of the original £500) so another £50 and KPI 3 would be £25

    Any combination of KPI1, KPI2 and KPI3 is possible, so there are 9 scenarios I think. for example it would be possible for KPI 1 to not be met but 2 and 3 be met.

    I would like the bonus figure to be calculated automatically.

    I cannot work out how to write this, please help!

    1. Hello!
      The formula below will do the trick for you:

      =B1-B1*((A1=0)*0.1+(A2=0)*0.1)-(A3=0)*25

      where A1 A2 A3 - KPI
      B1 - bonus (500)

  14. Sir,
    I have querry where column A has two names and column B has one name but if the name in column is a part of column A the in column C only the non same name should come

    IN SHORT Common Name Part or Full should be removed

    A (COLUMN) B (COLUMN) C (COLUMN)
    SHAILESH SINGH SINGH SINGH
    RAMESH PATEL PATEL PATEL
    SURESH SHAH SURESH SURESH
    ANAND SHAH ANAND ANAND

    Please Guide
    Thanks
    Shailesh

      1. Thank you Sir, I has solved my big query.

  15. how do I write a formula for the following:

    I need a my formula to count as 1 if the following criteria are met in two different columns within a different worksheet.

    so in worksheet2 if the word Apples - Red appears in column A and the date in column G is older than 30 days count as 1.

      1. Thanks it returns a blank cell but should return 7, I'm wondering if the Today formula is correct, I need it to count if the date (are all different dates) entered anywhere in column G are 30 or more days old not 30 days from today.

        If my worksheets have names and I need to only include data from A2 to A300 is the below formula still correct:

        =IF(AND('Data Sheet 4 - Fruit!'A2:A300="Apples-Red",'Data Sheet 4 - Fruit!'G2:G300-30>TODAY()),1,"")

        Also if the criteria isn't met could it return a 0.

        Thanks

        1. Hi!
          If you need to find the difference between dates, use the second date instead of the TODAY() function. If you want to get 0 in case of a negative result, replace "" with 0 in the formula.
          Please read carefully the guide I linked to.

          1. Alexander Thanks I have read the guide so many times but I'm so confused and its still not returning the result it should.

            I don't need a count of dates between each other I need the formula to count how many Apples - Red in column A2:A300 and if any dates in column G2:G300 that are older than 30 days, basically I need to count how many Apples-Red are past their use by dates by more than 30 days??

              1. But if I use Today()-30, won't it count the dates that are 30 days less than today? If so I actually need the formula to count how many of the dates listed are out of date by 30 days i.e.

                Column G has:
                Used by dates
                20/3/2021
                31/12/2021
                30/12/2020
                4/5/2021
                1/2/2021
                4/2/2022
                etc

                I need to know of the dates in column G how many are over their used by dates by 30 days and I need the formula to count how many in total.

              2. Hi!
                The formula I sent to you was created based on the description you provided in your first request. If you need to consider dates that are 30 days less, use the <= operator

                "< ="&TODAY()-30)

  16. Data is in A1= 100. (this is fix data ).

    B1-B10 = date wise high data will come
    C1-C10=. Date wise low data will come
    D1= Pass/Fail (fix Data)

    1.
    Now I want if D1= Pass then if B1 to B10 >= A1+20. Then Result "CLEAR" BUT C1 to C10 <A1-20

    2.

    If D1=Fail then if C1 to C10< A1-20. Then Result "Fail" That time B1 to B10 <A1+20

    3- this Rule should be applied Row wise. If CLEAR Result come first then CLEAR will be valid Output after that if Fail condition met in next row then Fail is invalid in this condition and Results should not chnge to Fail.

    Same For If Fail came first after that Clear Result is not valid.

    1. Hi!
      Unfortunately, I couldn't understand your description of the problem. Write an example of the source data and the result you want to get.

  17. I need help writing =if formulas.
    I have the following'
    A B C D E
    ID age flag
    83125782 63 1 63 Final
    82343216 63 1 63 Final
    82059889 63 1 63 Final
    82843001 56 1 56
    82843001 34 1 56,34 Final
    82843001 15 15
    82843001 13 13
    86835004 61 1 61
    86835004 14 1 61,14 Final
    83326997 58 1 58
    83326997 51 1 58,51
    83326997 19 1 58,51,19
    83326997 17 1 58,51,19,17
    83326997 13 1 58,51,19,17,13 Final
    85120571 32 1 32 Final
    82225798 47 1 47 Final
    82488841 54 1 54
    82488841 48 1 54,48
    82488841 15 1 54,48,15
    82488841 10 1 54,48,15,10 Final

    My formula for D is =IF(AND(A1=A2,C2=1),D2&","&B2,B2)
    and formula for E is =IF(OR(A2A1,AND(C21,A2A1))=TRUE,"Final","")

    I want to essentially concatenate the ages for each series of ID only if they have a value of 1 in C.
    and when I have all the ages for a particular ID write Final to the last concatenated ID in the series.
    There are multiple ID's as well as single ID's.
    Thank you.

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

      =IF(AND(A1=A2,C2=1),E1&","&B2,B2)

      =IF(OR(AND(C1=1,A2<>A1),AND(A2=A1,C2<>C1)),"Final","")

      I hope I answered your question. I

      1. Hi Alexander,
        I put the wrong data.

        A B C D E
        ID age flag
        83125782 63 1 63 Final
        82343216 63 1 63 Final
        82059889 63 1 63 Final
        82843001 56 1 56
        82843001 34 1 56,34 Final
        82843001 15 15
        82843001 13 13
        86835004 61 1 61
        86835004 14 1 61,14 Final
        83326997 58 1 58
        83326997 51 1 58,51
        83326997 19 1 58,51,19
        83326997 17 1 58,51,19,17
        83326997 13 1 58,51,19,17,13 Final
        85120571 32 1 32 Final
        82225798 47 1 47 Final
        82488841 54 1 54
        82488841 48 1 54,48
        82488841 15 1 54,48,15
        82488841 10 1 54,48,15,10 Final

        What I need to do is concatenate the ages in Column D, as long as the ID (Column A) are the same and there is a "1" in the Flag (Column C). The first 3 rows have different ID's but starting at row 4 through 7 the ID's are the same but for row 6 and 7 the flag is blank so I don't want to concatenate the ages for those rows.
        In addition when ID's change and all the ages have been concatenated I want to write the word "Final" on column E. I hope this time the explanation is better.
        These are the formulas you sent.
        I believe I need to use more IF's and AND's conditions to test for different scenarios.

        =IF(AND(A1=A2,C2=1),E1&","&B2,B2) This one is working fine
        =IF(OR(AND(C1=1,A2A1),AND(A2=A1,C2C1)),"Final","") This one is not giving me the right results.

        Thank you!

        1. Hi!
          Your attentiveness could help save a lot of my and your time.

          =IF(AND(A1=A2,C2=1),E1&","&B2,IF(C2 < > 1,"",B2))
          =IF(OR(AND(C1=1,A2 < > A1),AND(C1=1,A2=A1,C2 < > C1)),"Final","")

          Hope this is what you need.

  18. I have finally got the following formula to work

    =IF(F13=45,F13<=100),"Top Dress","")

    is there a way to do it differently to not have the repeated "topdress" if both if statements are true

    1. Hi!
      The formula is written incorrectly and cannot work. You can write your terms like this

      =IF(AND(F13=45,F13<=100),"Top Dress","")

      But the first condition does not make sense, since it is always true if the second condition is true.

      =IF(F13 < = 100,"Top Dress","")

      1. Ugg.. thanks so much for the response, but just realized the beginning was missing.

        =IF(F13=45,F13<=100),"Top Dress","")

        could you please take another look? I appreciate the help

  19. Hi there,

    Thanks for this article :).

    I have been trying to add a formula where if the H cell result is greater than 900, it will add 450 as a number, or a 0 if less than 900.

    =IF(H651>900,"450","0") - This is the formula here. It is working but it seems to be formatting the 450 or 0 as text instead of a number. Therefore, other formulas don't add up the total amount correctly.

    Thanks for this, this is for an earnings sheet for commissions.

  20. Hi Mr Trifuntov,

    How do i formulate let say if A1 has =55, any value in A1 will stay.

    i.e. A1=55 to 65 then B7 shall have the actual value of A1.
    A1>=66, then B7 will show the actual value of A1

    I cant get around the use of a correct excel formula. I need your advice. Thank you

    2. correction:
      A1=65, B7 will show the value of A1 "as-is"

      Again, thank you

  21. i'm facing a dilemma, i'm working at a leasing company, and the official reports that i take from our equivalent of the DMV about our vehicle count and status and plate No. comes in Arabic, i want to transfer the arabic character to the english character, because they're using and english letter against an arabic one, for example A always is the first letter of the arabic alphabet, but using CTRL+f and replace is a time consuming process if you do 3 times a week as this report shows which vehicle do have and don't have a person authorized using the vehicle to avoid making my company take violations and force the actual user to pay it, this report make us avoid these kind of issues, do any knows any formula or another way to speed up the process? as macro isn't the way i tried several times in various ways but with no luck.
    The characters transfer chart as below:
    A=أ
    B=ب
    D=د
    E=ع
    G=ق
    H=ه
    J=ح
    K=ك
    L=ل
    N=ن
    R=ر
    S=س
    T=ط
    U=و
    V=ي
    X=ص
    Z=م

    1. Hello!
      To do a mass text replacement, use the formula

      =IFERROR(SUBSTITUTE($A2, LOOKUP(999,FIND($E$2:$E$13,$A2),$E$2:$E$13), LOOKUP(999,FIND($E$2:$E$13,$A2),$F$2:$F$13)), $A2)

      $E$2:$E$13 - characters to be replaced
      $F$2:$F$13 - what should be replaced
      If not all characters are replaced, reuse this formula with the result of the first replacement.
      I hope it’ll be helpful.

  22. i use DATA VALIDATION

    I have the Data on "Data Sheet" D17 to D90 where E17 to E90 are the corresponding amount... I want to put on separate sheet "SOA" with columns "Description" and "Amount"... all I want to get is that when I select a "Description" the amount on the "Data Sheet" will appear on the "SOA Sheet"...

    Example from "Data Sheet"
    D17 = Apron E17 = 500.00
    on "SOA Sheet"
    D12 = Apron E12 = 500.00 and so on the rest of the cells...

    What formula should I use? I try this one NO GOOD!

    =If(D12=Data!D17,Data!E17) it won't follow to the rest of the cells...

    Hoping you can help me with this... Thank you in advance.

  23. I am looking for a formula that will return a numeric value into another cell. I am stumped ,Here's what I require:
    Columns
    A B C D
    Employee name Shift Attend Absent
    Bill Days 1
    Sara Nights 1
    Bill Days 1
    Bill Nights 1
    Bill Days 1
    Bill afternoons 1
    Jim nights 1

    I typed this formula in Cell M4 and I figured that the formula would be =sumif(a4:a10,"Bill",if(b4:b10,"days",c4:c10)) to which the value in cell M4 will be 2.

    The formula works when I just do =sumif(a4:a10,"Bill",c4:c10)

    I am trying to achieve that anytime "Bill" worked throughout the year on "Days" it returns a sum of those days. Bill Days 2. In another cell (M5) I would do the absent days.

    I would then do the same for him on nights and afternoons in different columns.

    any help would be great. I keep getting stuck on #value.

    Jerry

      1. Absolutely worked.

        Thank you!

  24. Hello, I am trying to make a grading formula for an audit I am making. It seems simple but I can not figure it out.

    The values of G47 will be 0,1,2,3,4 or 5
    I need a formula that will return a score.
    0=0
    1=2
    2=4
    3=6
    4=8
    5=10

    If someone can help let me know!

  25. can someone help me with this IF statement. If the hours worked are 10 but regular is 8 i need an IF statement to calculate the different of time between the two numbers.

  26. Hi,

    I have a list of individual items that belong in a system. The individual items are being inspected and every item needs to pass inspection for a system to be considered "Passed". For example, I want to say if items in system 2 all pass inspection, the total System passed inspection. If only half of the items pass inspection, then the System is in progress. For example:

    System item Passed? System Passed?
    1 1 Passed In Progress
    1 2 Failed In Progress
    1 3 In Progress In Progress
    1 4 Passed In Progress
    1 5 Passed In Progress
    1 6 Failed In Progress
    2 7 Passed Passed
    2 8 Passed Passed
    2 9 Passed Passed
    2 10 Passed Passed

    System 1's inspection is in progress and System 2 passed inspection. I'm looking for a formula that can fill out the "System Passed?" column and basically tell me "Not every item in system 1 passed inspection so system 1 is in progress" and "Every item passed inspection in system 2 so system 2 passed"

    1. Hello!
      To count the number of records by condition, use the COUNTIF function.
      The formula is something like this:

      =IF(COUNTIF(C1:C20,"Passed")/COUNTIF(A1:A20,A1)=1,"Passed", IF(COUNTIF(C1:C20,"Passed")/COUNTIF(A1:A20,A1) > = 0.5,"In Progerss", ))

    2. Column A is "System" with systems 1 and 2, column B is "item" with item numbers 1-10, column C is "Passed?" with "passed"/"failed"/or "in progress", column D is "System Passed?" with either the system "in progress" or "passed."

  27. Hi,
    I'm trying to get a formula that puts together values from different columns (10+) in one same cell with line breaks.

    For each column I want to have the "column title: " + value of the corresponding cell
    BUT
    I only want to have the "column title: " IF the corresponding cell is not empty (otherwise I would see "column title: " without any value

    For example:
    column title 1 is "material"
    cell value 1 is "metal"
    and
    column title 2 is "color"
    cell value 2 is empty
    and
    column title 3 is "size"
    cell value 3 is "15"

    Ideally I wanna see the following:

    material: metal
    size: 15

    and don't wanna see (since "color" is empty I don't wanna have it displayed):

    material: metal
    color:
    size: 15

    The formula I created is:

    =Y1&":"&Y3&CHAR(10)

    Y1= title
    Y3= cell value

    But this leaves me with titles with blank cells (and values).

    How can I integrated a series of IF functions that check cell content of each column?

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(A2 < > "",A1&":"&A2&CHAR(10),"")&IF(B2 < > "",B1&":"&B2&CHAR(10),"")&IF(C2 < > "",C1&":"&C2&CHAR(10),"")

  28. Hi! I need to get reflect 30 if the sum of A2 + B2 is greater or equal to 30, then just reflect the ACTUAL sum of A2 + B2 if the result is less than 30. How should I write the formula? I tried below but I don't get the exact answer, considering the decimal point also, for ex. with result 29.5 it will reflect 30 when it should 29.5 only.
    =IF((A2+B2)>=30, "30", IF((A2+B2)<30, ""))

  29. Im trying to make a formula, but not too handy with excel.

    Its a condion,

    lets say if O8 has a Letter E turn it red and if has letter L turn it green

    Could someone help me.
    Thanks

  30. Hi,
    50 employee's different department
    how to calculate correct incentive %

    conditions

    *Incentive structure*

    (Target completed 1 to 5 jobs)
    quarter 1 = 2%
    quarter 2 = 2%
    quarter 3= 2%

    (Target completed 6 to 10 jobs)
    quarter 1 = 3%
    quarter 2 = 3.5%
    quarter 3= 4%

    (Target completed 11 to 25 jobs)
    quarter 1 = 4%
    quarter 2 = 4.5%
    quarter 3= 5%

    (Target completed 26 to 50 jobs)
    quarter 1 = 4.5%
    quarter 2 = 5%
    quarter 3= 5.5%

    (Target completed 51 to 100 jobs)
    quarter 1 = 5%
    quarter 2 = 5.5%
    quarter 3= 6%

    please help

    1. please help

  31. =IF(B4500,B4*I5,IF(B4>=1001,B4*J5)))

    Last one is not multiple
    please help

  32. plz help me,
    The sum of column A1 and column B1 must be greater equal than 20, the sum of column C1 and column D1 must be greater equal than 46. Then if the sum of all these is greater than 65 then the result will be A +.
    How can I apply,..............plz

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question
      If my understanding is correct,

      =IF(AND(A1+B1>20,C1+D1>46,A1+B1+C1+D1>65),"A+","")

      1. Hi, Alex,
        Thanks a lot to you. Your suggestions is really help me very much.
        Thanks again.

  33. if A= 30 , B=40, C=20 , if i want to show larg/max No from the cell , without using =max , =larg formula
    is it possible to show large no (which is 40 ) by using of "=And" Formula.., if its then how,
    this question was asked during my interview ,please give me answer??? and thanks you in advance,

      1. Thank you sir

    2. can you just help me

  34. I am working with timesheets. So basically I need a formula to state if the total hours worked is less than 2 that it remains 2 but if it is more than 2 that it shows the true value. How do I do that? Please help me.

  35. IF(A2*B2<=4,"1"*C2, IF(A2*B2<=8,“2”*C2, IF(A2*B212,”4”*C2))))

    IF A2xB2 = 1, 2,3 or4 value = 1
    IF A2xB2 = 5, 6, 7,or 8 value =2
    IF A2xB2 = 9,10,11,12 value =3
    IF A2xB2 = greater than 12 value =4

    the new assigned value of 1,2,3, 4 would be multiplied by value in column C2

    What am I doing wrong? Still a beginner in excel so any help would be appreciated.

  36. Hi Alexander,
    I hope all is well
    I am trying to come up with a formula that reads a calculation whereby if the month is may and the year is more than and equal to 2027 and less than and equal to 2046 (The range of years is between 2027 and 2046) and month is may

    If the 3 conditions above are correct then I want it to multiply two numbers (lets say 4*5) in every May of the period between 2027 and 2046.

    I succeeded in the month part but not the range of years all together

    Highly Appreciated

    1. Hello!
      With the MONTH function, you can determine the month, and with the YEAR function, you can determine the year from the date. Define the month and year and use these conditions in the IF function.
      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  37. Hi Please solve this. We have two cell in row A and B.

    A B
    1 1
    1 2
    1 3
    2 1
    2 2
    2 3
    3 1
    3 2
    3 3

    where A and B matched with above number then result should be below.

    A B Result should be
    1 1 A
    1 2 B
    1 3 C
    2 1 D
    2 2 E
    2 3 F
    3 1 H
    3 2 I
    3 3 J

  38. =IF(C3="n", B3-A3,IF(AND(C3="y",SQRT((B3-A3)/2)=0,(B3-A3)/2,(B3-A3)/2+0.5)))

    I'm trying to make it so that if a cell equals "n", it'll just subtract one cell from the other and if the cell equal "y", it will then check if one cell subtracted from the other equals zero. If it does equal zero, it'll divide the cells by two, and if not, it'll divide the cells by two and then add .5. I can't figure out what is wrong with my formula, but every time I hit enter, it tells me that there is. Can anyone tell me what I did wrong?

    1. Hello!
      I don't really understand your terms. But this formula will work.

      =IF(C3="n",B3-A3,IF(AND(C3="y",(B3-A3)=0),(B3-A3)/2,(B3-A3)/2+0.5))

    2. if the cell equals "y", it will check if the square root of one cell subtracted from the other equals 0*

  39. Hi,

    I need a formula for below:

    Current Period Start Date 1-Jan-21
    Current Period End Date 31-Dec-21

    Payment Periods (sample) below:

    Start Date End Date
    1. 10-Mar-19 10-Mar-21
    2. 9-Apr-21 08-Nov-21
    3. 15-May-20 15-Feb-23

    I need IF formula for A,B and C as respective columns after End date column:

    A=Period (in days) for Previous Years, i.e Before 31-Dec-2020
    B=Period (in days) for Current Year, i.e 01-Jan-2021 to 31-Dec-2021
    C=Prepaid period (in days) from, i.e From 31-Dec 2021 onwards

    Kindly request your help.

      1. Hi Sir, Thanks. DATEDIF is helpful to calculate difference between two dates. However I need to segregate the results to the days for "previous year", "current year" and "future years"?

        Below is my problem:

        Current Period Start Date 1-Jan-21
        Current Period End Date 31-Dec-21

        Payment Periods (sample) below:

        Start Date End Date (Samples)
        1. 10-Mar-19 10-Mar-21
        2. 9-Apr-21 08-Nov-21
        3. 15-May-20 15-Feb-23

        I need IF formula for A,B and C as respective columns after End date column:

        A=Period (in days) for Previous Years, i.e Before 31-Dec-2020
        B=Period (in days) for Current Year, i.e 01-Jan-2021 to 31-Dec-2021
        C=Prepaid period (in days) from, i.e From 31-Dec 2021 onwards

        1. Hello!
          For example #1, use these two formulas:

          =IF(DATE(YEAR(B1),1,1)>A1,DATEDIF(A1,DATE(YEAR(B1),1,1),"d"),"")

          =IF(DATE(YEAR(B1),1,1)>A1,DATEDIF(DATE(YEAR(B1),1,1),B1,"d"),DATEDIF(A1,B1,"d"))

          A1 is 10-Mar-19
          B1 is 10-Mar-21

          1. Thank you Sir.

  40. I'm trying to get an if function to cooperate with me.

    if(a17=12, then c17=c16). so basically if there is the number 12 is cell a17 then cell c17 will equal c16

  41. I have a formula I am trying to come up with and have multiple ranges of numbers involved.

    Is converting the numbers below into a formula so when I enter my blood pressure readings into my data sheet so the status column changes to the appropriate status when the following conditions are met for each status.

    NORMAL less than 120 and less than 80
    ELEVATED between 120 129 and less than 80
    HYPERTENSION STAGE 1 between 130 139 or between 80 89
    HYPERTENSION STAGE 2 140 or Higher 140 or 90 or Higher 90
    HYPERTENSIVE CRYSIS Higher than 180 180 and/or Higher than 120 120

    For example when I enter 118/75 the status changes to Normal
    138/65 the status changes to HYPERTENSION STAGE 1

    1. Hello!
      Use substring functions in Excel to extract numbers from text:

      =IF(AND(--LEFT(A1,SEARCH("/",A1)-1) < 120,--MID(A1,SEARCH("/",A1)+1,10) < 80),"Normal",
      IF(AND(--LEFT(A1,SEARCH("/",A1)-1) > = 120,--LEFT(A1,SEARCH("/",A1)-1) < = 129,--MID(A1,SEARCH("/",A1)+1,10) < 80),"ELEVATED",
      IF(AND(--LEFT(A1,SEARCH("/",A1)-1) >=130,--LEFT(A1,SEARCH("/",A1)-1) < = 139, --MID(A1,SEARCH("/",A1)+1,10) > = 80,--MID(A1,SEARCH("/",A1)+1,10) < = 89),"HYPERTENSION STAGE 1",
      IF(AND(--LEFT(A1,SEARCH("/",A1)-1) > = 140,--LEFT(A1,SEARCH("/",A1)-1) < = 179, --MID(A1,SEARCH("/",A1)+1,10) > = 90,--MID(A1,SEARCH("/",A1)+1,10) < = 119),"HYPERTENSION STAGE 2",
      IF(AND(--LEFT(A1,SEARCH("/",A1)-1) > = 180,--MID(A1,SEARCH("/",A1)+1,10) > = 120),"HYPERTENSIVE CRYSIS","")))))

      This should solve your task.

      1. The help in the formula is appreciated, but I am still not getting the result I needed.

        The table I have to record my data has 8 columns on it and uses columns B-I the data starts at B6 to I6.

        I want to use the following information to use as the criteria in a formula for when I enter my BP Data into (D) and (E), (I) Changes status based on the data entered into (D) and (E).

        NORMAL less than 120 and less than 80
        ELEVATED between 120 129 and less than 80
        HYPERTENSION STAGE 1 between 130 139 or between 80 89
        HYPERTENSION STAGE 2 140 or Higher 140 or 90 or Higher 90
        HYPERTENSIVE CRYSIS Higher than 180 180 and/or Higher than 120 120

        B C D E F G H I Date Weight Systolic Diastolic Pulse BMI Weight Status BP Status

        What I need is a formula that uses the information above for when you put your BP data into column D and column E, column I changes the status to Normal, Elevated, HYPERTENSION STAGE 1, HYPERTENSION STAGE 2, or HYPERTENSIVE CRYSIS based on the data numbers put in Column D and Column E.

        For example:

        If you put in 119 in (D) and 79 in (E), (I) Would have Normal as status
        If you put in 119 in (D) and 85 in (E), (I) would read Elevated Status
        and so on....

  42. Hi. I am working on a file that looks like this.

    Date submitted Quarter Deadline Remarks
    9/7/2021 4th 9/01/2021 Late/On-time

    The result that I wanted is like this:

    a. If the time of submission is beyond three days after the date reference (deadline with 3-day extension), remarks should be LATE. Excluding weekends from the 3-day extension.
    Example, deadline is on 9/1/2021, output is submitted on 9/7/2021, remark is LATE.

    b. If submitted within the deadline with 3-day extension, remarks will reflect ONTIME. Excluding weekends from the 3-day extension.
    Example, deadline is on 9/1/2021, output is submitted within the deadline or within 3 days after the deadline (9/1-7/2021, remark is ONTIME.

    c. If the submitted is from previous quarter or before 9/01/2021, remarks is FOR VERIFICATION

    Hoping for your answer ?

    Thank you in advance ?

      1. Thank you very much.

        This is so much helpful.

  43. In the column 'L', I have values between 0-100. In column M, i want to fix decile class based on corresponding values in L column. Ex. If L2 value is between 0.00-1.00, i have to get 1 in M column, If L2 value is between 1.01-2, I have to get 2 in M column and so on. I tried the below formula, but error occurs,Pls help me out.

    =IF(L2>0 AND L21 AND L22 AND L2<=3,'3','0')))

  44. please help me and give me " =if " formula to paste student marks range(0 to 34, 35 to 50,51 to 70....etc) as per their marks
    ...please help me.....

    Marks 0 to 34 35 to 50 51 to 70 71 to 90 91 and Above

    A B C
    Name Marks find marks belongs to which range(0 to 34,35 to 50…etc)
    Alan 80
    Bob 50
    Carol 60
    David 95
    Eric 20
    Fred 40
    Gail 10
    Harry 80
    Ian 30
    Janice 10
    Alan 75

    David 85

      1. sorry but i didn't get , i didn't find anything from the comment section, please help me because i got stuck...only this last time give me formula ,

        column A (Name) column B ( Marks) Colmn C Range
        Sam 80 71- 80 < -- --- how to paste this text by
        using =If Formula, Cuase i just
        want to paste range(71-80)

        1. Im trying this formula ,

          =if(B2>=0,B2=36,B2=51,B2=71,B2<=80,"71-80")))

          but i did not get the actual rang(0-35...etc)

          please help me i have to solve this assignment question ....and tomorrow is my last day pls help

  45. Hi,

    I need help, if I sell every 10cartons i will get 1 carton free

    For eg:
    sell 29ctns free 2ctns
    sell 59ctns free 5ctns
    sell 10ctns free 1ctn

    Is there any formula I could use for this condition?
    Appreciate any help to solve this. Thanks!

  46. Hi Alexander,
    I have the following table:

    No. on St. Street Name
    1 A2 (European Road)
    2 A2 (European Road)
    3 A2 (European Road)
    1 E5 (National Road)
    2 E5 (National Road)
    4 A2 (European Road)
    5 A2 (European Road)

    I need a formula to number the points located on the same road (column A), in ascending order using the B column (Street name, which is text)

    I've tried to use =IF(B2B1,1,A1+1) and the result is ok if the streets are organized well (ordered by name) but for the last 2 cells, the values will be 1,2 and not 4,5.

    Can you help me with the correct one, please?
    Thanks in advance!

    2. Solved with =COUNTIF($A$2:A2,A2)

  47. Hi,

    I am trying to build an if statement which will tell me to either "Strong bet", "bet" or "fade" the capper. Conditions are ROI 20%+ for "Strong bet", ROI 10-19% for "bet". Less than 10% or sample size less than "10" from the capper is a "fade"

    thanks

  48. please help me on the formula to use here
    The college wishes to analyze the applicants’ data in order to find those applicants who qualify for admission to pursue a course in IT. Successful candidates MUST meet the following minimum requirements;
    • Must have scored a mean of 45 marks and above;
    • Must have scored 60 marks and above in Mathematics;
    • Must have scored 50 marks and above in either English or Kiswahili.

    Enter an appropriate function in cell I4 and copy it to other cells to determine whether the student qualifies for admission. If the student qualifies, the function should display ‘Successful’. Otherwise it should display ‘Unsuccessful’.

  49. Hi Alex,

    Trying to do something with AVERAGE but got stuck, hope you have some ideas!
    Let's say we count visitors in shop for last 10 days. We have 90 visitors per day for 9 days but on one particulate day we had 3x more customers then usual. I would be happy if I could exclude this day from average count ("paranormal day" or something like that). So we have days in columns A to J, row 1. In row 2, we have our daily count. K2 cell is average count (9*90+1*270). That cell with 270 value should be excluded from average count.

    Hope this above makes sense!

    Thanks
    Ivan

    1. Hello!
      To calculate the mean without anomalies, you can use the standard deviation. Only values are taken into the calculation that deviates from the average value by no more than the value of the standard deviation.
      In Excel2019 and below, enter this formula as an array formula.

      =AVERAGE(IF((A1:A10>=AVERAGE(A1:A10)-VARP(A1:A10)^0.5)*(A1:A10<=AVERAGE(A1:A10)+VARP(A1:A10)^0.5),A1:A10))

      You can also try the TRIMMEAN function:

      =TRIMMEAN(A1:A10,0.2)

      I hope I answered your question. If something is still unclear, please feel free to ask.

  50. How many nested if statements can be used in Excel 2013

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