specifying a range of numbers (i know this should be easy)

[tonywhite]

I need a little "beginner-level" assistance. I'm ashamed that I can't find such things in the excel help files for this, but I thought I'd ask the forums -- a wonderful resource for help, btw.

What I want to do: list a value in an excel cell that is specified as a range. Such as: values 1 through 10.

What I'm using it for:
Currently I'm using a vlookup table (another suggestion from this forum that has worked great!) to return a value. Here's kind of the breakdown for what I'd like to accomplish:
If A1 is 0 - 1.5, return the value "red".
If A1 is 1.6 - 5.0, return the value "blue".
If A1 is 5.1 - 15.0, return the value "green".
and on and on.

Currently, I am having to use "ceiling" to take a decimal place, round it up to nearest ".5", then going to the lookup table to return the right value. The formula I'm using is =VLOOKUP((CEILING(D7,.5)),$A$1:$B$20,2,True)

and my current "vlookup" table looks something like this:
A B
0.5 red
1.0 red
1.5 red
2.0 blue
2.5 blue
3.0 blue
etc.

I would much rather have it be
A B
0-1.5 red
1.6-5.0 blue
5.1-15.0 green
etc.

only, I don't know how to specify the "range" part.

Anybody know how I can tell excel "zero through 1.5" ?

Thanks!
Tony

 

[Corticus]

If you set up your lookup table like this:

A B
0 red
1.5 red
1.6 green
6 green
6.1 blue
10 blue
10.1 orange
100 orange

its not exactly what you asked for, but if you used a formula like this:

=VLOOKUP(ROUNDUP(D1,1),$A$1:$B$8,2,TRUE)

assuming data your f(x)ing is populating D1:D8, f(x) would be in column E.

(I'm on a friends computer, and oh I miss Colo's wonderful tool!)

You have effectively said if D1 is between 0 and 1.5 put red, if its greater than 1.5 but less than or equal to 6 put green, etc.

Maybe this helps?

Corticus
This message was edited by Corticus on 2002-09-18 17:42