+ Reply to Thread
Results 1 to 5 of 5

How do I return a value based on multiple possible conditions?

  1. #1
    nevermore627
    Guest

    How do I return a value based on multiple possible conditions?

    I am trying to assign a specific value to a cell based on multiple
    choices.

    For example, cell A1 needs to be "A", "B", "C" or "D" (pay grades)
    This value is based on what is contained in cell B1.

    If the value in B1 is between 12.00 and 20.00, A1 needs to = "A"
    If the value in B1 is between 20.01 and 28.00, A1 needs to = "B"
    If the value in B1 is between 28.01 and 39.00, A1 needs to = "C"
    If the value in B1 is between 39.01 and 52.00, A1 needs to = "D"

    I've tried a number of different formulas, but can't make it work with
    =IF alone, since it only returns one of two values based on a single
    condition.

    How do I make the formula return one of four values based on four
    different conditions?

    I have to have this on another desk by tomorrow morning! Any help
    anybody can give would be much appreciated, and then some! Thanks in
    advance,

    Mike Simard
    [email protected]


  2. #2
    Elkar
    Guest

    RE: How do I return a value based on multiple possible conditions?

    Try this:

    =IF(B1>=12,"A",IF(B1>20,"B",IF(B1>28,"C",IF(AND(B1>39,B1<=52),"D",""))))

    Note that this formula returns a blank if B1 is less than 12 or greater than
    52.

    HTH,
    Elkar


    "nevermore627" wrote:

    > I am trying to assign a specific value to a cell based on multiple
    > choices.
    >
    > For example, cell A1 needs to be "A", "B", "C" or "D" (pay grades)
    > This value is based on what is contained in cell B1.
    >
    > If the value in B1 is between 12.00 and 20.00, A1 needs to = "A"
    > If the value in B1 is between 20.01 and 28.00, A1 needs to = "B"
    > If the value in B1 is between 28.01 and 39.00, A1 needs to = "C"
    > If the value in B1 is between 39.01 and 52.00, A1 needs to = "D"
    >
    > I've tried a number of different formulas, but can't make it work with
    > =IF alone, since it only returns one of two values based on a single
    > condition.
    >
    > How do I make the formula return one of four values based on four
    > different conditions?
    >
    > I have to have this on another desk by tomorrow morning! Any help
    > anybody can give would be much appreciated, and then some! Thanks in
    > advance,
    >
    > Mike Simard
    > [email protected]
    >
    >


  3. #3
    nevermore627
    Guest

    Re: How do I return a value based on multiple possible conditions?

    Thanks Elkar - at least it's not returning an error - but regardless of
    the value in B1, the formula always returns "A" to A1 - as if it stops
    as soon as it returns the first "true" value.


  4. #4
    Elkar
    Guest

    RE: How do I return a value based on multiple possible conditions?

    Oops, wasn't thinking clearly when I wrote this. I got it backwards. Try
    this instead:

    =IF(B1>52,"",IF(B1>39,"D",IF(B1>28,"C",IF(B1>20,"B",IF(B1>=12,"A","")))))

    Ugh, must be qutting time...

    HTH,
    Elkar


    "Elkar" wrote:

    > Try this:
    >
    > =IF(B1>=12,"A",IF(B1>20,"B",IF(B1>28,"C",IF(AND(B1>39,B1<=52),"D",""))))
    >
    > Note that this formula returns a blank if B1 is less than 12 or greater than
    > 52.
    >
    > HTH,
    > Elkar
    >
    >
    > "nevermore627" wrote:
    >
    > > I am trying to assign a specific value to a cell based on multiple
    > > choices.
    > >
    > > For example, cell A1 needs to be "A", "B", "C" or "D" (pay grades)
    > > This value is based on what is contained in cell B1.
    > >
    > > If the value in B1 is between 12.00 and 20.00, A1 needs to = "A"
    > > If the value in B1 is between 20.01 and 28.00, A1 needs to = "B"
    > > If the value in B1 is between 28.01 and 39.00, A1 needs to = "C"
    > > If the value in B1 is between 39.01 and 52.00, A1 needs to = "D"
    > >
    > > I've tried a number of different formulas, but can't make it work with
    > > =IF alone, since it only returns one of two values based on a single
    > > condition.
    > >
    > > How do I make the formula return one of four values based on four
    > > different conditions?
    > >
    > > I have to have this on another desk by tomorrow morning! Any help
    > > anybody can give would be much appreciated, and then some! Thanks in
    > > advance,
    > >
    > > Mike Simard
    > > [email protected]
    > >
    > >


  5. #5
    nevermore627
    Guest

    Re: How do I return a value based on multiple possible conditions?

    Elkar - I tried your formula in reverse, starting with the higher
    numbers so that the condition would continue to be false until it hit
    the right range, and BINGO! It worked like a charm! My gratitude is
    virtually infinite - thanks a million!


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1