Creating Ranges Easily
 

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

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

Creating Ranges Easily
 

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)
"Guilherme Loretti" wrote in
message ...
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

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

Creating Ranges Easily
 

Not quite sure what you want but you could use a VLOOKUP table, a choose
function or simply IF.
=if(a110,1,if(a15,2,if(a11,3,4)))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Guilherme Loretti" wrote in
message ...
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

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