1

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?

1 Answer 1

2

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
6
  • 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, 2010 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. Apr 25, 2010 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, 2010 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? Apr 25, 2010 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, 2010 at 0:57

You must log in to answer this question.