Excel help: I think this needs to end up as a pivot table

[dancelnow]

I have a list of 50 tasks that have 4 attributes each. I had people rate the tasks on those attributes.  What’s the best way to aggregate those responses? Right now it’s the 50 tasks down columns A-D (some extra sorting info in there) and the people added ratings in columns E-H. Everyone sent their response back as a separate file.  

[ginger]

Once you compile everyone's answers into a table, I agree a Pivot Table will represent the data best.

Are you familiar with Pivot Tables?

[dancelnow]

Nov 28, 2017 11:39:17 GMT -5 ginger said:

Once you compile everyone's answers into a table, I agree a Pivot Table will represent the data best.

Are you familiar with Pivot Tables?

I can manipulate them, but I’m still unsure what format the underlying data should be in start with.

[kath16]

Nov 28, 2017 11:58:50 GMT -5 dancelnow said:

Nov 28, 2017 11:39:17 GMT -5 ginger said:

Once you compile everyone's answers into a table, I agree a Pivot Table will represent the data best.

Are you familiar with Pivot Tables?

I can manipulate them, but I’m still unsure what format the underlying data should be in start with.

It will spit out whatever you put in. Once you figure out what data you want to aggregate, you can fiddle with the pivot table tool to make it look how you want...

...does that help at all?

[y4m]

I think you want to get your data into:

Task # - Attribute # - Rating

That way the pivot could filter for just one task, etc.

What are you trying to find in the end?

[dancelnow]

Nov 28, 2017 12:12:31 GMT -5 y4m said:

I think you want to get your data into:

Task # - Attribute # - Rating

That way the pivot could filter for just one task, etc.

What are you trying to find in the end?

I want to be able to see average rating for each attribute of each task. Ex: task 1 on average was rated medium difficulty, completed weekly and of low importance.

[y4m]

Ok - then the database I would want is:

Task Name and Additional sorting data A-D - Difficulty Rating - Frequency Rating - Importance Rating - Rater Name  You're essentially stacking the spreadsheet each person returned and adding an additional column at the end that tells you which person gave that rating.

Not sure how you will pivot the ratings though (if those are numeric so you'd report a mean or what).

[dancelnow]

Nov 28, 2017 12:48:34 GMT -5 y4m said:

Ok - then the database I would want is:

Task Name and Additional sorting data A-D - Difficulty Rating - Frequency Rating - Importance Rating - Rater Name  You're essentially stacking the spreadsheet each person returned and adding an additional column at the end that tells you which person gave that rating.

Not sure how you will pivot the ratings though (if those are numeric so you'd report a mean or what).

Thanks! I can convert each of those to a numeric for the sake of averaging.