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

  1. Hi I need help with a formula to see if this is possible.
    I want to have A1>5 it is Yes, if A1<5, then it is NO and if A1 id empty or blank then it should be left blank,

    I tried using : =IF(A1<5,"NO",OR IF(ISBLANK(A1),"BLANK", "")))
    But it returns with NO even when A1 cell is empty instead of returning as blank

    1. =IF(ISBLANK(A5),"",IF(A5<5,"NO","YES"))

      1. Sorry I used A5 instead of A1. The formula still holds true though. Just a note though, if the cell contains anything other than blank or 0-5 it will say "YES".
        If a cell contained "a" the result would be YES

  2. I am trying change the value of a cell based on another value depending on if it is a multiple of a specific number.

    So, I want C1 to change to "1" if G1 is a multiple of 8. And to "0" if G1 is not a multiple of 8

    Not sure if anybody has commented this before but I have spent hours trying to figure it out.

    1. Hello!
      If the remainder of the division of numbers is zero, then the first number is a multiple of the second. You can determine the remainder of a division using the MOD function.
      Please try the following formula:

      =IF(MOD(G1,8)=0,1,0)

  3. Hi, I have a problem with the formulas I am using for an analysis.

    I have made a drop down, with 4 options:
    1.) 0-2
    2.) 3-6
    3.) 7-10
    4.) 11+

    I have also made a short table where this option got a value to calculate with:
    0-2 = 5
    3-6 = 10
    7-10= 15
    11+ = 20

    To calculate I use the formula: =LOOKUP(Cell of the option 1 to 4; {"0 to 2";"3 to 6";"7 to 10";"11+"};{5;10;15;20})

    This is going good untill i need the vallue of the last option 4 (11+), because it shows the value 5, and it has to be 20.

    What am I going wrong in the formula?
    Or do I need to use an IF formula?

    1. Hello!
      The second argument to the LOOKUP function, [Lookup_vector], must be sorted in ascending order. Your formula doesn't have it. The fourth value is less than the third.
      You can learn more about LOOKUP syntax in Excel in this article on our blog.

  4. Hi I need some help with IF statements,

    Im using the following formula which so far works great but not sure if what I want to happen can actually work.
    =IF(Y17="A","Awesome job.",IF(Y17="B","great work")) ect. all the way through to F.

    however I want it to try and give more than one option to pick from as I am doing this for a lot of kids and what different options. For example if they get an A randomly pick out of 3 different comments for example "awesome job" or "Amazing" or "Fantastic work". can I have this many options? even a choice of 2 options would be great.

    Also I have the example comments written in example Cell C21-C24. Can I get it to pick and copy one of those 3 cells instead of typing out the whole text comment I want?

    1. Hello!
      Use the RANDBETWEEN function to generate random numbers for selection.
      You can select values in a formula using the CHOOSE function.
      Or, randomly select a cell address using the INDIRECT function.

      =IF(Y17="A",CHOOSE(RANDBETWEEN(1,3),"awesome job","Amazing","Fantastic work"),"")
      or
      =IF(Y17="A",INDIRECT("C2"&RANDBETWEEN(1,4)),"")

      I hope my advice will help you solve your task.

  5. Could you help me to write a formula for IF B2 is YES, then it should consider the data present in A2,if B2 is No, then it should be blank

      1. No,I Could not find answer in the mentioned link. Kindly help me on the below
        Example:In A2 column document number has given, In B2 cell if document is closed then closed date has mentioned, If document is open then cell is blank, In C2 cell if document is in closed status (date updated)mentioned as YES if it’s open (B2 is blank) C2 shows as No.
        Now I need the formula for D2 cell, if C2 is yes then it should consider the date which is mentioned in B2, if C2 is no then D2 cell should be blank.

        1. Hi!
          Both of these formulas match your conditions:

          =IF(C2="Yes",B2,IF(C2="No","",""))

          =IF(C2="Yes",B2,"")

          1. Perfect! It’s working thank you so much ☺️

  6. Hi,

    I'm looking at trying get a yes/ no answer for a column that has "yes" or "no" data entered.

    If the column is all "yes" the statement is true and the result should be "yes". Is there a way to do this?

    E.g.

    A1 Yes

    A2 Yes

    A3 Yes

    A4 Formula gives the answer "Yes"

    However, if A2 had "No", the formula in A4 would give the answer as "No"

    Any help would be appreciated as I have been researching for a few months on and off now.

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

      =IF(AND(A1="Yes",A2="Yes",A3="Yes"),"Yes","No")

  7. I need a formula that will do the following:

    =IF(F2*60%)/32)2, I want 3 for my answer, If >1 and < 3 I need 2 as my answer. thanks kindly

    1. It dropped something from my question:
      =IF(F2*60%)/32) >2 I want 3, <2 I want 1 and 1 I want 2.

      1. Hi!
        Please re-check the article above since it covers your case. However, your second and third conditions can be met at the same time. It's not allowed in the formula.

  8. the "," does not work! it has to be ";" to work

    1. Hi!
      This depends on your computer's regional settings. European settings use ";". US regional settings - ","

  9. Hi! Is it possible that I can have two values if my logical test is true?

    Example:
    If(A1>0, B1-A1 and at the same time C1-A1, "")

    I hope I explained it well. Thank you.

      1. I write it like this =IF(A1>0, OR(B1-A1, C1-A1),"")

  10. Hi,
    Required to identify credit / debit ( contain with "-" after number that is credit, otherwise debit

    my data as below

    84.03
    0.05
    0.58-
    131,429.79
    209,489.24
    239,999.90
    145,599.21
    0.44
    0.43
    170,989.70
    4,666.29-
    0.98-
    0.39

  11. Hi

    A1=100
    A2=50
    =sum(A2-A1) WILL = -50
    How do I make it show 0

  12. Hi, can you please help me write an IF formula/statement that alerts users of a chart that the total nett profit after tax is less than 10% of the total sales?

  13. If I calculate 12 rolls of different yardings in one cell and i want to know the quantity of rolls in different cells. Then what formula should i apply?

  14. If my amount of 20001 more is 5%.

    Rate monthly GP
    3% 20000
    5% 20,001 - 40,000

    Margin* Cons Margin* %
    3,131.20 3,131.20 3%=IF(Cons Margin<20000,3%,5%)
    1,844.40 4,975.60 3%
    2,720.00 7,695.60 3%
    2,604.00 10,299.60 3%
    2,620.80 12,920.40 3%
    3,633.00 16,553.40 3%
    2,856.00 19,409.40 3%
    2,155.33 21,564.73 5%
    1,890.00 23,454.73 5%
    2,204.00 25,658.73 5%
    3,192.00 28,850.73 5%
    1,920.00 30,770.73 5%
    1,817.60 32,588.33 5%

    When 3% to 5% then 3% on 20001 but what happen here it is calculate 2,155.33 (21,564.73) on 5% this is not correct.

    So Can you please help me it how to calculate my Exact 20001 on 5%?
    I know it's really hard to explain but please let me if you have any suggestions.

  15. Hello, i need formula to calculate cost from one sites to another with many option, for example my good from city a to city b cost about $4 per quintal, from city a to c $5 per quintal, from city d to b is $7 per quintal. only 2 destination point but many source point with every point is different cost. thank you

  16. Hi, I'm looking for some help to automate an NBA bracket pool that I am currently running for my office.
    I am awarding 1 point for having predicted the correct series score (i.e. 4-0, 4-1, etc.). I have a single cell for each teams score (i.e. Team A's score will go in C5 and Team B's score will go in C9).

    I'm hoping there's a formula that can award 1 point for having corrected the correct score. I currently have a "master bracket" where I am entering the series scores as the playoffs continue. I've tried using a formula like =(IF(player1!C5='POOL - MASTER'!E5,1,0)) which will give me a point if correct, but this does not necessarily show whether player 1 guessed the correct series score. So, I need to somehow meet both conditions (or have both scores be correct) in order to award a point.

    Any ideas?

    Thanks in advance!

    LG

    2. *1 point for having PREDICTED the correct score

      1. I failed to mention that these formulas are on a "standings" sheet which I'm hoping will update automatically as I enter the series scores in the "pool - master" sheet.

  17. Hi

    I have a question to solve my case..

    I have a table:

    G1 to L1 : 1 2 3 1 2 3
    G2 to L2 : 4/26 4/26 4/26 4/27 4/27 4/27

    With condition :
    B3 : 4/26
    C3 : 3
    D3 : 4/27
    E3 : 3

    Im trying to make a Grant Chart, i already make it if the condtion just the date. But im not yet solve it with additional condition. Please help :)

  18. I need help with creating a formula in excel

    IF A5=5, then 0 points is assigned
    IF A5=4, then 1 point is assigned
    IF A5=3, then 2 points is assigned
    IF A5=2, then 3 points is assigned
    IF A5=1, then 4 points id assigned
    IF A5=0, then 5 points is assigned

    1. Just us Nested - If(AS=5,"0",If(AS=4,"1",If(AS=3,"2",If(AS=2,"3",If(AS=1,"4",If(AS=0,"5"," "))))))

  19. Hey please help me with the the formula for taking R12 from every R100 due .I have tried this but I can't get the correct answer when scrolling down ,=if(d6>=A4,B2,0)

  20. I have a question,
    If the opening size is >0.5= 3 then half opening size deduction
    If the opening size is >3 then full opening size deduction
    Ex Door = 2.0 x 2.1m = 4.2 Sqm is the opening size, then criteria 2
    If Door = 1.2 x 2.1 = 2.52, then half of the opening size (2.52/2) criteria 1

    1. Hi!
      I don't quite understand what result you want to get with our conditions. All the necessary information is in the article above. Or explain the problem in more detail.

  21. Is there an excel formula where I want that if the birthdate (Column C) is equal to 12 and is elementary level (Column G) then it will returns to the age (Column E)otherwise returns to "Underage"(Column E).

  22. Hi I need help with a nested if argument, I want to calculate a specific percentage according to an alphabetical code, all entries with codes (column F) R,P,B,W need to depreciate by 5% per year (column H) if the code is anything else the percentage is set at 50%

    This is what I have so far:

    =IF(or(F10="r",f10="p",f10="b",f10="W"),and(f10=n),100%-(H10*5)/100),"50%")

    1. H1!
      If I understand your task correctly, try the following formula:

      =IF(OR(F10="r",F10="p",F10="b",F10="w"),H10*0.95,H10*0.5)
      or
      =IF(SUM(--(F10={"r","p","b","w"}))>0,H10*0.95,H10*0.5)

  23. Hi,

    I'm trying to work out a formula which deducts travel time from appt time. Column 'P' is actual Appt duration, Column U is overall Appt duration (inc travel) I have the following formula which deducts actual appt time from overall appt time but wanted to include an IF statement saying take column 'U' if it = 00:00 (no travel time) so currently have the following: =IF(U135="00:00",U135,IF(U136<P136,P136-U136,U136-P136))

    However this isn't working as still calculates regardless of column 'U' being 00:00, any idea what I'm doing wrong?

  24. Hello,

    I am trying to use an "if and" statement with 3 conditions at the end of a very long statement.

    It is 3 IF statements, 1 IF AND statement, and 1 IF AND AND statement. The formula works up to the 3 IF statements + 1 IF AND statement, but does not work once I add the IF AND AND statement. I have copied below what I have so far, can someone please assist?

    IF(V2="*",S2,IF(S2="",T2,IF(S2="*",S2,IF(AND(S2="",T2="<"),"T2","U2",IF(AND(AND(S2="",T2=""),U2,V2))))))

    Above is translated to if V2="*", then look at S2. If S2 is blank, then look at T2. If S2="*", then use S2. If S2 is blank and T2 = "<", then look at T2, if not then look at U2. If S2 is blank, and T2 = "", then look at U2 if not then look at V2.

    1. Hi!
      Your conditions are incorrect. If condition IF(AND(S2=””,T2=”<”) returns FALSE, then there are 2 choices: 1) U2. 2) checking the condition IF(AND(AND(S2="",T2="”)

  25. Hi,

    I require formula for a confiriton where, "If we input date in one cel, then a specific amount should popup in the destination cell"

    Example: if a cell A15 has date 23/04/2022, then cell G15 should popup with the amount 50.

    will this be possible?

  26. Dear Sir,

    I noticed you are helping a lot of people here and i would like to ask if you could guide me in the right direction in excel. I am trying to create a worksheet with available lens (of all sorts) for our lab. And separate worksheets with lenses grouped for uses (contact lenses/ lenses by brands and stuff like that). My idea was for the lens name with its ID from the other worksheets to be copied into the availability worksheet (like this ='Contact Lenses'!D8). But when there is a date of taking the lens in the availability worksheet the worksheet that regards that specific lens (like 'Contact Lenses') will have in the relevant cell a value of Yes (for taken) and a No value for when there is a date of return in a separate column in the 'availability' worksheet. However The problem i am struggling with is that the same lens may occur several times in the same availability worksheet - making If statements kind of ridiculous.

    Things like started coming out as such - which obviously won't work
    'IF(OR(AND('Availability' $C10:C100 ISTEXT = "CL1.75 #A" , 'Availability' $F10:F100 ISBLANK), AND('Availability' FC10:F100 ISTEXT = "CL1.75 #A", ISTEXT )), "Yes", "No")

    ('Availability' worksheet) - Cells[Lens ID, date of taking item, name of person who took it, date of returning item]

    Example 'Lens' worksheet - Cells [Lens ID, Specification, Available?]

    I hope i explained it well enough. Do you have an idea on how to proceed with such a task?

    Regards
    Wiktor

    1. Hello!
      If you need to find the last match with Lens ID in the ‘Availability’ worksheet, you can use the XLOOKUP function with Search_mode = -1
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  27. I am trying to get my IF formula to work with multiple conditions. Here are the conditions:
    1. Cell C4 must contain a certain term, let's call it "apple".
    2. Cell M4 must contain a numerical value.
    If both conditions are met, I would like to subtract the value in cell M4 from the value in cell D4.
    If one or both of the two conditions are NOT met, I would like to return the value "N/A"

    Thanks in advance for your help!
    Mike

    1. Hi!
      To check if a cell contains a number, use the ISNUMBER function. Also, use the recommendations from the article above.

      =IF(AND(C4="apple",ISNUMBER(M4)),D4-M4,"N/A")

      This should solve your task.

      1. Thank you Alexander, this worked well. I was not employing the ISNUMBER function, but I had the rest of the formula elements. I really appreciate the swift response.

  28. I have. project and I'm struggling to know how to do this IF formula given what they say:
    "The dataset contains ten measurements of student readiness for college in columns B-K. The description of each one of them is available in the file."
    "To analyze this data, you need to create additional columns and recode the data, making low a 1, moderate, a 2, and High a 3 (you can use an IF function to do this). In this format, the highest the number, the more prepared the student is."

    the column already says moderate low and high what function would I do to get it to formulate to 1,2,3, etc...

  29. Sorry not all the context was stated in my message, The results , need to be
    Poor (345)

    1. Hi!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =IF(J2 > = 5,"top",IF(J2 > 3,"good","poor"))

      If this is not what you wanted, please describe the problem in more detail.

  30. Hi Alexander, I am hoping you can help me, its probably a simple formula, I just can't seem to get my head around.
    I have a list of scores ( results) in one column, from these scores I need to determine, poor ( 345). preferably the result being the corresponding text.

    Average Total Output
    2.3
    2.4
    2.5
    2.6
    2.7
    2.8
    2.8

    This is my formula so far: =IF(J2>=5,"top",IF(J23,"good",IF(J2>33>0,"poor",)))) but its not giving me the result I am looking for. Help appreciated.

  31. Hello

    Please assist me with this scenario:

    Bus fare from A to B - $30 for the elderly
    Bus fare from A to B - $50 for adults
    Bus fare from A to B - $40 for teenagers
    Bus fare from A to B - $30 for kids

    Bus fare from B to C - $35 for the elderly
    Bus fare from B to C - $55 for adults
    Bus fare from B to C - $45 for teenagers
    Bus fare from B to C - $35 for kids

    I receive trip sheets with passengers' dates of birth and destinations so I want create a formular that says if a passenger is 13 years old, and travelled from B to C, they should have paid $45

    1. Hello!
      Pay attention to the following paragraph of the article above - Using multiple IF statements in Excel (nested IF functions). It contains answers to your question.
      You didn't provide enough information to write you a formula.

  32. Good afternoon,

    I need two different formulas for percentage increase/decrease in one cell.

    For more clarification...I am compiling statistics. July 2020 has 0 (crime reported) and July 2021 has 1 (crime reported).

    I used this formula: =IF(ISERROR((L23-L4)/D4),0, (L23-L4)/L4) and the percentage is 0%.
    I need to show the 100% increase from 2020-2021.

    So I used this formula : =if(iserror((L23-L4)/L4),1,(L23-L4)/L4) and the percentage changed to 100%.

    Some months have say 5 (reported crimes) in 2020 and 13 (reported crimes) in 2021. For those, the formula =IF(ISERROR((D34-D15)/D15),0, (D34-D15)/D15) works.

    Is there a way to combine both formulas for each cell in order to give me an and/or result?

    Thank you kindly for your response.

    1. Hello!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =if(ISERROR((L23-L4)/L4),1,IF(ISERROR((L23-L4)/D4),0, (L23-L4)/L4))

      I hope it’ll be helpful.

  33. Hello. I need to combine two functions and need help please.

    two cells H177 and H65

    when H77 is <=0,2 i want to calculate the abs difference and if is 0.05 ok, if not Failed
    =IF(AND(H177<=0,2;ABS(H177-H65) 0.2 it responds as Failed,

    but how can i combine
    when H77 is >0,2 ABS((H177-H65)*100/H77)<20);"OK";"FAILED")

    1. Hi!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =IF(H177<=0.2,ABS(H177-H65),IF(ABS((H177-H65)*100/H77)<20,"OK","FAILED"))

  34. Hi, I am trying to do the following:
    If c2 is blue (true or false) then c3=c2 - 8

    If c2 is another colour nothing happens

    So.. is it possible to bind c3 to a c2 in that way and still be able to write any number in c3 if condition is not met?

    Thanks

  35. I have a list of prices. "X" and "NON-X" products (one column). I want the price separately to the "X" Column and "NON-X" Column from that mixed list..

  36. Hi,

    I am trying to create a code for trading.

    C3 is Australian Dollar
    D3 is Euro
    bull is up
    bear is down
    cons is consolidating/sideways

    There is 8 different combinations for the answer that can be seen in the function below.

    =IF(AND(D3="bull";C3="bear"); "bull";"") IF(AND(D3="bear";C3="bull");"bear";"") IF(AND(D3="bull";C3="bull"); "cons";"") IF(AND(D3="bear";C3="bear"); "cons";"") IF(AND(D3="bull";C3="cons"); "bull";"") IF(AND(D3="bear";C3="cons"); "bear";"") IF(AND(D3="cons";

    1. Hello!
      If I understand the problem correctly, you have many combinations of conditions in two columns. I recommend writing down all possible combinations in a table and using the VLOOKUP function to search for the desired option. Here is a guide with examples: How to Vlookup multiple criteria in Excel.
      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Thank you for the quick response!

        I digged into VLOOKUP function and I am not 100% sure how to use it in my instance. I will try to further explain my situation.

        Basically I have 8 possible combinations and 3 possible outcomes, but just one right answer.

        First word is one currency (Australian Dollar)
        Second word is the other currency (Euro)
        Answer is what their cross currency should be if conditions are met (EURO / Australian Dollar)
        bull + bull = cons
        bear + bear = cons
        bull + bear = bear
        bear + bull = bull
        cons + bull = bull
        cons + bear = bear
        bull + cons = bear
        bear + cons = bull

        There can only be one right answer for each date.

        1. Hi!
          Write your criteria in the G2:H9 range as:
          bullbull cons
          bearbear cons
          bullbear bear
          bearbull bull
          ......

          Formula in cell C2:

          =VLOOKUP(A2&B2,G2:H9,2,FALSE)

          Please use the link I gave you earlier.
          I hope I answered your question.

    2. Here is the full function

      =IF(AND(D3="bull";C3="bear"); "bull";"") IF(AND(D3="bear";C3="bull");"bear";"") IF(AND(D3="bull";C3="bull"); "cons";"") IF(AND(D3="bear";C3="bear"); "cons";"") IF(AND(D3="bull";C3="cons"); "bull";"") IF(AND(D3="bear";C3="cons"); "bear";"") IF(AND(D3="cons";C3="bull"); "bear";"") IF(AND(D3="cons";C3="bear"); "bull";"")

  37. Hi, I have 2 columns of data, both containing either a 0 or a 1.

    I need to be able to code the cell in another column to read the data and return a number...
    i.e : when it is 0 in both columns, to return a 0.... then 1 , 0 to return a 1... etc.

    I have tried this;

    =IF((AND(D2=0, E2=0),0), IF(AND(D2=0, E2=1), 1), IF(AND(D2=1, E2=0), 2), 3)

    Any assistance would be greatly appreciated.

    1. Hi!
      If I understand your task correctly, try the following formula:

      =IF(AND(D2=0,E2=0),0,IF(AND(D2=0,E2=1),1,IF(AND(D2=1,E2=0),2,3)))

      1. Thank you so much for your speedy response. This resolved the issue.
        So close, yet so far. Bracket, everybody's best friend and worst enemy.

  38. Range Commission %
    > 800 16.0%
    > 600 to 400 to 299 to < 399 22.0%
    < 299 23.0%

    I have a large set of values I need to calculate the commission. How do I apply the formula in excel to calculate the commission value? Please advise.

  39. Hi, I'm trying to create a formula to show the following:

    If residency column is "Res" and "Off" = 32068
    If residency column is "Res" and "On" = 31846
    If residency column is "Nonres" and "Off"= 61564
    If residency column is "Nonres" and "On" = 61342

    Thanks!

    1. Hi!
      Please re-check the article above since it covers your case. I can't offer you a formula because I don't understand how you would write two values in one column (for example, "Res" and "Off")

  40. Hi
    I have multiple conditions to fulfill as described below, I am trying to use IF formula but it is not working. Please suggest.

    If a customer selects qty between 0-10 then no discount of total price.
    If a customer selects qty between 10-20 then 5% discount of total price.
    If a customer selects qty between 20-30 then 10% discount of total price.
    If a customer selects qty >30 then 15% discount of total price.

    Thank you

  41. I have problem, I have to values for L21 & L23 and final outcome is CA..

    CA= L23*3+L21*2 if value of L23 is greater than 0
    Like - L23 = 3 L21 = 2, then CA = 3*3+2*2=13

    and if L23 =0 and L21 =3, then CA=0

    Pls suggest farmula

    1. Hello!
      If I understand your task correctly, try the following formula:

      =IF(L23>0,L23*3+L21*2,IF(L21=3,0,""))

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

      1. I'm trying a formula where data exixts in one sheet, i need answer in one sheet with below condition, Can you please provide the formula for this

        Sheet 1: Answer & Sheet 2 : Existed Data

        I'm inputting formula in Answer sheet with below conditions

        If G column Data matches with A Column Data of "Existed Data and
        If J Column Data matches with B Coumn Data of "Existed Data and
        If Xcolumn Data matches with C columnd Data of : "Existed Data then
        answer to be appear which is in D column of Existed Data.

          1. I'm getting an error at end as The formula is missing an Opening or Closing Parenthesis.

              1. can i select entire column instead of cell

                =if(AND(Sheet1!A:A=Sheet2!A:A,Sheet1!B:B=Sheet2!B:B,Sheet1!C:C=Sheet2!C:C,=Sheet1!D:D,"")

              2. Hi!
                If I understood your task correctly, you want to compare cells in each row. Therefore, use a relative cell reference to each individual cell in the formula. After that, you can copy this formula down along the column.
                I also draw your attention to the fact that the use of column references in formulas significantly slows down the speed of Excel calculations.

  42. In a drop down tab there are 4 names, I want Excel to read selected name and some of the data can be written that person sheet in real time or command to copy

    I am totally beginner love to learn, I appreciate you had well explained the use of IF AND OR
    Thank you
    Hameed

  43. Hey! I want excel to perform different calculation depending on the outcome of a fraction, ive tried the following formula but cant get it working, how should i put it instead?

    =IF(SUM(I2/Q2)0.30.50.7, "R2*0.5", "R2*0.75", "R2*0.9", "R2*1.0"))

    1. Hi!
      I can't guess what conditions you want to check in your formula. Please describe them. However, all the necessary information to write the formula is in the article above.

  44. I have a problem where scoringh is made based on separate conditions being met and i am stumped.

    Example: If name = Type 1 and Condition = Y, score 2, otherwise score 1.

    The formula below doesn't seem to work properly.

    =IF(AND(Y2="Y"),IF(AND(P2="Type 1"),2,1))

    Additionally, If neither is it possible to have 0, rather than FALSE?

    If anyone is able to answer this for me, i'd be extremely grateful :)

      1. Thanks for your help, that's great!

  45. hi need this code to correct please help

    i have date on M17 given and the are encode MM/DD/YYYY
    i want to auto fill the date if 1st quarter or 2nd and so on.....

    this is my sample code but the result is always 4th Quarter need help to correct it thank you

    =IF(M17>=10/1/2022,"4th Quarter",IF(M17>=7/1/2022,"3rd Quarter",IF(M17>=4/1/2022,"2nd Quarter",IF(M17>=1/1/2022,"1st Quarter"))))

    2. i have date on M17 given and they are encoded on this format MM/DD/YYYY dateshort

  46. I will use this ( if(vlookup(a2, sheet1! A:ap, 42,0) ="TN", A2, "") fourmal this condition only sow TN state,
    Now I need TN & KA State which fourmal use plz help????

  47. Hello,
    I am trying to make eGFR calculator. It needs to differ between men and women and different serum concentrations of creatinine (SCr). Formula reads as follows:
    142 x (Scr/A)^B x 0.9938^age x (1.012 if female), where A and B are the following:

    Female Male

    SCr ≤0.7, A = 0.7 B = -0.241 SCr ≤0.9 A = 0.9 B = -0.302

    SCr >0.7 A = 0.7 B = -1.2 SCr >0.9 A = 0.9 B = -1.2

    So what I need is to Excel calculate/show value of B in one cell when specific conditions are met. For example patient is female and SCr is 0,55 (it is <0,7) B should be B=-0,241 and etc.
    Is it possible to test combinations of two variables (sex and SCr) to calculate three possible outcomes of B (-0,241, for women, -0,302 for men, and -1,2 in cases when SCr is greater than 0,7 for women, 0,9 for men, respectively)?

    Looking forward to your answer

    Edit of previous comment due to error.

  48. I please need help doing a formula for the following, which should just be one formula :Combine these into 1 formula
    if cell A is empty , it must use cell B info , if cell B is empty, it must use cell A info , If both cell A and B has info in it then it must use Cell B info, If no info then it must show a clear cell and not a 0.

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

      =IF(AND(ISBLANK(A1),ISBLANK(B1)),"", IF(ISBLANK(B1),A1,B1))

      To not show 0 in an empty cell, use custom number format.

  49. Hi there, I am looking for a formula which will return the following result:

    If text in cell A1 matches the text in cell range A1:A100, then show text from cell B1, assuming A1 is Name and B1 is Surname, please help.

    1. Hi!
      I’m not sure I got you right since the description you provided is not entirely clear. Your terms and conditions are incorrect. The text in cell A1 will always match the text in range A1:A100 in cell A1.
      If you want to find the corresponding surname by name, then use the VLOOKUP function. For example:

      =VLOOKUP("Name",A1:B100,2,0)

  50. Hi

    =IF(OR(AND(L37="Yes"),OR(M36="Yes",N36="Yes",M37="Yes",N37="Yes")),"Yes","No")

    Effectively, the formula should be saying if L37 is Yes, and M37 or N37 is Yes, then give Yes, or if L37 is No, and M36 or N36 is Yes, then give Yes. Everything else, give No.

    the above formula is coming back as a Yes where it should say No.

    are there any other formalas i can use?

    1. Hello!
      If I understand your task correctly, try the following formula:

      =IF(AND(L37="Yes",OR(M37="Yes",N37="Yes")),"Yes", IF(AND(L37="No",OR(M36="Yes",N36="Yes")), "Yes","No"))

      Please re-check the article above since it covers your task.

Post a comment



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