Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Say I have a table with columns A = {1..5}, B = {a..e} and Score = Number. I want to produce a new table that would display statistics for each A, B combination.

e.g.

A   B   Mean   SD
1   a     ..   ..
1   b     ..   ..
...
2   a     ..   ..

What's the best way of doing this?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Use a Pivot Table for this kind of summary.

  • Insert a Pivot Table
  • Drag A and B from the Field List to Row Lables
  • Drag Score from the Field List to Values and set its Value Field Settings to Average
  • Drag Score again from the Field List to Values and set its Value Field Settings to StdDev
  • Go to Design Tab and set Report Layout to Outline Form
share|improve this answer
    
that's great! just one more thing - what if i'd like a measurement that is not listed in the value field settings? e.g. i'd like to see 95% and 99% confidence intervals - is there a way to do that as well? –  user34502 Apr 25 '10 at 19:16
    
You can add Calculated Fields to a pivot table, which can provide a lot of flexibility. You'll find these in Excel 2010 on the PivotTable Tools ribbon under Fields, Items & Sets. –  Mike Fitzpatrick Apr 25 '10 at 23:13
    
yeah, i've been trying them for a while now, but with little luck. e.g. to calculate confidence i need the mean value, but none of the pivot table summary fields appear in the fields list for the new formula. –  user34502 Apr 25 '10 at 23:33
    
You can apply normal worksheet functions (such as Count, Sum & Average) on the data fields in a calculate field. Does that help? –  Mike Fitzpatrick Apr 25 '10 at 23:59
    
Do they work? I got the feeling the fields are already sums, not the ranges behind. at least so far, sum/min/max (field) return the same value as the field itself, and none of the range functions work. Where am I going wrong? –  user34502 Apr 26 '10 at 0:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.