Average of Variables based on categories in another column

I have an excel sheet with Industry Codes in a particular column and firm returns and risk in two columns. Now I want an average return and risk of the industry in the last two columns. That is for all firms in the same industry the values will be the average of returns (Average of SRET) and average of risk (Average of SRI) of all firms and it will obviously be the same value for all firms with a particular industry code. The issue is this can be done with some tediousness through the normal formula mode.

However, there will be instances when I will be deleting a few records and I want a macro that will automatically recalculate the industry average based on records left and update the values. Can you please help me with this. I need this pretty bad coz I have over 60 variables in as many sheets.

I have attached the excel sheet for your convenience. Any help in providing a solution to this is highly appreciated.

Thanks in Advance

Best Regards
Srini

Hi

The easiest and most convenient way would appear to be with a Pivot Table. A PT can handle deleting records from your source data and can be built from multiple data ranges on seperate sheets.

In the Pivot Table Wizard Layout Window, you would have these fields:

Row area
NIC Code

Data area*
Average of SRI
Average of SRET

*The default setting for the two data items SRI and SRET is 'Sum of', so double-click each and change to 'Average'.


When your source data changes, click Refresh Data on the PivotTable Toolbar and both averages will update for all NIC Codes.

Hi T-J,

Thanks a lot! That solves my problem. Though I had an idea about Pivot Table I never knew it did this. Thanks once again!

Best Regards
Srini