formula to find a mean over a range based on two criterias

[vxg]

hello, I need some help here:
basically what i have now is 70,000 observations of assets of companies in different years in diff industry (industry is represented by 4-digits sic code), what i want is a formula that could give me a mean of the assets in each year in each industry. But what makes it more complicated is that the criteria I use for SIC is only the first two digits
for example:
company fyear assets SIC
A 2000 10 1222
B 2001 20 1234
D 2000 30 1233
C 2000 40 1345
E 2001 50 1234
F 2000 60 1356

I need the result to be
fyear first 2 digits SIC average of assets
2000 12 20
2001 12 35
2000 13 50

ideas ?? thank you !!

[NBVC]

Create a helper column with the 2-digit SIC with formula

=Left(C2,2)+0

copied down where Column C is your 4-digit SIC column

Then create a Pivot Table from the year, Qty and 2-digit SIC column

Drag the year and 2-Digit SIC into the Row data area (in that order) and then drag the Qty to the Data (Sigma) area.

[daddylonglegs]

If your data is in B2:D100 then you could use AVERAGEIFS like this in H2 copied down

=AVERAGEIFS(D$2:D$100,B$2:B$100,F2,C$2:C$100,">="&G2*100,C$2:C$100,"<"&(G2+1)*100)

where F2 contains a specific year and G2 the 2 digit code

[vxg]

@NBVC and daddylonglegs
thanks for the formula, in the end I use pivot table because it seems easier to implement

but I have one question: is there a reason why I should drag year first ? i mean its just the same if i drag sic first right ? its just the layout will be different ?

thank you

[NBVC]

Exactly, the layout would be different, it is your choice... to mimic the results you want, you would drag the year first,.. It will sort by year first, then group the SICS for each year...

It you drag SIC first, you will get all the SICs listed and then each one will be broken down by year..

.. whatever your preference, experiment with it.