Nested IF Statement with Greater Than and Less Than

Posted on 2011-10-25
Last Modified: 2012-05-12

I need to do a nested if statement within Excel 2007. I am working with number values and need to basically say IF value is greater than X do this but IF value is less than X and greater than Y Do That and IF value is less than Y but greater than Z do something else.

Please advise.


Question by:jpldpd
    LVL 24

    Accepted Solution

    Something along these lines?

    =if(a1>x,"this",if(and(a1<x,a1>y),"that",if(and(a1<y,a1>z),"the other","what?")))

    but probably need specifics to be 100%.
    LVL 27

    Expert Comment

    by:Glenn Ray
    Assuming value to test is in cell A1:
    =IF(A1>X,"this",IF(A1>Y,"That",IF(A1>Z,"something else","do nothing")))

    However, you're ignoring the possibility that your test value (A1, in this example) may be equal to X, Y, or Z.
    In those cases, the return result may not be correct.

    LVL 50

    Expert Comment

    by:barry houdini
    Hello John,

    If the "ranges" are contiguous then there's usually no need for and, e.g.

    IF you want to return "x" for values less than 3, "y" for value greater than or equal to 3 but less than 7, "z" for values >= 7 but < 20 and "a" for >=20 then this nested IF would worl


    ...or especially for larger numbers of ranges a LOOKUP formula is better. List the lower bound of each range in Y2:Y10 (in ascending order) and the corresponding values to be returned in Z2:Z10 and use


    regards, barry

    Author Closing Comment


    Thank you! The only change I made was, rather than just > and <, I used the >= and <=.

    LVL 24

    Expert Comment

    Glad it worked, didn't expect that to do fully do the job.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now