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

tonywhite

New Member
Joined
Sep 9, 2002
Messages
4
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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
 

Forum statistics

Threads
1,144,329
Messages
5,723,737
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top