Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating ranges in closed workbooks without #REF errors | Excel Discussion (Misc queries) | |||
Creating sheet specific names ranges | Excel Discussion (Misc queries) | |||
Creating a list (data validation) fromt wo different source ranges | Excel Discussion (Misc queries) | |||
creating a pivot table w/ ranges from 2 worksheets | Excel Discussion (Misc queries) | |||
Creating drop down list for multiple cell ranges | Excel Discussion (Misc queries) |