Hi all - thanks in advance for your help. As my username says, I am an excel noob, and struggling with even the google tutorials on these topics. I attempted a simple array "averageif" formula - but receiving errors, and just a test case.

There are 4 sheets, with screenshots provided below:

1) Universe sheet - In this sheet, column C has all of my "industry codes" - and this extends all the way down 200 or so rows. Columns D - VJ are the companies (each cell has a unique ticker code) that is assigned to this code. Companies can exist in multiple industry codes.

2) Raw Revenue - In this sheet, from 2000 - present, are quarterly revenue data points for each "ticker/company". Column A is the company ticker, D onwards are the data points

3) Net Margin - In this sheet, I've calculated Net Margins for each company for each time series. Very similar sheet to Screenshot #2

4) Averaging sheet - In this sheet, I would like to take a revenue weighted average of the net margins shown in screenshot 3 - by industry code, as shown in screenshot 1.

All industry codes are shown in column C. A list of the companies in each code is in the first sheet. All the Revenue data is in screenshot 2. screenshot 3 has the net margin percentages. Basically, for each time period that has a revenue data point, take a revenue weighted average of the netmargin percentage.

I attempted in screenshot 4 to just take a simple averageif of the netmargin, which is a simple average.

Questions: 1) Why do I have so many div/0? The array formula does give me a few good data sets, but any idea why have so many div/0? I thought the averageif formula excludes missing data points?
2) How do I take a revenue weighted average as mentioned above? "
All industry codes are shown in column C. A list of the companies in each code is in the first sheet. All the Revenue data is in screenshot 2. screenshot 3 has the net margin percentages. Basically, for each time period that has a revenue data point, take a revenue weighted average of the netmargin percentage."

Here is the formula i dragged out and array ctrl shift enter on in sheet 4: =AVERAGEIF('Calculated "Net Margin"'!$A$3:$A$3791,'BICS & Equities Clean H'!$D3:$ZZ3,'Calculated "Net Margin"'!AZ$3:AZ$200)

1.png
2.png
3.png
4.png