Use IF command in >7 nested function??

[Guest]

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

 

[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

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

 

[Paul Sheppard]

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

 

[Guest]

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.

 

[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

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