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?
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?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
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.....
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks