Take an Average of values only If a condition is satisfied.

[alsmith]

Hi all.

I have a table that has data as such:

Please Login or Register  to view this content.

I need to organise it into percentages of each category, counting "Yes" as "100%", but can't seem to work out a formula.

I have an overall formula for history complete, which is:

Please Login or Register  to view this content.

What I'm looking to do is say,

• Filter to Category A

• Take AVERAGE of History Complete for Category A

And so on for other Categories. Possible?

[ChemistB]

Try at the bottom of your data
=SUBTOTAL(101,B2:B14)
The 101 is the code to tell Excel to Average but ignore hidden values
Does that work for you?

[daddylonglegs]

You can get the average for a specific category like this

=AVERAGE(IF(List!M2:M256="A",IF(List!N2:N256="YES",100%,List!N2:N256)))

confirmed with CTRL+SHIFT+ENTER

that will give you the average for category A whether filtered or not........or do you want it to automatically calculate the average for visible values? If so try

=AVERAGE(IF(SUBTOTAL(3,OFFSET("List!M2,ROW(List!M2:M256)-ROW(List!M2),0)),IF(List!N2:N256="YES",100%,List!N2:N256)))

assumes that category column is M.....