+ Reply to Thread
Results 1 to 5 of 5

How do create a formula to evalute a # to return 1 of 4 conditions

  1. #1
    Larry
    Guest

    How do create a formula to evalute a # to return 1 of 4 conditions

    I have to evaluate a single number in a single cell and return a value based
    on that number. How do I build a formula to do the following?
    If the number is < 500, I need to return "Non-Target"
    If the number is >= 500 but < 1500, I need to return "Low"
    If the number is >= 1500 but < 3500, I need to return "Medium"
    If the number is >= 3500, I need to return a value of "High"

    How do I build this formula

  2. #2
    Peo Sjoblom
    Guest

    Re: How do create a formula to evalute a # to return 1 of 4 conditions

    One way, assume the number in question is in cell A1


    =IF(A1="","",VLOOKUP(A1,{0,"Non
    Target";500,"Low";1500,"Medium";3500,"High"},2))

    I assumed that the number can't be less than zero

    --
    Regards,

    Peo Sjoblom


    "Larry" <[email protected]> wrote in message
    news:[email protected]...
    >I have to evaluate a single number in a single cell and return a value
    >based
    > on that number. How do I build a formula to do the following?
    > If the number is < 500, I need to return "Non-Target"
    > If the number is >= 500 but < 1500, I need to return "Low"
    > If the number is >= 1500 but < 3500, I need to return "Medium"
    > If the number is >= 3500, I need to return a value of "High"
    >
    > How do I build this formula



  3. #3
    Larry
    Guest

    Re: How do create a formula to evalute a # to return 1 of 4 condit

    Peo, you are Fantastic!!!.

    I understand some of what you did, but can you explain, in simple terms,
    what is occurring in this formula?

    Thanks
    LB

    "Peo Sjoblom" wrote:

    > One way, assume the number in question is in cell A1
    >
    >
    > =IF(A1="","",VLOOKUP(A1,{0,"Non
    > Target";500,"Low";1500,"Medium";3500,"High"},2))
    >
    > I assumed that the number can't be less than zero
    >
    > --
    > Regards,
    >
    > Peo Sjoblom
    >
    >
    > "Larry" <[email protected]> wrote in message
    > news:[email protected]...
    > >I have to evaluate a single number in a single cell and return a value
    > >based
    > > on that number. How do I build a formula to do the following?
    > > If the number is < 500, I need to return "Non-Target"
    > > If the number is >= 500 but < 1500, I need to return "Low"
    > > If the number is >= 1500 but < 3500, I need to return "Medium"
    > > If the number is >= 3500, I need to return a value of "High"
    > >
    > > How do I build this formula

    >
    >


  4. #4
    paul
    Guest

    RE: How do create a formula to evalute a # to return 1 of 4 conditions

    =IF(A1=0,0,IF(A1<500,"non
    target",IF(A1<1500,"low",IF(A1<3500,"medium","high"))))
    because your sequence is "logical"you can use a simple "if",because it moves
    to the next argument as soon as the previous one is not true. You may or may
    not need the initial if( a1 is o argument,it will display high if cell a1 is
    empty or 0 without it

    --
    paul
    remove nospam for email addy!



    "Larry" wrote:

    > I have to evaluate a single number in a single cell and return a value based
    > on that number. How do I build a formula to do the following?
    > If the number is < 500, I need to return "Non-Target"
    > If the number is >= 500 but < 1500, I need to return "Low"
    > If the number is >= 1500 but < 3500, I need to return "Medium"
    > If the number is >= 3500, I need to return a value of "High"
    >
    > How do I build this formula


  5. #5
    paul
    Guest

    Re: How do create a formula to evalute a # to return 1 of 4 condit

    vlookup is a function that you use to look at a column of information and
    return a value from the same row a specified number of columns across to the
    right
    normally you would have a little table
    0 non target
    500 low
    1500 medium
    3500 high
    but because its so simple peo used an array constant to represent the table.
    so his formula says if a1 is blank return blank otherwise look in the first
    column for a value and return the value next to it (in the second column)


    --
    paul
    remove nospam for email addy!



    "Larry" wrote:

    > Peo, you are Fantastic!!!.
    >
    > I understand some of what you did, but can you explain, in simple terms,
    > what is occurring in this formula?
    >
    > Thanks
    > LB
    >
    > "Peo Sjoblom" wrote:
    >
    > > One way, assume the number in question is in cell A1
    > >
    > >
    > > =IF(A1="","",VLOOKUP(A1,{0,"Non
    > > Target";500,"Low";1500,"Medium";3500,"High"},2))
    > >
    > > I assumed that the number can't be less than zero
    > >
    > > --
    > > Regards,
    > >
    > > Peo Sjoblom
    > >
    > >
    > > "Larry" <[email protected]> wrote in message
    > > news:[email protected]...
    > > >I have to evaluate a single number in a single cell and return a value
    > > >based
    > > > on that number. How do I build a formula to do the following?
    > > > If the number is < 500, I need to return "Non-Target"
    > > > If the number is >= 500 but < 1500, I need to return "Low"
    > > > If the number is >= 1500 but < 3500, I need to return "Medium"
    > > > If the number is >= 3500, I need to return a value of "High"
    > > >
    > > > How do I build this formula

    > >
    > >


+ 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