Summarizing raw data

Hi all,

I was asked at work to create a report that takes raw data from a questionnaire and compiles it in an easy-to-read report. (Little do they know that this is how I'm such a wiz at excel ;-) )

It seems pretty simple, but I can't seem to figure it out! On the attached workbook, the first spreadsheet,"SUMMARY", is the jist of what the report should look like - the average score for each question by floor. On the next spreadsheet, "RAW DATA", is a sample of what the raw data looks like - just a list of each room number and the score it received by question. the first 2 digits of the room number signify the floor (ex. 201=2nd floor, 1215=12th floor, etc.).

My major issue was with getting the score for each floor without having to manipulate the raw data - this report will eventually become a shell that can be used with new data every month. Is there a way to make a range equal a number (ie. 200-299=2)?

I need to have this ready by tomorrow, so any help you can offer will be greatly appreciated!!

Thanks in advance,

- Sonya