Use IF command in >7 nested function??

K

KDD

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
 
R

Ron Rosenfeld

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
Click to expand...


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
 
P

Paul Sheppard

KDD said:
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
Click to expand...

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

KDD

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

Ron Rosenfeld

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
Click to expand...

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
 
Top