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

  1. Anyone know why my formula is not working?
    Column B Year 2021, 2020, 2019, down to 1999
    Column C Week Number 2,3,4,5,6, up to 53
    Formula being used: =IF((AND(B40327=2021, C40327=52)), "Yes", "No")
    Result is No in everything even though I have 57 that should meet the condition of equalling 2021 in B and 52 in C

    Can anyone help please?

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Why does 57 have to match 2021 and 52?

  2. Please help to put the calculation of incentive
    Conditions:-
    * 1% Commission for the debt collection with in 10 days after invoice submission.
    * .75% for with in 10-30 days after invoice submission.
    * 0.50% for the debt with in 30-60 days after invoice submission.
    * 0.25% for the debt with in 60-120 days after invoice submission.
    * Nothing for the debt for more than 120 days after invoice submission.

  3. 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

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

    conditions

    *Incentive structure*
    Target quarter 1 quarter 2 quarter 3
    1 to 5 2% 2% 2%
    6 to 10 3% 3.5% 4%
    11 to 25 4% 4.5% 5%
    26 to 50 4.5% 5% 5.5%
    51 to 100 5% 5.5% 6%

    please help

  5. vinesh/mahesh 9 5 5 emi/kelly 4 9 3

    3 games - 1st winners 9, second game opponents 9, third game 5 in the forth cell I want to put 2 games for first pair and the opponents 1 game.
    How do I do that? I'm 76 and struggling on this one.

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

    Last one is not multiple
    please help

  7. 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.

  8. 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

  9. 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.

  10. Plz help,

    if (b1+c1>=20)+ (b2+c2>=46)= >79 it will be A+ how to apply

    1. Hi!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get?

  11. Please
    how will I write a formula whereby I weight range value and want to group these weights. (if the weight is 0-3.5tons then excel gives me 3.5<7.5tons......) something of the sort.

  12. 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.

  13. Thanks for the tips on using the IF condition.
    However, I would like to share my doubts regarding the topic under discussion. The question is:
    I have this formula, below, where I$3 are the hours, which range from 0 to 10; G and H are the entry and exit times.
    =IF(AND(I$3>=$G4,I$3<=$H4),$F4.0)

    I want excel to fill in the cells that fall between the entry time and exit time, but in cases where the entry time is greater than the exit time (for example when the worker enters at 8 pm to leave at 6 am) the above formula is no longer valid.
    Therefore, I ask for your help in improving the formula.
    Thanks

    1. Hello!
      I don't understand how your formula works. If you use time, then the formula does not work for the interval from 13-00 to 23-00. Do the cells contain the time or the usual numbers from 1 to 12? Give examples of source data.

  14. Greetings friends, I am working on a spreadsheet for my work but I do not know how to make the calculator add the odd results by a constant figure, for example by 10, I would appreciate any help thanks.

  15. 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.

  16. 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

  17. HI there,

    I am trying to do the following -

    A1 = Target date
    B1 = Completion date
    C1 = State (Open / OVERDUE / CLOSED)

    If the target date from column A is met column C will state OPEN - =IF(A1=TODAY,"OPEN"
    and if it has gone past the target date it states OVERDUE - =IF(A1<TODAY,"OVERDUE"

    however I also need the formula to consider column B which is the date of completion
    IF 'TODAY' has hit/gone past completion date column C will state CLOSED and ignore the previous part of the formula.
    If no dates have been entered column C will remain black

    Please help ?

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      Your explanations are not very clear, but I guess the formula could be something like this:

      =IF(A1=TODAY(),"Open", IF(AND(A1 < TODAY(),B1 > TODAY()),"Overdue", IF(AND(A1 < TODAY(),B1 < = TODAY()),"Closed", "")))

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

      1. I am looking for something similar, but different:
        A1: ETA
        B1: Quantity due by ETA
        C1: Late if not received by today

        =IF(AND([@ETA]<[@[Late if not received by this date]],([@[Due In]]=0)),"Closed","Late")

        I am using this formula, it is *almost* working. For those dates that are before today, it is fine, but for future dats it is labeling Late. I am unsure how to add to the formula to read Future.

        Could you assist?

        Thank you.

        1. Hello!
          It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work.
          The simplest formula looks like this:

          =IF(A1>=TODAY(),"Close","Late")

  18. =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*

  19. 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.

  20. 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

  21. tomorrow is my assignment test so do you send me dum excel file to practice all advance formula i.e, multiplr if,sumifs,index,power query,time and dates subtraction,pivot table...and more,,,
    which will help me to build my confidence....please help me ???

  22. I'm attempting to do a nested If/or to populate cells based on text contents of another cell, regardless of case of the words in the source cell.
    Basic summary: If A1 contains the word "Normal" or "normal" in the text, A2 = "Normal"
    If "abnormal" or "Abnormal", A2 = "Abnormal"
    If "no call" or "No Call", A2 = "No Call"

    What I have currently is below, but I can't find the error.

    =IF(OR(((ISNUMBER(SEARCH("abnormal",a3)),"Abnormal"),ISNUMBER(SEARCH("no call",a3)),"No Call"),ISNUMBER(SEARCH("normal",a3)),"Normal"))

    1. Hello!
      Please re-check the article above since it covers your case.

      =IF(ISNUMBER(SEARCH("abnormal",A3)),"Abnormal", IF(ISNUMBER(SEARCH("no call",A3)),"No Call", IF(ISNUMBER(SEARCH("normal",A3)),"Normal","")))

      1. Thank you for your help! I hadn't realized I was missing that set of quotation marks. I overcomplicated it for what I needed, it seems.

  23. I am trying to create an IF/AND Statement with 2 criteria. The criteria are below. Each point value needs to have both the start and spread value achieved to hit that point value.

    Point Value Starts/Spread

    24 8 / $3,300
    18 7 / $2,900
    12 6 / $2,500
    6 5 / $2,100

  24. 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....

        1. Hi!
          The formula doesn’t work since it has been created based on the details you provided in your first query. I see from your subsequent comment that your task differs from the one you originally described. Time was wasted. I think that using this formula and the recommendations of the article above, you can find the solution you need.

          1. Based on my latter inquiry what would the formula be? I really need help with this. Unable to come up with the entire formula on my own.

  25. Thank you for this, it really helped me a lot on my excel in OPERATIONA MANAGEMENT AND TQM if some may ask you could also do this.
    =IF(OR(AND([@Item]="Pencil",[@Units]>9,[@Total]>1000,[@Region]="Central"),AND([@Item]="Binder",[@Units]>3,[@Total]>1000,[@Region]="Central"),AND([@Item]="Pen",[@Units]>4,[@Total]>1000,[@Region]="Central"),AND([@Item]="Desk",[@Units]>1,[@Total]>1000,[@Region]="Central"),AND([@Item]="Pen Set",[@Units]>2,[@Total]>1000,[@Region]="Central")),"Yes","No")

    or

    =IF(AND([@Item]="Desk",[@Units]>1),"5%",IF(OR(AND([@Item]="Pencil",[@Units]>9),AND([@Item]="Binder",[@Units]>3),AND([@Item]="Pen",[@Units]>4),AND([@Item]="Pen Set",[@Units]>2)),"2%","0%"))

    "as an example"

  26. 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.

  27. Im trying to Combine two IF Functions into a single Cell:
    =IF(G11>=M13,"SUBJECT-MAJOR",IF(G11=0,"SUBJECT-NO",IF(G111, "SUBJECT-MINOR")))
    and
    =IF(I11>=4,"SUBJECT-MAJOR",IF(I11=0,"SUBJECT-NO",IF(I110,"SUBJECT-MINOR")))

    Can you help me given the scenario
    Score is 100
    20% of 100 is 20
    Scenario :
    0 and 0count is Subject No
    <20 and 1 count is Subject Minor
    <20 and 2 counts is Subject Minor
    <20 and 3 counts is Subject Minor
    = to 20 and >=1 is Subject Major
    >20 and >1 is Subject Major

    Pls Help how to combine two IF Functions in one single Cell

  28. I Want Formula

    0 to 10 = 01
    11 to 20 = 02
    21 to 30 = 03
    31 to 60 = 04
    61 to 100 and above = 05

    please suggest.....Thanks in advance

  29. Examples I have same description in A1 and different quantity in B1, the question is how i can capture all the quantity using the A1 description only. What formula should I used?

    Thanks for the answer

  30. Hello, I have created a formula that has both the IF and IF(AND) included, based on the various scenarios, but it is coming back with #Value error. Please see below. Appreciate your help. What alternative formula may I use?
    =IF(P12>=4,100%),IF(P12=3.5,P12=3,P12<3.5),50%)

    Thanks much.

    1. Hi!
      The formula is written incorrectly and will not work. But I can’t give you any advice, because I don’t know what you want to calculate. Read the article carefully. I think this will help. Or describe the task in detail.

  31. 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')))

  32. 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. Hi!
          I gave you a link to the comment you want. Please note the paragraph above "Using multiple IF statements in Excel (nested IF functions)"

          1. i've try this formula, want to past TEXTVALUE OF ( "0-34",''35-50''OR 50-71 SON ON..) NEXT TO A COLOUMN

            A B
            35 0-35
            55 51-71
            91 90 AND ABOVE

            =if(A2=35,A2==51,A2<=70,"51-70",..so on) But i did'nt get ans in B Column text range (0-34,35-71,....so on

            JUST NEED RIGHT FORMULA TO PAST in cloumn B , " 0-35" or, "35-51", or "51-71", as per their Marks column " A"

            1. thanks i got this formula,

              '=IF(A2<=34,"0-34",IF(A2<=50,"35-50",IF(A2<=70,"51-70",IF(A2<=90," 71-90",IF(A2<=100,"91-100",IF(A2=131,"131 and above")))))))

        2. 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

  33. if A2>B2, MAKE A2-B2, OR B2-A2

  34. 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!

  35. 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)

  36. 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

  37. 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’.

  38. 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.

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

  40. I two columns, one has cities and the other column some cells empty,

    i will create 3rd column if the second column is empty get the data from the first column and if not empty get the data from the second column.

    Thanks

  41. My difficulty is if i chose cell from another sheet which have "DATE" formula. And i want to change that date into another date to in the cell in which i have to do the modification. So which formula i should use, So i get direct result.

    1. Hi!
      I don't understand very well what you want to do. But I hope you know that the formula only changes the value of the cell in which it is written. If this is not what you wanted, please describe the problem in more detail.

  42. Kindly help me with this -

    From(PCPM )000 To(pcpm ) Inc %
    0 0.749999 0
    0.75 1.24999 0.0175
    1.25 1.74999 0.025
    1.75 2.24999 0.03
    2.25 2.749999 0.0325
    2.75 3.249990 0.0375
    3.25 9.9999 0.04
    3.5 0.04

  43. I need help with mine.

    I have 4 categories, SA, SB, SC, SD. These categories have their consequent ranges of pass or fail.

    For example,
    If SA is less than or equal to 20, pass
    SB less than or equal to 100, pass
    SC <= 200, pass
    SD <=400, pass

    Thanks!

    1. Hi!
      Pay attention to the following paragraph of the article above — Using multiple IF statements in Excel (nested IF functions). There is an answer to your question.

  44. A=0 AND B=0,"1"
    A=0 AND B<0,"2"
    A<0 AND B<0,"3"

  45. unfortunately Using IF & AND only work for 2 cells at a time but if we have more than 2 cells/column to compare then it would not work in excel.

  46. Hi

    Would you please help me to make the formula in excel:

    1 Underweight <18.5
    2 Normal weight (18.5–24.9)
    3 Overweight (25–29.9)
    4 Obesity (BMI of 30 or greater)

    I have made this
    =IF(E2=18.5, E2=25, E2=30, "4",))))

    But it does not work.

    1. Hello!
      Have you tried the ways described in this blog post? Please re-check the article above since it covers your case.

      =IF(A1<18.5,1,IF(A1<24.9,2,IF(A1<29.9,3,4)))

  47. =IF(G10=1828,"1.770",IF(G10>2558,"1.812")))

    if > 2558 result 1.812 not working

      1. =IF(G10=1828,"1.770",IF(G10>2558,"1.812")))

        Dear Alex
        Now My G10 Value is > 2558 but the result is 1.770 (wrong result)
        By formula 1.812
        First two condition working ,

  48. Hi,

    J6= 1,62

    Why it`s not working??
    =IF(0.55>=J6<=0.8,0.55,IF(0.8<J6<=1.2,1.075,IF(1.2<J6<=1.8,1.6)))

    Many thanks

    1. Hi!
      Instead of 1.2 < J6< 1.8, you need to write a condition with the AND operator in the formula, as described in the article above: AND(J6 > 1.2,J6 < 1.8). The expression 0.55 > = J6 < = 0.8 doesn't make sense.

  49. do you have an email to send you the attachment as well?

  50. In excel calculation we have more than 4 types of rate for set of party's (ie: Wholesale Partys, Retail Partys, and other partys).

    Formulas is just one type but how could calculate for different types of partys?

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