Array Average Formula issues

[mclark08fdic]

When I first posted to the forum I was looking for a new formula that I could use to calculate the averages of each columns based on the title agency listed. Now the data in my print area is good on the exception of the Averages Column which takes the total average for each agency for all modules. I used an array formula, but it is way off. I tried using the array formula to take the average of the average, which gets me closer to the correct value, but not quite. I've attached the sheet I'm having issues with.

The raw data that I've inputted is at the top in grey while the printout is at the bottom in the tables. The first table with the last column marked Averages is the one in questions. All other formulas work as inputted. What is wrong?

From doing the manual calcuations, I get the following averages per agency

FRB 1.69
FDIC 1.77
NCUA 1.70
OCC 1.58
FCA 1.21
FHFA 1.92
UNKNOWN 1.54

[djapigo]

Hi mclark,

It seems AVERAGE is not as "smart" as we want it to be...

I added a couple of columns (AC and AD), then I changed your formulas in AC35:AC45...

TEST COPY(1).xlsx

I don't know if you like this solution, but it's the only why I can get around it...

- Dennis