Summarizing raw data

Hi all,

I posted last night regarding a spreadsheet that I needed help with and I got exactly the solution I needed. The problem is, I need to further breakdown the summarized data and I'm not sure how to go about doing it...

I have attached 2 woorkbooks - "SAMPLE" which is what the report should look like and "RAW DATA" which is how I get the data.

The main issue is breaking down the raw data into floors and sections... If you look at "SAMPLE", I have a breakdown of all the floors and the rooms of each section on the floor (columns A and B). Also, while braistorming, I put together an array in columns L and M with each room number and the section it is part of, thinking I could maybe use a VLOOKUP function, but not sure how to go from here.

What I need to get from "RAW DATA" is the average of each score for each section ("SAMPLE" Columns E,G,I) and the number of responses for each section ("SAMPLE" Columns F,H,J).

I appreciate any help I can get with this!

Thanks in advance,

Sonya

Sonya,
Here’s the way I approached the problem and it seems to work.

I copied the “September 09” worksheet into “SAMPLE.xls”.
I used VLOOKUP to assign a section to each room number in the raw data and named that range FlSec.
I used named ranges for the SCORE_1, SCORE_2 and SCORE_3.
I entered the following array formula (Ctl+Shift+Enter) in column E

=IF(ISERROR(AVERAGE(IF(FlSec=B6,SCORE_1))),0,AVERAGE(IF(FlSec=B6,SCORE_1)))

The same array formula is used in columns G and I changing SCORE_1 to SCORE_2 for column G and SCORE_3 for column I.
I entered the following formula in column F.

=SUMPRODUCT((FlSec=B4)*(SCORE_1<>""))

The same formula is used in columns F and J changing SCORE_1 to SCORE_2 for column F and SCORE_3 for column I.

Hope this helps,
Gary