Thread: 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

Please take a few minutes to read the forum rules, and then amend your thread title accordingly. It should describe what you're trying to do, not what you speculate is the solution, and skip all the spurious words.

Thanks.

In B2

=SUMPRODUCT(--($A2=INT('RAW DATA'!$A$3:$A$137/100)),'RAW DATA'!B$3:B$137)/SUMPRODUCT(--($A2=INT('RAW DATA'!$A$3:$A$137/100)))

copy down and across

This will yield #DIV/0! for floors/scores that have zero values, but maybe in your complete dataset this does not occur. If it does, you can wrap an IF statement around it to test for it, like

IF(SUMPRODUCT(--($A2=INT('RAW DATA'!$A$3:$A$137/100)))=0,0,SUMPRODUCT(--($A2=INT('RAW DATA'!$A$3:$A$137/100)),'RAW DATA'!B$3:B$137)/SUMPRODUCT(--($A2=INT('RAW DATA'!$A$3:$A$137/100))))

To single out the room number that is represented by the first digit of a three digit number or the first two digits in a four digit number, divide the number by 100 and keep the integer part, i.e. the part of the result in front of the decimal point.

=INT(A3/100)