# Thread: Calculating Stdev and Averages in pivot table that has multiple columns.

1. ## Calculating Stdev and Averages in pivot table that has multiple columns.

Hello folks,
I am faces with a big challenge that I cannot seem to find the answer to. If you can please help me out.
I have a spread sheet that looks like this:

Excel.jpg

I have tried to use the calculated field option with no success. I can arrange everything as I need but I cannot seem to make the pivot table read from more than one column. It always spits out Data 1, Data 2, Data 3, Data 4 all separately. Also the Pivot table will not allow merging of column names so I am stuck with this problem.
My actual spread sheet is a lot larger with a lot more data. But this will get me started.

2. If you want to use a pivot table, your source data must be setup differently. Data only in 1 column and a column for Group. More like:
Date | Group | Data
12/19/2013 | A | 490
12/19/2013 | A | 492
12/20/2013 | A | 467
12/20/2013 | A | 490
12/19/2013 | A | 442
12/19/2013 | A | 500
12/20/2013 | A | 416
12/20/2013 | A | 414
12/19/2013 | B | 406
12/19/2013 | B | 433
12/20/2013 | B | 439
12/20/2013 | B | 414
12/19/2013 | B | 476
12/19/2013 | B | 499
12/20/2013 | B | 402
12/20/2013 | B | 432
You can include a column of Data1-Data4 if desired as well...]

To get the information out as it is setup would require some array formulas.
I con't provide details without the ranges you use which is not in the screenshot (in practice attaching a file with sample data is better so we can see the setup and work with your example data). [Note: it seems to me that your summary is messed up: the 12/20 A and 12/19 B avg/std seem to me to be reversed...]

Steve

3. As shown in the previous post, you cannot use split columns in a pivot table. The excel table must be linear with regular rows and columns. Also you cannot use a calculated field twice in the same pivot table. The results that you require can be achieved, but you need to reconsider the naming and structure of your columns. I reproduced your sample table to show this. First I created a calculated column 'Total A' from B2+C2, and a second calculated column 'Total B' from D2+E2. I recreated both these columns again, using the same cells, and called them 'Total AA' and 'Total BB', and created the pivot table from that data.

Table 1.JPG

Table2.JPG

In the pivot table the Date field goes into the columns box. The four calculated fields go into the Values box. This will leave a Values field in the Columns box. Drag this into Rows box and the pivot table will be created. It is then just a matter of changing the field settings of the calculated field to 'Average' and 'StdDev'.

#### Posting Permissions

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