# 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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I'm assuming this is what you are looking for...

Saurabh...
samplefile1.xlsx
0
Author Commented:
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).
0
Commented:
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
0
Commented:
One way without helper columns:
D4: =SUM(--(IFERROR(SUBTOTAL(1,OFFSET(DATA!\$C\$3:\$G\$17,ROW(DATA!\$C\$3:\$G\$17)-MIN(ROW(DATA!\$C\$3:\$G\$17)),0,1)),"")<2))/COUNTA(DATA!\$A\$3:\$A\$17)

E4: =SUM(--(IFERROR(SUBTOTAL(1,OFFSET(DATA!\$C\$3:\$G\$17,ROW(DATA!\$C\$3:\$G\$17)-MIN(ROW(DATA!\$C\$3:\$G\$17)),0,1)),"")<3))/COUNTA(DATA!\$A\$3:\$A\$17)-D4

Both of these must be array-entered with Ctrl+Shift+Enter. You need to adjust the ranges for the other areas.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.