Comments on: Excel Nested IF statement: examples, best practices and alternatives

Though very popular, the nested IF statement is not the only way to check multiple conditions in Excel. In this tutorial, you will find a handful of alternatives that are definitely worth exploring. Continue reading →

Comments page 6. Total comments: 231

  1. I need to do an IF statement to get a range for:
    Volumes Greater than and equal to 2,000,000
    Volumes Less than 2,000,000 but greater than and equal to 200,000
    Volumes less than 200,000 but greater than and equal to 500
    Volumes less than and equal to 500

  2. I just want to say thank you. I was able to create an if-choose on my excel thanks

  3. Awesome article. I switched nested ifs for Lookup - made it so much easier. Also now I can go to the lookup table and change values without having to copy paste the formula again. Thanks....

  4. I need help in defining the reorder level using "IF" or any other formula in excel
    Column A = Shortage = 18500
    Column B = MOQ = 5000
    Column C = Reorder level = ?
    I would like to calculate the reorder level as follows :-
    (ie. Reorder level should be = to MOQ if shortage is less than MOQ
    OR Reorder level should be 20000 if shortage is between 15001 & 20000
    ie. Reorder level should be in multiples of MOQ but > shortage
    Kindly confirm how to use "IF" formula or any other formula in excel

    1. Tony,
      You do not need an IF function for this.
      Try:
      =CEILING(shortage, 5000)

      This will round your shortage number up to the next 5000.

      K

      1. Thanks a lot. it did work

  5. Error in your explanation of the SWITCH function. Line 1, you use SWIFT. I expect you mean SWITCH. #yourewelcome

    1. Of course, I meant SWITCH. Fixed, thank you!

  6. I have a formula I am trying to use for Overbillings and underbillings, each a separate column. If I use one column the formula looks like this:
    =IF([@[% Comp]]<30%,[@[Earned Cost]],[@[% Comp]]*[@[Contract Amount]])-[@Billed]
    but I want it to give the result of zero if the answer to this in the overbillings is over zero. What do i add?

  7. I am attempting to create a formula with IF statements.
    Here it is:
    G6 =TODAY() which inputs today's date.
    J6 If "Yes" is selected
    L6 will input TODAY()+7
    =IF(J6="Yes",G6=TODAY()+7) the result will go into L6
    Thank you

  8. I am attempting to create a dynamic table where the value in one cell makes another equal a 3rd cell plus the data in another cell. So for example, =IF(F2=P7,G2+Q7,"-") I can get it to work with that one---but I need to nest that statement with 10+ others of the same type (=IF(f2=P8,G2+Q8) etc etc (pulling data from a table). I'm lost.

  9. Not working. Please help please. I need to use 16 conditions for if statement but as you see, it is only 10 and not working. It said that "this formula uses more levels of nesting than you can use in the current file format". Anyone please.

    Thanks!

    =IF(S10>=95,20, IF(S10=94,19, if(s10=93,18, if(s10=92,17, if(s10=91,16, if(s10=90,15, if(s10=89,14, if(s10=88,13, if(s10=87,12, if(s10=86,11))))))))))

    1. I know this is kind of late, but try this:

      =IF(S10>=95,20, IF(and(S1085),s10-75,))

      1. For some reason that did not format correctly. I will try it one more time:

        =IF(S10>=95,20,IF(and(S10 85),s10-75,))

  10. Can I use nested IF to do the following :
    I have a column with three possibilities entered in the cells : ABC DEF GHI

    the next column in the contagious cell needs a number based on the above so that :
    ABC would be 123
    DEF would be 456
    GHI would be 789

  11. I have 2 work sheets, the first one named Property 1 and the 2nd one named summery.
    In the first sheet I have expenses listed by date, amount and category. the categories are Repairs,Labor,Materials,Transport,Advertising and Commission.
    I would like to sum each category monthly (Jan,Feb.....) and display the total on the Summery sheet.

  12. I'm trying to make a formula along the lines of if D3>= 2300 multiply by J3, once 2300 is exceed the difference should be multiplied by K3. Is this possible?

  13. Rustin.
    Your formula is missing the condition. If 'UTILITY Rates'!A2:A13 equal, larger, contains, etc

  14. My formula wont work, can anyone see where I am going wrong?
    =IF('UTILITY Rates'!A2:A13,'Utility Score Card'!C1,"'UTILITY Rates'!C2")

  15. Hello. I have look different ways, but I'm running out of time to complete a project I'm working on. I have a table like this, is a changing pattern, so there is not actually a pattern, but there are some blank cells in between the text in Column A. In Column be I have tax names, which is just next. I need to be able to concatenate a formula with if or I don;t know to be able to concatenate tax names on the row with out blanks, I need to delete the blanks but have that information in every text
    A B C
    Text1 tax1 tax1,tax2
    blank tax2 ------------
    text2 tax1 tax1,tax2
    blank tax2 --------------
    text3 tax1 tax1
    text4 tax1 tax1
    text5 tax1 tax1
    text6 tax1 tax1,tax2,tax3,tax4,tax5
    blank tax2 --------
    blank tax3 --------
    blank tax4 ---------
    blank tax5 -------
    text7 tax1 tax1,tax2,tax3,tax4
    blank tax2 ------------
    blank tax3 ------------
    blank tax4 -------------
    text8 tax1 tax1
    text9 tax2 tax2
    text10 tax3 tax3,tax2
    blank Tax2 ---------
    test11 tax1 tax1

  16. I'm having trouble with this formula. I have three conditions and they need to all be true to return "TRUE". If any are not true, it should return "FALSE". Here's what I've tried now, which does not work.
    =IFS(C:C="CSD_SERVICES", "TRUE", (K:K=0,"TRUE", (O:O="","TRUE","FALSE")))
    I've tried probably 25 different iterations but so far none returns the correct answer.

    1. Hi,
      It seems to me that any of the two formulas below can help you with your task:

      =IF(AND(C:C="CSD_SERVICES", K:K=0, O:O=""), "TRUE", "FALSE")

      =IFS(AND(C:C="CSD_SERVICES", K:K=0, O:O=""), "TRUE", TRUE, "FALSE")

      Please note that the necessity of one more 'TRUE' in the second formula is dictated by the syntax that the Excel IFS function has. Please press 'F1' if you feel like looking into it.

  17. if D4<=4,
    true E4*50%,
    False E4*100% but if
    E45%, true E4*100%,false 5%

  18. IF sentens:

    About getting % when buying

    Ih I by <= 4 pallets I´ll get 60%
    If I by 5 but les than 9 pallets I'll get 60% and from his price I get ekstra 5 %

    J=Amount of Pallets
    L= My price (pallets times price pr. pallet)
    M= 60 %

    This works perfectly:
    =IF(J6<=4;L6;L6-(IF(OR(J6=5;J6<=9);L6*(M6))))*(0,95)

    My problem comes when I add this:
    10 pallets but than 15 pallets I get 10 % (so first the 60 % and than an ekstra 10 after the first results.

    Like this:
    =IF(J6<=4;L6;L6-(IF(OR(J6=5;J6<=9);L6*(M6))))*(0,95);(IF(OR(J6=5;J6<=9);L6*(M6))))*(0,93);(IF(OR(J6=5;J6<=9);L6*(M6))))*(0,90)

  19. I have a summary of invoices (positive) and credit notes (negative).
    When I knock off against payment made it shows zero.
    I am trying to make an if statement which would show:-
    If zero - "-"
    If more than .01 - "OS" (invoice)
    If more than -.01 - "OS" (CN)

    The aim is to filter what are the unpaid invoice and CN to generate
    a payment proposal for those outstanding.

    Thanks

  20. I'm using a nested IF formula for conditional formatting and to evaluate if the date in a cell is equal to today =IF(I2=TODAY(),I2,IF(J2="NDA",J2,IF(J2="SDA",J2))). I want the formula to stop if the date in cell I2 is not equal today. The formula should stop at the first false argument however the formula evaluates all the arguments and returns a NDA which is the value in J2. I have evaluated the formula using Formula Auditing and I get a false value whether or not my first logical test is nested in an nested IF formula.

  21. I have the following data in C7:
    S123 - using formula will result in D7 as 80123
    SA123 - using formula will result in D7 as 81123
    E123 - using formula will result in D7 as 82123
    EA123 - using formula will result in D7 as 83123
    C123 - using formula will result in D7 as 84123
    CA123 - using formula will result in D7 as 85123
    U123 - using formula will result in D7 as 87123
    UA123 - using formula will result in D7 as 86123
    I tried to nest IF statements but excel is returning an error that maximum nesting is reached.
    FORMULA:
    =IF(LEFT(C7,2)="SA",CONCATENATE(81,RIGHT(C7,3)),IF(LEFT(C7,1)

    ="S",CONCATENATE(80,RIGHT(C7,3)),IF(LEFT(C7,2)="UA",CONCATENATE(87,RIGHT

    (C7,3)),IF(LEFT(C7,1)="U",CONCATENATE(86,RIGHT(C7,3)),IF(LEFT(C7,2)

    ="CA",CONCATENATE(85,RIGHT(C7,3)),IF(LEFT(C7,1)="C",CONCATENATE(84,RIGHT

    (C7,3)),"-"))))))

  22. Dear Sir,
    Kindly confirm one coloum are value 1-15, and secound Coloum value 15-30, if kindly confirm which sort out the value's of Below & uper.
    & kindly confirm vlookup formulas fungtions.

    1. Thanks for the reply.

      When a name is selected from H67,
      The result in H68 should be 000234-UMPT.

    2. I created a drop box of names in cell H67. I wanted my nested if statement to return some numbers and alphabets when a name is selected but it's not working.

      Eg.
      =IF(H67="M.ISAAC","000234-UMTP",IF(H67="D.MICHAEL","000678-UMPT",""))

      The statement is not working. What am I doing wrong?

      Do you offer classes online?

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