zalik
asked on
using an array to sum row averages
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
etc
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%
etc
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.
samplefile1.xlsx
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
etc
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%
etc
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.
samplefile1.xlsx
ASKER
Not sure that's returning what I'm looking for, though it's getting me closer.
For Area1, calculating manually, I get 1 site that averaged <2 (site "g" got an average of 1.8, with scores of 2, 2, 1, 2, 2). There were 15 sites, so around 7% of sites scored <2 (1/15).
The attached file had a mistake, which I see you caught, in that the last column is for the % of sites scoring between 2-3 (not <3, which would encompass those that scored <2).
For Area1, calculating manually, I get 1 site that averaged <2 (site "g" got an average of 1.8, with scores of 2, 2, 1, 2, 2). There were 15 sites, so around 7% of sites scored <2 (1/15).
The attached file had a mistake, which I see you caught, in that the last column is for the % of sites scoring between 2-3 (not <3, which would encompass those that scored <2).
Their you go..I added an extra column to first calculate the site average basis area wise and then giving you the desired results what you were looking for...
Saurabh...
samplefile1.xlsx
Saurabh...
samplefile1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Saurabh...
samplefile1.xlsx