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 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

KDD 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

Try creating a lookup table and returning the range value from the
lookup table


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=399860

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 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 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