Select Categories and statistics from a table of Categories and Products (Excel 2000)

[L Mehl]

One sheet in a workbook contains these columns:
Product Category, Product Name, Product Bitrate

I want to use the table in 2 ways:

1 - present a list of Categories only, from which the user selects 1 or more
to be processed

2 - in the processing, use the Average of the Bitrate of products in the
selected category/categories.

If this was a SQL database, the solutions would be:
1 - SELECT Category FROM ... GROUP BY Category as the source for a listbox
2 - SELECT Category, AVG(Bitrate) FROM ... GROUP BY Category

Is a Pivot table part of the solution? If so, how do I extract data from a
pivot table?

Can someone get me started doing this in Excel?

Thanks for any help.

Larry Mehl
mehl_at_cyvest.com

 

[Dave Peterson]

A pivottable seems reasonable to me.

And you could always use an =vlookup() to return the second column of the
pivottable (first column would hold the category).

You could also use a few formulas:

=SUMPRODUCT(--(sheet2!A2:A99="cate1"),sheet2!C2:C99)
/COUNTIF(sheet2!A2:A99,"cate1")
(all one cell)

=====

I'm not sure how you're going to present the list, though.

Maybe a few cells that have Data|validation applied. Then you could even use an
adjacent cell that has something like:

=if(a2="","",thatsumproductformulafromabove)

 

[L Mehl]

Dave --

Thanks. That helps me get started.

Larry