I have a dataset that has audit questions as columns and sites as rows (sample file attached). Input looks like:
SiteName Area1Q1 Area1Q2 Area1Q3 Area2Q1 Area2Q2 etc
a 3 4 3 4 3
b 2 4 4 2 3
c 1 1 2 N/A 1
I want to return the # or % of sites that scored an average in Area1 of <2 (% of sites is preferable, but # of sites would be ok).
So, the average score for sitea in area1 is (3+4+3)/3=3.3, and for area2 is 4+3/2=3.5
Average score for siteb in area1 is (2+4+4)/3=3.3, and for area2 is 2+3/2=2.5
Average score for sitec in area 1 is (1+1+2)/3=1.3, and for area2 is 1 (N/A doesn't count)
My desired output looks like this:
Area % sites that scored on average <2 % of sites that scored on avg 2-3
1 33% 0%
2 33% 33%
We will have hundreds of sites, and there are 24 Areas. The overall average for all sites was easy. But I can't figure out how to code the following:
1. Computing an average of values within a row across a number of columns =AVERAGE(C3:G3), repeated for each row in the sheet
2. Summing up all of the results of step1 that are equal to <2
3. Dividing the result of step2 by the total number of rows and returning that average
I'd be happy with only steps #2-3 above (getting the Number of sites with an average score <2 instead of the % of sites is OK).
Any help appreciate. Sample file attached. Note that sheet "DATA" has the database, and sheet "DESIRED OUTPUT" has where the output goes.