Creating Ranges Easily

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I was wondering if you guys can come up with a easier and quicker solution
to work with ranges in Excel.
For example:

I need to classify prices ranges.
PRICE
$2.50
$1.72
$12.00
$4.50

RANGES
<$1.00
$1.00 AND <$5.00
$5.00 AND <$10.00
$10.00
Click to expand...

I can do this, using IFs but if you dramatically changes the ranges and
include many, it's very time consuming.

How can I do this on a more automated fashion?

Btw, Excel could provide some wizard tool to create those ranges as it has
for subtotals, pivot tables, etc.

Thanks
 
Try something like this:

Enter this list in A1:B4
0____<1
1____1 to 4.99
5____5 to 9.99
10__10 and over

Then
C1: (a price)

This formula returns the range category for that price:
D1: =VLOOKUP(C1,A1:B4,2,1)

That formula translates into:
Find the price (C1)
in the left column of the lookup range A1:B4
Then return the corresponding item from the 2nd column

The last function parameter (1) indicates:
....If no exact match use the largest value that is smaller than the price

See VLOOKUP in Excel Help.
Post back if you have more questions.

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Not quite sure what you want but you could use a VLOOKUP table, a choose
function or simply IF.
=if(a1>10,1,if(a1>5,2,if(a1>1,3,4)))
 
Back
Top