• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Nested IF’s Explained

You are here: Home / Excel Formulas / Excel Nested IF’s Explained
Excel Nested IFs
September 29, 2010 by Mynda Treacy

Did you know we can use a nested IF formula to extend the number of logical tests and therefore, possible outcomes?  Simply put, this is multiple IF’s nested in the one formula.

Prior to Excel 2007 the limit of IF's you could nest in one formula was 7.  Excel 2007 has increased this to an outrageous 64.  I say outrageous, because in most cases if you’re using more than a few nested IF’s in one formula, there’s most likely a more efficient way to perform your calculation.  So don’t get carried away nesting!

Watch the Video

Subscribe YouTube

Download Workbook from the Video

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook from the video and follow along. Or Download the Excel Workbook from the written tutorial below. Note: These are .xlsx files please ensure your browser doesn't change the file extension on download.

IF Formula

In a previous tutorial we looked at the IF function (singular), which is one of the most versatile functions in Excel, but on its own you’re limited to only one of two outcomes. That is, if the answer to the question I am asking is true, do this, if not, do that.

Before we dive in to nested IF’s I want to recap our singular IF statement example to remind us how the logic works:

=IF(The number of units in column D is >5,Then take the Total $k x 10%, but if it’s not > 5 then take the Total $k x 5%)

So, if the answer to the question is true, you get outcome 1, and if answer is false you get outcome 2.

Nested IF Formula

Now let’s take a look at a more complex problem that a nested IF would solve.

In our spreadsheet below I’d like to enter the commission for each row in column G.  The commission rates are different for each region.  I’ve listed the different rates in columns I and J so it’s easier to follow.....and later we’ll make the formula more dynamic using this table, but let’s walk before we run.

Nested IF functions Explained

In Excel language our Nested IF statement would read:

= IF(logical_test, value_if_true, IF(logical_test, value_if_true, IF(logical_test, value_if_true,  IF(logical_test, value_if_true,.............so on and so on up to 64 iterations)

Let’s translate it into English by applying it to row B of our spreadsheet:

=IF(B2="Central", if so enter 3%, if not see if B2="East",  and if so enter 4%, if not see if B2="North",  and if so enter 5%, if not see if B2="South",  and if so enter 6%, if not see if B2="West",  and if so enter 7%, if not enter "Missing")

In Excel it would look like this:

=IF(B2="Central", 3%,IF(B2="East", 4%,IF(B2="North", 5%,IF(B2="South", 6%,IF(B2="West", 7%,"Missing")))))

In the above formula we’re telling Excel to put 3% in the cell if B2=”Central”, if not move on to the next IF statement and so on.  In the last IF statement, IF(B2="West",7%,"Missing"), we tell Excel to enter the word 'Missing' in the cell if all previous IF’s are false.

Alternatively we could instruct Excel to enter ‘0%’ or anything else we like in place of ‘Missing’.  Or, if we left this argument out altogether Excel would enter the word ‘FALSE’ for us by default.

Let’s make it better

With the formula the way it is we’d have to manually update the percentages for each region if we wanted to alter them.  And then copy and paste the revised formula down the column.  A better solution would be to link to the table in columns I & J.  Then if we updated the percentages in column J, our formula in column G would dynamically update.

For example:

=IF(B2="Central", $J$2,IF(B2="East", $J$3,IF(B2="North", $J$4,IF(B2="South", $J$5,IF(B2="West", $J$6,"Missing")))))

If we wanted to change a rate we’d simply change the rate in column J and it would dynamically update our formula in column G.

Nested IF formula example

You could take it one step further and link the IF statement to the region names as well, but I’ll let you play around with that when you download the practice spreadsheet.

I know I said at the beginning that you shouldn’t use more than a few Nested IF’s, and I’ve broken that rule here for the purpose of my example. In reality I would use the VLOOKUP in this scenario as it’s a simpler formula for both the user to interpret later on, and for Excel to compute.

Warning

Too many nested IF's can result in performance issues. In this tutorial you can learn alternatives to nested IFs.

Excel Nested IFs

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:IFERROR FunctionExcel’s IFERROR Puts an End to Messy Workarounds
Next Post:Excel IF AND OR Functions Explainedif or and if and formulas

Reader Interactions

Comments

  1. sumit

    April 27, 2019 at 2:42 am

    Hi, I have 4 warehouses (columns) listed in Excel. Each associated with a unique sku in each row. If each value in the warehouse shows a qty of 4 or more of a particular sku, I would like it to return a name value. I am having a hard time coming up with a formula for that. Any suggestions?

    Reply
    • Mynda Treacy

      April 28, 2019 at 2:46 pm

      Hi Sumit,

      It’s a bit difficult to visualise how your data is structured. Would you please post your question on our Excel forum where you can upload a sample Excel file and we can help you further.

      Thanks,

      Mynda

      Reply
  2. Arun Bhadra Khanal

    October 3, 2018 at 6:03 pm

    I can see there is a mistake in your statement but not in the formula.

    Reply
    • Mynda Treacy

      October 3, 2018 at 6:40 pm

      Not sure what you’re referring to, Arun. Can you provide more information?

      Reply
  3. Rex Makemson

    September 4, 2018 at 6:42 pm

    Hi,I’m having a little trouble sorting a conditional format for a blood pressure chart.the cells concerned are C2 (for the formula) cells D3 & E3.(example) Cell C2 needs to change colour to Orange if D3 is less than 120 or Red if greater than 140 AND/OR IF E3 is less than 60 for Orange and more than 100 for Red

    Your help please

    Rex

    Reply
    • Mynda Treacy

      September 4, 2018 at 8:31 pm

      Hi Rex,

      Please post your question and sample Excel file on our Excel forum where we can help you. There are too many variables in a conditional format like this to properly answer here in the comments.

      Mynda

      Reply
  4. Ron MVP

    October 8, 2016 at 3:53 am

    You might want to add a cross reference to this newer article:

    WHEN TO SAY NO TO EXCEL NESTED IFS
    https://www.myonlinetraininghub.com/when-to-say-no-to-excel-nested-ifs

    Reply
    • Mynda Treacy

      October 10, 2016 at 11:58 am

      Indeed. Thanks for the nudge, Ron. I’ve updated the post.

      Cheers,

      Mynda

      Reply
  5. Akke Blom

    July 21, 2016 at 9:37 pm

    Pff … still learning. Next step using it.

    Reply
  6. Marsha Ritter

    July 3, 2014 at 5:11 am

    I need a nested IF formula that looks at dates.
    If a date in cell B3 minus the date in B4 is equal to or greater than 1 year but less than 3 years, then I want it to return an answer of 40. If B3-B4 is equal to or greater than 3 years but less than 7 years, I want it to return an answer of 80. If B3-B4 is equal to or greater than 7 yrs but less then 15 yrs, I want it to return an answer of 120 and if B3-B4 is equal to or greater than 15 years, I want it to return an answer of 160. I can get the correct answer when using just the two first logical test, but when I try to put it all together, my return answer for every scenario is 160.

    Your help would be greatly appreciated

    Thank you

    Reply
    • Catalin Bombea

      July 3, 2014 at 2:03 pm

      Hi Marsha,
      The problem with IF nested statements is that the function stops when a criteria is met and returns an answer, regardless of your other criterias.
      Try:
      =INDEX({0,40,80,120,160},MATCH(A1,{0,1,3,7,15},1))
      Catalin

      Reply
      • Marsha Ritter

        July 4, 2014 at 4:57 am

        Catalin
        Thank you for your time. I guess I don’t know how indexes work. When I put the index formula in the formula bar for that cell I get a result of True when I need a result of 40, 80, 120 or 60

        I’m trying to take each employee’s hire date, subtract their anniversary date to get a result of how many hours vacation time they are due – 40 hours for their first and second year anniversary, 80 hours for their 3rd through 6 year anniversary, 120 hours for their 7-14 year anniversary and 160 hours for 15 years and over.

        Reply
        • Catalin Bombea

          July 6, 2014 at 3:27 pm

          Hi Marsha,
          Try this file from our OneDrive folder, you can download the file and test it. It’s the same formula and it returns desired values.
          Catalin

          Reply
  7. kathleen horkowitz

    December 5, 2013 at 1:54 am

    very good

    Reply
    • Mynda Treacy

      December 6, 2013 at 3:58 am

      Thank you, Kathleen 🙂

      Reply
  8. khalid

    August 6, 2013 at 3:41 pm

    Thx for such valuable work out

    Reply
    • Mynda Treacy

      August 6, 2013 at 11:30 pm

      You’re welcome, Khalid 🙂

      Reply
  9. BJ

    May 22, 2013 at 10:56 pm

    what would hinder a subtotal formular from responding on 2007

    Reply
    • Mynda Treacy

      May 23, 2013 at 9:57 am

      Hi BJ,

      Incorrect syntax probably, but without seeing it I don’t know. You can send your file to me at the help desk and I’ll take a look.

      Kind regards,

      Mynda.

      Reply
  10. jai

    April 17, 2013 at 5:59 pm

    how i use nested if’s function more than 15 times

    Reply
    • Carlo Estopia

      April 17, 2013 at 7:52 pm

      Hi Jai,

      There are a lot of ways to nest an IF statement.

      Here’s my favorite technique:

      IF Criteria
         True Value
         False Value --replace with new IF... IF Criteria
                                                 True Value
                                                 False Value --replace with another IF and so on
      

      Example:
      Data is A1 i.e. 1
      Formula is in B1

      =IF(A1=1,"ONE",IF(A1=2,"TWO",IF(A1=3,"THREE",IF(A1=4,"FOUR",IF(A1=5,"FIVE",IF(A1=6,"SIX",IF(A1=7,"SEVEN",IF(A1=8,"EIGHT",IF(A1=9,"NINE",IF(A1=10,"TEN",IF(A1=11,"ELEVEN",IF(A1=12,"TWELVE",IF(A1=13,"THIRTEEN",IF(A1=14,"FOURTEEN",IF(A1=15,"FIFTEEN","BLANK")))))))))))))))
      

      Please copy and paste this formula in the formula bar while in B1 (and not in the cell) and if Excel asks to check the error just click “yes”.

      Now the formula shows 15 IFs nested in this example. If value in A1 is 1 then “ONE” if 2 then “TWO” and so forth and so on.

      See IF BASICs

      Cheers,

      CarloE

      Reply
      • jai

        April 18, 2013 at 6:42 pm

        in data validation [ list ] unable to use this formula
        please help
        thanks

        Reply
        • Carlo Estopia

          April 18, 2013 at 6:45 pm

          Hi jai,

          Please send your file with your concerns here: Help Desk.

          Cheers,

          CarloE

          Reply
  11. Max

    April 17, 2013 at 9:48 am

    Hi Carlo,
    I’m hoping you might be able to construct this formula so it gives me the correct outcome I need in my speadsheet. It is a sharetrading spreadsheet I’m putting together and I’m trying to use nested ifs to make this work.
    I have the total value amount purchased in a cell then the next cell across contains the brokerage fees value depending on the amount purchased.
    Transaction amount Brokerage fee*
    $0 to $5,000 $15.00
    $5,001 to $10,000 0.30%
    $10,001 to $30,000 0.20%
    $30,001 to $50,000 0.16%
    $50,001 + 0.12%
    Working out your brokerage
    Different brokerage rates will apply to each component of
    your trade value above $5,000 based on the levels above.
    For example, if you placed a $35,000 trade your total
    brokerage would be $78.00. This is calculated as follows.
    Trade component Brokerage fee Total
    First $5,000 $15.00 $15.00
    $5,001 to $10,000 0.30% x $5,000 $15.00
    $10,001 to $30,000 0.20% x $20,000 $40.00
    $30,001 to $35,000 0.16% x $5,000 $8.00
    Total brokerage $78.00

    Thank you for any solution and really looking forward to the construction of this formula.

    Kind regards
    Max

    Reply
    • Carlo Estopia

      April 17, 2013 at 1:12 pm

      Hi Max,

      Please check this formula out. Assumption here is that the value is in A1.

      =IF(A1<=5000,15,IF(A1<=10000,15+(A1-5000)*0.003,IF(A1<=30000,15+(5000*0.003)+(A1-10000)*0.002,IF(A1<=50000,15+(5000*0.003)+(20000*0.002)+((A1-30000)*0.0016),15+(5000*0.003)+(20000*0.002)+(20000*0.0016)+((A1-50000)*0.0012)))))
      

      Note: I have noticed that when you copy the formula in the cell, it will come out as a text. I prefer you paste it in the formula bar. Just click yes whenever you are prompted by a question that Excel wishes to correct your formula.

      Cheers,

      CarloE

      Reply
      • Max

        April 17, 2013 at 1:44 pm

        Hi Carlo

        Thank you ever so much, works a treat.

        Microsoft Office Excel asked to accept a correction which they deleted the = sign in the first bit (A1<=5000 to (A1<5000

        I understand the way you constructed it now, very very clever.

        I have only come across this site today and to get solution back so fast is astonishing and very much appreciated. I have already saved the site in my favorites.

        Once again Carlo, thanks for your time, effort and goodwill.

        Kind Regards,
        Max

        Reply
        • Max

          April 17, 2013 at 1:55 pm

          Hi again Carlo,

          To leave the cell empty if A1 has no value I guess I would put a comma & “” after the *0.0012
          Such as *0.0012,””)))))
          Is this the correct place?

          Thanks again Carlo

          Cheers … Max

          Reply
          • Carlo Estopia

            April 17, 2013 at 2:01 pm

            Hi Max,

            Just enclose the main IF with another IF .. IF(A1=””,””, Main IF)
            like this:

            =IF(A1="","",IF(A1<5000,15,IF(A1<=10000,15+(A1-5000)*0.003,IF(A1<=30000,15+(5000*0.003)+(A1-10000)*0.002,IF(A1<=50000,15+(5000*0.003)+(20000*0.002)+((A1-30000)*0.0016),15+(5000*0.003)+(20000*0.002)+(20000*0.0016)+((A1-50000)*0.0012))))))
            

            Cheers,

            CarloE

        • Carlo Estopia

          April 17, 2013 at 1:59 pm

          Hi Max,

          On behalf of Mynda,
          You’re welcome.

          Cheers,

          CarloE

          Reply
          • Max

            April 17, 2013 at 2:07 pm

            Hi Carlo,

            Ahhhhhhhh, very good.

            Thanks once again and my thanks also goes out to Mynda.

            Just fantastic.

            Cheers … Max

          • Carlo Estopia

            April 17, 2013 at 2:45 pm

            Hi Max,

            Our pleasure!

            CHeers,

            CarloE

  12. Rick

    March 28, 2013 at 2:30 pm

    Great info!

    Reply
    • Carlo Estopia

      March 28, 2013 at 2:34 pm

      Hi Rick,

      Kind words you’ve got right there.
      On behalf of Mynda, Thank You!

      Cheers,

      CarloE

      Reply
  13. FRAN

    February 22, 2013 at 1:03 pm

    TRYING TO FIGURE OUT IF I CAN USE =IF() STATEMENT IN MY WORKBOOK TO ACHIEVE… WHAT IT IS IM TRYING TO ACHIEVE. LOL. ANYWAY.

    SO I HAVE TWO WORKSHEETS IN MY WORKBOOK. ONE TITLED “INC_ASSESSMENT” THE OTHER TITLED, “SHOP_PARTS_TRACKER”.

    ON THE “INC_ASSESSMENT” WORKSHEET I HAVE IN CELLS B2:R2 A PLACE TO INPUT SERIAL NUMBERS.
    ON THE SAME SHEET CELLS B59:B83 HOUSE A SIMPLE =IF FORMULA TO AUTO-FILL INFORMATION. IN THIS CASE ITS PARTS. =IF(B2,2,0) =IF(B2,4,0) AND SO FORTH…
    THIS ALL WORKS GREAT, HOWEVER I NEED MORE FROM THIS EQUATION.

    ON THE SAME SHEET IN CELLS B3:R3 I AM USING THIS FUNCTION…
    =IF(SHOP_PARTS_TRACKER!H13>0,B2&”R”,0). IT IS LINKED TO THE SECOND WORKSHEET AND ALL IT TRULY DOES IS COPY THE NUMBER FROM THE CELL ABOVE AND PLACE A R NEXT TO IT TO SHOW THAT WE HAVE RECEIVED PARTS FOR THE UNIT.

    HERE’S THE NEED I CANT FIGURE OUT HOW TO WORD IN EXCEL SPEAK.

    I NEED THE CELLS B59:B83 TO ALSO CHANGE WITH CELL B3, AND WOULD LIKE THEM TO READ “RECEIVED” IF A NUMBER GREATER THEN 0 IS INPUT IN CELL H13, OR RUN THERE STANDARD FUNCTION OR =IF(B2,2,0)

    CAN ANYONE HELP ME WITH THIS?

    Reply
    • Carlo Estopia

      February 22, 2013 at 7:56 pm

      Hi Fran,

      I hope I got this right.
      Try this formula.

      =IF(H13>0,"RECEIVED",IF(B2,2,0))
      

      More on IF STATEMENTS.

      Cheers.

      CarloE

      Reply
  14. jezryl

    February 22, 2013 at 5:00 am

    Hi Mynda,

    I need to custom the column into 7 digit but the current data i have contains one (1) to seven (7) digit. Can I use this formula to custom the data and translate it into 7 digit?

    Sample
    Column A Column B
    1 0000001
    12 0000012
    123 0000123
    1234 0001234

    I want to translate the column A just like the data in column B.

    Thank you.

    Reply
    • Carlo Estopia

      February 22, 2013 at 11:39 am

      Hi Jezryl,

      Use TEXT function.

      =TEXT(A2,"0000000")
      

      Cheers.

      CarloE

      Reply
  15. Susan

    February 18, 2013 at 3:54 am

    It may be asking too much but I would like to write an IF statement based on time (in one cell) for the following and keep getting an error even when i try to do only the first line. This is what I tried:
    =IF(AND(H4300:03:58)”3″,”N”)
    =0:3:58,”3″
    =0:04:03,”2.5″
    =0:3:40,”2.5″
    >=0:4:14,<=0:4:24,"2"
    =0:3:15,”2″
    >=0:4:25,<=0:4:35,"1.5"
    =0:3:00,”1.5″
    0:4:36,”1″

    Reply
    • Carlo Estopia

      February 18, 2013 at 11:33 am

      Hi Susan,

      You might want to send your file with some explanation of the algorithm/logic of your formula
      via HELP DESK.

      In the meantime, try TIME FUNCTION to make your criteria work.

      Cheers.

      CarloE

      Reply
  16. tom

    January 29, 2013 at 10:18 pm

    Hi

    I would *really* appreciate your guidance on my attempted nested equation. Here’s how I’ve it- but I think there must be something wrong as the ‘Or’ bit doesn’t seem to work (B14 is meant to increase by a value of 1).

    =IF(AND(A13=A14,B13+B14>=1)*OR(B14>=1),A14+1,A14)

    thanks – very helpful site

    Reply
    • tom

      January 29, 2013 at 10:21 pm

      nb. sorry, reading that back, that was unclear. B14 *is* equal to 1, so the cell the above equation is in is meant to +1… but it doesn’t seem to change its value.

      Reply
    • Carlo Estopia

      January 29, 2013 at 11:38 pm

      Hi Tom,

      I couldn’t quite get what you mean here but I tried to understand this anyway:

      your formula should look like this:

      =IF(OR(AND(A13=A14,B13+B14>=1),(B14>=1)),A14+1,A14)

      here’s the pseudo-formula
      IF (A13=A14 AND B13+B14>=1);
      OR B14>=1 then
      A14 + 1
      ELSE(false value of if)
      A14

      Please you may also send your file through Help Desk for clarification.

      See also NESTED IF

      Sincerely,

      CarloE

      Reply
  17. James

    January 18, 2013 at 5:31 am

    I need a nested IF statement to display the season corresponding to a date. For example, if I enter a date (dd/mmm/yy) in a cell, I want another cell to show “Spring”, “Summer”, “Autumn” or “Winter”. I can’t get Excel (ver 2007) to understand what is meant by: =IF(A1=”01 Mar”, “Spring”,”X”)), etc. etc. Can you help please? (Your instructions are very clear & a great help – well done)

    Reply
    • Carlo Estopia

      January 19, 2013 at 12:12 am

      Hi James,

      Here’s the nested formula that I have created:

      =IF(AND(MONTH(A1)>=3,MONTH(A1)<=5), "SPRING", IF(AND(MONTH(A1)>=,MONTH(A1)<=,"SUMMER, IF(AND(MONTH(A1)>=9,MONTH(A1)<=11),"FALL","WINTER"))) So in your case, If you have your date in A1 you can validate it with the formula above To Follow this one here's the pseudo-formula I have diagrammed: 1st IF(Criteria,"TrueValue", 2nd IF(Criteria,"TrueValue", 3rd IF(Criteria,"TrueValue", CatchAllValue) Please note that each IF function is cut-off in its false value until the catch-all value is arrived at. Hence, if we look at the basic syntax IF(Criteria,"True Value", "False Value"), the False Value is replaced with a new IF Function until the Catch-All Value or the False Value for the Main IF Function --which is actually the first-- is arrived at. Click Here for more on IF Functions

      Sincerely,

      CarloE

      Reply
  18. jackie

    January 16, 2013 at 7:34 pm

    =IF(E26=1,($D$5*$B$5),IF(E26=T,($D$5*$C5),0)) Whats wrong with my formula, it brings a “NAME” error

    Reply
    • MikeBanawa

      January 16, 2013 at 10:04 pm

      Hello Jackie,

      Inside your Outcome 2 = IF(E26=T the letter T should have quotations.
      You’ll need the “” sign for Excel to recognize that you are commanding IF Formula to look for TEXT.
      So it should be: IF(E26=”T”.

      Other than that, I think your formula is perfect.
      Just to make sure, have you checked the other cells if you referenced TEXT?
      Hope this helps. 🙂

      Thanks!
      Mike

      Reply
  19. Ajit Chauhan

    December 19, 2012 at 8:43 pm

    Thanks for help me The online training Hub

    Reply
    • Mynda Treacy

      December 19, 2012 at 8:48 pm

      You’re welcome, Ajit 🙂

      Reply
      • Hamid

        April 18, 2013 at 9:08 pm

        Thanks, they were usefule.

        Reply
        • Mynda Treacy

          April 19, 2013 at 8:43 am

          Thank you, Hamid 🙂

          Reply
  20. Michael Keenan

    October 27, 2012 at 6:07 am

    Need tips on nested if statemetns

    Reply
    • Mynda Treacy

      October 28, 2012 at 5:00 pm

      Hi Michael,

      Have you got a specific question on a nested IF statement that I can help you with?

      Kind regards,

      Mynda.

      Reply
  21. Karen

    October 4, 2012 at 11:19 pm

    Hi Mynda, I’m going cross-eyed trying to work out how to do (what seems to me to be) a complex IF formula. I have a table with a range of numbers of games played and a payment range according to the number of games played. Is there a way of saying IF the number in cell A8 falls between (say) 16 and 40 then payment is $40, but if the number in cell A8 falls between (say) 41 and 100 then payment is $100, etc etc – there are five different ranges and the final one is if the number in cell A8 is greater than 150. I’ve tried using >= and <= in the one logical test, and in the final logical test false is not an option as after all that testing the number must be greater than 150 (if you get my drift).

    Reply
    • Mynda Treacy

      October 5, 2012 at 8:19 am

      Hi Karen,

      You’d be better off using a VLOOKUP formula with a sorted list like this.

      Kind regards,

      Mynda.

      Reply
  22. Beena

    August 16, 2012 at 4:27 am

    Really help when you don’t remember how to finish whole formula.

    Reply
    • Mynda Treacy

      August 16, 2012 at 12:53 pm

      Glad to have helped 🙂

      Reply
  23. Jon Roberts

    November 26, 2011 at 2:39 am

    Looking forward to some great tips.

    Reply
  24. Al

    August 27, 2011 at 12:44 am

    Very helpful

    Reply
  25. Excel Statements

    April 30, 2011 at 6:15 am

    Are you able to have two side by side IF’s (not nested) and have them link to one another without bringing up an error?

    I mean side by side in the same formula, not in different cells.

    Reply
    • Mynda

      April 30, 2011 at 2:35 pm

      Um, no! However, I’m sure there’ll be a solution to whatever it is you’re trying to do. Can you give me an example of your conundrum?

      Reply
  26. Andrew

    November 14, 2010 at 8:12 pm

    cool, thanks

    AC

    Reply
  27. zero

    October 20, 2010 at 7:32 pm

    Thanks for the info

    Reply

Trackbacks

  1. Excel 2007 – IF AND OR Functions Explained says:
    October 2, 2010 at 9:38 pm

    […] For more on Nested IF statements go here. […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.