Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2013
    Thanked 0 Times in 0 Posts

    Smile 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:


    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.

    Thanks in advance for all your help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    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...]


  3. #3
    New Lounger kalvinson's Avatar
    Join Date
    Dec 2013
    The beautiful and serene Yorkshire Dales, England, UK
    Thanked 0 Times in 0 Posts
    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


    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'.
    Last edited by kalvinson; 2013-12-20 at 09:03.

Posting Permissions

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