Average of a field in excel

[mmr3b9]

Hi,

I have data in excel 2000 and i need to get the average of the "Employ" field grouped by all the other fields. Here is an example of the data:


Year Month Owner NAICS Employ
1990 4 3 2210 44
1991 6 3 6111 2794
1994 7 5 6116 54
1993 12 5 2120 3
1994 4 5 6116 236
1990 4 3 2210 321


Here is some code that i have been able to come up with so far. How can i include the "NAICS" field? Any help will really be appreciated.

Sub ann_avg()

dim owner, year, month, naics as integer
dim sumemp, avgemp as double

select case owner

Case is = 5

For year = 1990 to 2001
emp = 0
for month = 1 to 12
sumemp = emp + sumemp
Next Month
avegemp = sumemp/12
next year

Case is = 3

For year = 1990 to 2001
emp = 0
for month = 1 to 12
sumemp = emp + sumemp
Next Month
avegemp = sumemp/12
next year

Case else

For year = 1990 to 2001
emp = 0
for month = 1 to 12
sumemp = emp + sumemp
Next Month
avegemp = sumemp/12
msgbox avgemp
next year

End Select

End Sub


Thanks,
Mike

 

[papajohn20]

Can you be more specific? For exaple how many diferrent value do you have for NAICS? After you group the average og employ by including NAICS you would like to continue grouping ALL the other variables? Do you mind your data to be arranged differently or it must be exactly like you have it? That will help alot.

 

[DrBowes]

Have you tried pivot tables?

 

[mmr3b9]

Hi,

thanks for the replies...there are about 111 different NAICS values. and the years go from 1990-2001. i would like to group by all the variables including naics. I would like the data to be arranged in a simlar way as it is now. but it does not have to be exactly the same. im not suppose to use a pivot table, but thanks for the suggestion.

Thanks,
mike