Help to calculate average based on filtering - including dataset

[signekolind]

Hi Everbody :-)

I have a problem that I have tried to simplify. Basically I have a large data set over some stocks, their monthly stock prices and an indicator whether a stock is a winner or not.

I need to find the average of the winner stock prices in each month. This can be done manually by filtering by the date and winner and the manually calculate the average. The dataset I have is very big, so I need to find a way where this is not done manually, but maybe by VBA.

I have attached a simplified dataset where I have done the calculation manually for month 1.

I hope to hear from you :-)

[Crooza]

see attached. I used a pivot table.

[signekolind]

Thank you for the help, maybe I wasn't clear but I need the average for every month in a column or row :-)

[Crooza]

that's what you've got. There were no 'winners' in month 2 so no data. there was one winner in month 3 which is shown

[signekolind]

Ah okay, my bad, thank you so much! :-) Maybe I had simplified the problem to much, because I actually have an extra criteria which is that the company's size is NotSmall, which I can not figure out how to implement using your pivot table. I have added it to you document! I hope you can help, but you help have already been really amazing! :-)

[JeteMc]

If I understand correctly then the following may help:
1. From the PivotTable Tools tab > Analysis sub tab > Change Data Source so that Table/Range reads: Sheet4!$A$1:$E$10
2. From the same sub tab select Refresh
3. Put Size in the Filters area
4. [optional] Put Month in the Filters area
Let us know if you have any questions.