+ Reply to Thread
Results 1 to 4 of 4

Summarizing raw data

  1. #1
    Registered User
    Join Date
    04-07-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    12

    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
    Attached Files Attached Files
    Last edited by sonyap; 10-29-2009 at 07:38 AM. Reason: Updating thread title

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: HELP! Possible VLOOKUP formula? Not sure...

    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.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Summarizing raw data

    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)
    Last edited by teylyn; 10-29-2009 at 03:21 AM.

  4. #4
    Registered User
    Join Date
    04-07-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Summarizing raw data

    Thanks!

    That's exactly what I need. I had a feeling it was going to be a SUMPRODUCT, but I wasn't sure to go about it. I can't believe I didn't think of dividing by 100!! I guess I was thinking way too much into it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1