Use IF command in >7 nested function??

Hi.

Column G is a value from 500-75000 and i need to slot the value into 15
ranges like 500-999, 1000-1499 etc

How do i tackle this, as IF accepts nested command for only upto 7 scenarios??

pls help, thanks
KDDXB

On Sun, 28 Aug 2005 07:54:02 -0700, KDD <KDD@discussions.microsoft.com> wrote:

>Hi.
>
>Column G is a value from 500-75000 and i need to slot the value into 15
>ranges like 500-999, 1000-1499 etc
>
>How do i tackle this, as IF accepts nested command for only upto 7 scenarios??
>
>pls help, thanks
>KDDXB


Use VLOOKUP (see HELP for details):

=VLOOKUP(G1,{500,"500-999";1000,"1000-1499";1500,"1500-4999";5000,"5000-75000"},2)

The array is most easily listed in a table, with column 1 being your "break
points" and column 2 being how you want to identify the bins (ranges).


--ron

Thanks Ron.

Need some more clarity..

VLOOKUP does not seem to recognize "500-599" as a number and therefore is
not co-relating "500-599" with median as 750.
--
KDDXB


"Ron Rosenfeld" wrote:

> On Sun, 28 Aug 2005 07:54:02 -0700, KDD <KDD@discussions.microsoft.com> wrote:
>
> >Hi.
> >
> >Column G is a value from 500-75000 and i need to slot the value into 15
> >ranges like 500-999, 1000-1499 etc
> >
> >How do i tackle this, as IF accepts nested command for only upto 7 scenarios??
> >
> >pls help, thanks
> >KDDXB
>
>
> Use VLOOKUP (see HELP for details):
>
> =VLOOKUP(G1,{500,"500-999";1000,"1000-1499";1500,"1500-4999";5000,"5000-75000"},2)
>
> The array is most easily listed in a table, with column 1 being your "break
> points" and column 2 being how you want to identify the bins (ranges).
>
>
> --ron
>

On Sun, 28 Aug 2005 08:35:02 -0700, KDD <KDD@discussions.microsoft.com> wrote:

>Thanks Ron.
>
>Need some more clarity..
>
>VLOOKUP does not seem to recognize "500-599" as a number and therefore is
>not co-relating "500-599" with median as 750

Probably the lack of clarity is due to my misunderstanding your specifications.

What I thought you wanted to do was given some number, return the bin into
which it was slotted. If the number was, for example, 700, that would go into
the bin labeled "500-999".

I don't understand why you require the label "500-999" to be a number.

The formula I gave you, given 700 in G1, would return the string "500-999".

What is it that you want to return, given an input of 700?


--ron