Average of column depending on filter

[jwdemo]

I am wanting cell E1 to show the average of Column H depending on what is showing.

The worksheet shows margins for items in our store. I want this to show what the average margin is if only one department is showing or one category or a few departments, etc. Basically, whatever is showing in the worksheet in Column H should be averaged in cell E1.

Some of the items have a text value of "#DIV/0!". Can the average formula work around these items instead of showing the same text value as a result?

Thanks for the help! Let me know if I forgot any information that would be helpful in getting this to work.

 

[FDibbins]

Have a look at using SUBTOTAL for this