Grouping values in excel, IF AND statement

[coffee<3]

Hi!

First of all: I'm very new to excel so please be patient and just ask if you don't understand the question.

The problem.
I have a large excel table (around 10 000 rows) and I would like to create a graph that shows how many of certain products are in a certain price range. So for example, I would like to be able to see how many products cost between $10000 and $20000.

The columns are: Product, Price, Date.

So what I was thinking of doing is creating a separate column with an IF/AND statment that checks if a price is within a range and then returns the range.

For example: =IF(All!I2<=9999;"0-9999";IF(AND(All!I2>=10000;ALL!I2<=19999);"10000-19999")

However, I have a lot of ranges so it would be a very long nested IF AND statement, is there another way of doing this? I've tried a pivot table and I know i can group values in it, but as I update my table, the pivot table ads new items outside the groups so I need to regroup them which takes a long time.

 

[texasalynn]

could you setup a table to use a vlookup? What are the conditions for all the ifs?

 

[coffee<3]

Hi! What would be different by using vlookup? Wouldn't i still need to set up all the conditions in the same way?

The conditions for the ifs are as above, one condition for each "range" or "category", 100-200 and 201-300 and so on, the max price category is 500 000, so i would need a lot of nested if statements to cover all the different ranges.

 

[texasalynn]

??? no understanding. Your if statement only has two that I'm seeing. This is what I was seeing

Excel 2010
	A	B	C	D	E
19			Tables
20			0	9999	0-9999
21			10000	199999	10000-19999
22
23	5000	0-9999
24	15000	10000-19999
25	2500	0-9999
Sheet1

Cell Formulas
Range	Formula
B23	=VLOOKUP(A23,$C$20:$E$21,3)

 

[coffee<3]

Sorry for beeing so unclear.
The if statement only has two right now, but if i want to create more i would need to add more, so I was wondering if there was an easier way to do this, instead of having to create an if statement with 50 different statements in it.

For example:


Product | Price | Date (these are the columns)

Rice | 200 | 2013-04-22
Meat | 100 | 2012-04-21
Peas | 140 | 2012-03-22
Chicken | 300 | 2003-04-22


So what i want to do is to see how many products cost between 100-110, 111-120 and so on. The solution I've come up with is to check if the value in the price column is between the values i want to categorize them in. This means that if i want to categorize them by 10s i need a lot of if statements for it.

Example: IF price<=100 return "0-100", if price>=101 AND price<150 return "101-150".

As you can see from the example above, as soon as i want to add another price category, lets say "300-400" i would need to add a new if and statement, and since the prices range from 10000 to 1 000 000 it will get quite boring to write so many if statements. Therefore I was wondering if there was a way where I could just select a column and simply "group by" and then select the range i would like, for example, every 10 000 or something.

By the way, how did you make the excel table in the reply?

 

[coffee<3]

I've created an example table to try to explain.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl65, width: 146"]Product[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Organic?[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="width: 146"]Olive oil extra virgin[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]19
[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="width: 146"]Olive oil standard[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="width: 146"]Olive oil greek[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]15[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="width: 146"]Flaxeed oil standard[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="width: 146"]Palm oil sour[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]16[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="width: 146"]Flaxeed oil Gold[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="width: 146"]Flaxeed oil Platinum[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="width: 146"]Palm oil organic[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]14[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="width: 146"]Palm oil sour[/TD]
[/TR]
[TR]
[TD="width: 146"][/TD]
[/TR]
[TR]
[TD="width: 146"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


So what i'm trying to do is to see "how many of my product costs between $1 to $10?" Sure with a table this small it's easy, but i've a lot of different products so I would like to create a separate column where i can group them into larger groups and then easily create a graph that counts the group column. That way i could easily see that I have 400 products that cost between $1 to $10. Then I would also like to group products into categories, for example it would be nice to see how many of my products are olive oil based?

The solution i have is: =IF(B2<=10;"1 to 10";IF(AND(B2>=11;B2<=15);"11 to 15";IF(AND(B2>=16;B2<=20);"16-20")IF(B2>20;">20")))

The problem with this solution is that my price range is quite big, so I will need a lot of nested if statements to cover it all. Therefore i'm looking for an easier way to do this, grouping by value. It would also be nice to see a way to group the products by name, something "if contains olive return "olive oil based".

Hope this description is better than my previous ones.

 

[texasalynn]

would still work with a vlookup

Excel 2010
	A	B	C	D
1	Product	Price	Organic?	Range
2	Olive oil extra virgin	19	Yes	16 - 20
3	Olive oil standard	12	No	11 - 15
4	Olive oil greek	15	No	11 - 15
5	Flaxeed oil standard	12	Yes	11 - 15
6	Palm oil sour	16	Yes	16 - 20
7	Flaxeed oil Gold	10	No	0 - 10
8	Flaxeed oil Platinum	9	Yes	0 - 10
9	Palm oil organic	14	Yes	11 - 15
10	Palm oil sour	11	10	11 - 15
Sheet1

Cell Formulas
Range	Formula
D2	=VLOOKUP(B2,$K$2:$L$14,2)

Excel 2010
	K	L
1	Table
2	0	0 - 10
3	11	11 - 15
4	16	16 - 20
5	21	21 - 25
6	26	26 - 30
7	31	31 - 35
8	36	36 - 40
9	41	41 - 45
10	46	46 - 50
11	51	51 - 55
12	56	56 - 60
13	61	61 - 65
14	66	Over 66
Sheet1

Cell Formulas
Range	Formula
L3	=K3 &" - "&K4-1
K5	=+K4+5

 

[coffee<3]

Thank you soo much! This is exactly what I was looking for! Sorry for being a bit unclear at first.

 

[texasalynn]

Your welcome. It was the same solution I offered before just didn't make it clear I'm guessing. Thanks for the feedback