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?
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? – None – 2010-04-25T19:16:03.907
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 – 2010-04-25T23:13:10.453
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. – None – 2010-04-25T23:33:05.883
You can apply normal worksheet functions (such as Count, Sum & Average) on the data fields in a calculate field. Does that help? – Mike Fitzpatrick – 2010-04-25T23:59:56.473
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? – None – 2010-04-26T00:57:23.003
They do work, but how is not always obvious. I sometimes need to manually check that they calculate the way I think/hope they are. But there are limitations on what you can do with calculated fields. – Mike Fitzpatrick – 2010-04-26T01:05:24.860