Auto filtering?

**jhelliar** · 01-25-2008, 10:41 AM

I wonder if anyone can help I have a load of data about 200 people who have been on a diet. In colum A is there name in Comun B is the n of weeks they have been on the diet and column C is their starting weight. On a separate sheet I have week in column A and I want to have average start weight for that week in column B. Is there any formula I can put in to get it to search for the week (e.g. 1) in Column B of sheet 1 and then Average the starting masses for those people and put it into column B of Sheet 2?

Any help welcome.

Many thanks

Jack

**nattasiray** · 01-25-2008, 11:47 AM

To get the result quickly. Please attach the sample data file and the sample of result that you want.

Thank you for your cooperative.

**jhelliar** · 01-26-2008, 07:34 AM

I have attached a sample file with a few bits of Data I have done the average in column C of sheet 2 to show what I would like calculated. Any help would be greatly appreciated

Many Thanks

Jack

**VBA Noob** · 01-26-2008, 08:11 AM

Try

=SUMPRODUCT(--(Sheet1!$B$2:$B$19=ROW(A1))*(Sheet1!$C$2:$C$19))/COUNTIF(Sheet1!B:B,ROW(A1))

Drag down as required

VBA Noob

**royUK** · 01-26-2008, 09:06 AM

A PivotTable would be the most efficient way. I have created an example for you

**jhelliar** · 01-26-2008, 09:27 AM

Many thanks to both of you for your help. When I click on the sample file for the pivot table it says page not found. Is that a problem with my end?

I also would like to do somethingf similar with another sheet I have. Every month our children are given effort grades and I work out the average for each student. So in column A is the student Column B is the house they are attached to column C is the year they are in and column D is there average. One of the things people as me for is the best average for each year and each house is there a simple wa of doing this withouht having to sort them manually? I have attached a sample sheet.

Sorry for all the questions it is amazing how much you can do with excel with some practice.

Many thanks

Jack

**VBA Noob** · 01-26-2008, 09:58 AM

See link

http://cdn.libsyn.com/mrexcel/podcast278.mp4

VBA Noob

**royUK** · 01-26-2008, 10:37 AM

I have attached the example now.

**jhelliar** · 01-26-2008, 10:41 AM

Many thanks for that I have tried opening that site but my quicktime does not seem to work. Is there anywhere else I can look?

Jack

**no dice** · 01-26-2008, 12:13 PM

I'm very much in the pivot table camp on this one.

**jhelliar** · 01-27-2008, 08:36 AM

Many thanks for everyone's help. Is there a site which explains how to use pivot tables properly? I have played around with them but can never get them to quite do what I want.

Again Many thanks

Jack

**VBA Noob** · 01-27-2008, 08:41 AM

See link under pivots

http://datapigtechnologies.com/ExcelMain.htm

To get average change Pivot table field

http://www.contextures.com/xlPivot05.html#Manual

VBA Noob

**jhelliar** · 01-27-2008, 08:45 AM

Many thanks. Is it therefore possible to use a pivot table to choose the best (Ie Lowest figure) from a range of data and then put the information attached to that data( e.g. the rest of the row) next to it? This is what I need to do for the grades example.

Thanks again

Jack

**jhelliar** · 01-27-2008, 03:36 PM

I have managed to produce a pivot table with the year and then the smallest average grade for that year. Is there any way of putting in the next cell the name of the person that grade corresponds to? I had a go at using lookup but could not get it to work and fear if two people have the same average it may cause problems. Any help would be more than appreciated.

I have attached an example

Many thanks

Jack

**VBA Noob** · 01-27-2008, 03:42 PM

See attached

VBA Noob

**jhelliar** · 01-27-2008, 04:23 PM

Many thanks for that how did you get from mine to yours. I can add the name and year in the layout but it seems to add all the names how can I just get the one with the smallest average?

**VBA Noob** · 01-27-2008, 04:31 PM

Did you even watch the video's ??

Just untick the names you don't require
http://www.contextures.com/xlPivot04.html

VBA Noob

**no dice** · 01-28-2008, 02:30 AM

Originally Posted by jhelliar

Many thanks for that how did you get from mine to yours. I can add the name and year in the layout but it seems to add all the names how can I just get the one with the smallest average?

In your pivot table, double click the field "House".
Advanced
Top 10 Auto Show, "On"
Show: "Bottom", "1"
Using Field: "Min of Average"

**royUK** · 01-28-2008, 05:05 AM

Originally Posted by jhelliar

Many thanks for everyone's help. Is there a site which explains how to use pivot tables properly? I have played around with them but can never get them to quite do what I want.

Again Many thanks

Jack

PivotTable examples & Tutorial at

www.excel-it.com.