sorting ranges with open ends

[Guest]

I have a number of values ranging from positive to negative that I need to
sort into 5 categories depending on different ranges (i.e the number 3 and 5
are "1-10" and so on)
ex. of values:
1, 15, 0, -15, -32, -100

the positive are openended (i.e they range from 1 and up)
0 is included in the positive numbers.
the negative range from -1 to <-91. (i.e it ranges from -91 and down)

I need these to be sorted into categories and labeled in a separate column
like this:
range: label

0 ; "0"

-1<>-30 ; "'1-30"
-31<>-60 ; "'31-60"
-61<>-90 ; "'61-90"
<-91 "'>91"


I have tried to do this with VLOOKUP but I am unsure of how to do this with
open ends (i.e <-91 and >0)
there might be some way to do this with the IF function (or a combination of
other logical functions)

any ideas?

 

[Guest]

You may have to fiddle with this a bit, but any of these techniques should
give you what you want:

=IF(A2="","",SUM($A$2:A2)-A2+1&IF(A2=1,""," - "&SUM($A$2:A2)))

=IF(OR(A2="",A2=0),"",SUM($A$2:A2)-A2+1&IF(A2=1,""," - "&SUM($A$2:A2)))

=Frequency()
From Help: Calculates how often values occur within a range of values, and
then returns a vertical array of numbers.


Regards,
Ryan---

 

[vezerid]

Indeed you can do with VLOOKUP. Set up your table as follows:

-9.99E307 >90
-90 61-90
-60 31-60
-30 1-30
0 0

If this table is in A1:B5 you can use:

=VLOOKUP(val,A1:B5,2,TRUE)

HTH
Kostis Vezerides