Naming ranges of cells based on type

[kcin]

Hello,

I am trying to create named lists of ranges of cells and I really have no idea how. Basically my worksheet is setup like this:

Product Price

Apple $0.50
Banana $0.25
BMW $30,000
Chevrolet $10,000
Orange $1.00
Volkswagon $20,000

Now I will be graphing this data as a bar chart and sort it in ascending or descending order. What I would like to do is "Categorize" each product into its own "group" and then be easily able to calculate the average price for each "group". Is there a way to select the cells "Apple" and "$0.50" and categorize them as "Fruit"? Then once I categorize all the "Fruit" I can use a simple formula such as =Average(Fruit) and it will give me the average price just for the fruit items?

Any help with this would be greatly appreciated, thanks.

 

[Anthony47]

Try using a new colomn (say C) to define, item by item, the Category; then you can either set an Autofilter on column C and use =SUBTOTAL(1,C2:C1000) to display the average price of the filtered items; or use a pivot table with Category in the Row area and "Average of price" in the Data area, and you will get a table with all the average prices; you will not see "Average of price" while setting the layout of the pivot table but, probably, "Count of.." or "Sum of..": just double click what you see and choose "Average" in the list that will be shown.

If you need assistance about creating the pivot table, just ask.

Bye.

 

[Peter_SSs]

Yet another option, after adding the extra column, is to use an array formula (confirmed with Ctrl+Shift+Enter, not just Enter) as follows.

Excel Workbook
	A	B	C	D	E	F
1	Product	Price	Category		Category	Average
2	Apple	0.5	Fruit		Car	20,000.00
3	Banana	0.25	Fruit		Fruit	0.58
4	BMW	30,000	Car
5	Chevrolet	10,000	Car
6	Orange	1	Fruit
7	Volkswagon	20,000	Car
Average by Category