Pivot table calculated item problems

Hi,

Given a set of raw data shaped like this:

Cat    Item    Value
--------------------
  1        1        45
  1        2        37
  1        3        40
  2        1        38
  2        2        34
  2        3        31
  3        1        36
  3        2        39
  3        3        40
.......

I created a pivot table that uses "Cat" as a row field and "Value" as a data 
field. I then change the summary function for the "Value" field to use 
"Average". Next, I add the "Value" field as a data field again and this time 
change the summary function to "StDev". This all works ok.

I'd like to add a calculated item to my pivot table that allows me to take 
the standard deviation of "Value" and divide that by the average of "Value" 
to calculate the coefficient of variance.

When I try to add a calculated item to calculate the coefficient of variance 
(i.e. STDEV(value) / AVG(value) , I get the following error from Excel:

"Multiple data fields of the same field are not supported when a pivottable 
report has calculated items"

However, even if I eliminate this problem, I get another message from Excel 
when trying to add a calculated item as follows:

"Averages, standard deviations, and variances are not supported when a 
pivottable report has calculated items"

Can anyone suggest how I would go about adding this calculation (i.e. 
STDEV(value) / AVG(value) to my pivot table?

Thanks in advance,
Jim


0
Jim
3/15/2005 10:41:41 PM
excel 39879 articles. 2 followers. Follow

5 Replies
5446 Views

Similar Articles

[PageSpeed] 38

You could do the calculation in a column adjacent to the pivot table, 
but you won't be able to create a calculated field to do it in the pivot 
table.

Jim~C wrote:
> Hi,
> 
> Given a set of raw data shaped like this:
> 
> Cat    Item    Value
> --------------------
>   1        1        45
>   1        2        37
>   1        3        40
>   2        1        38
>   2        2        34
>   2        3        31
>   3        1        36
>   3        2        39
>   3        3        40
> ......
> 
> I created a pivot table that uses "Cat" as a row field and "Value" as a data 
> field. I then change the summary function for the "Value" field to use 
> "Average". Next, I add the "Value" field as a data field again and this time 
> change the summary function to "StDev". This all works ok.
> 
> I'd like to add a calculated item to my pivot table that allows me to take 
> the standard deviation of "Value" and divide that by the average of "Value" 
> to calculate the coefficient of variance.
> 
> When I try to add a calculated item to calculate the coefficient of variance 
> (i.e. STDEV(value) / AVG(value) , I get the following error from Excel:
> 
> "Multiple data fields of the same field are not supported when a pivottable 
> report has calculated items"
> 
> However, even if I eliminate this problem, I get another message from Excel 
> when trying to add a calculated item as follows:
> 
> "Averages, standard deviations, and variances are not supported when a 
> pivottable report has calculated items"
> 
> Can anyone suggest how I would go about adding this calculation (i.e. 
> STDEV(value) / AVG(value) to my pivot table?
> 
> Thanks in advance,
> Jim
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
3/15/2005 11:12:48 PM
Deb,

Thanks for your reply.

Hmm...I'm trying to create a reusable "template" for data analysis of this 
data from a database. So, adding an adjacent column seems dangerous for two 
reasons:

    - The calculations would use GETPIVOTDATA function which does not fill 
down correctly
    - What happens if the user rearranges the pivot table. Does this destroy 
the adjacent column and it's data?

It certainly helps to know that I'm barking up the wrong tree with trying 
the calculated item. I'm scratching my head for a better approach...

Cheers,
Jim


"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message 
news:42376BF0.3040802@contexturesXSPAM.com...
> You could do the calculation in a column adjacent to the pivot table, but 
> you won't be able to create a calculated field to do it in the pivot 
> table.
>
> Jim~C wrote:
>> Hi,
>>
>> Given a set of raw data shaped like this:
>>
>> Cat    Item    Value
>> --------------------
>>   1        1        45
>>   1        2        37
>>   1        3        40
>>   2        1        38
>>   2        2        34
>>   2        3        31
>>   3        1        36
>>   3        2        39
>>   3        3        40
>> ......
>>
>> I created a pivot table that uses "Cat" as a row field and "Value" as a 
>> data field. I then change the summary function for the "Value" field to 
>> use "Average". Next, I add the "Value" field as a data field again and 
>> this time change the summary function to "StDev". This all works ok.
>>
>> I'd like to add a calculated item to my pivot table that allows me to 
>> take the standard deviation of "Value" and divide that by the average of 
>> "Value" to calculate the coefficient of variance.
>>
>> When I try to add a calculated item to calculate the coefficient of 
>> variance (i.e. STDEV(value) / AVG(value) , I get the following error from 
>> Excel:
>>
>> "Multiple data fields of the same field are not supported when a 
>> pivottable report has calculated items"
>>
>> However, even if I eliminate this problem, I get another message from 
>> Excel when trying to add a calculated item as follows:
>>
>> "Averages, standard deviations, and variances are not supported when a 
>> pivottable report has calculated items"
>>
>> Can anyone suggest how I would go about adding this calculation (i.e. 
>> STDEV(value) / AVG(value) to my pivot table?
>>
>> Thanks in advance,
>> Jim
>>
>>
>
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 


0
Jim
3/15/2005 11:24:54 PM
You can turn off GETPIVOTDATA and fill down normally

See Debra's site at:-
http://www.contextures.com/xlPivot06.html

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
                  It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip>


0
ken.wright (2489)
3/15/2005 11:57:30 PM
It seems to me that you could create a separate column (called CV) in the
Excel Table that calculates the CV in a specific Cell using the formula you
noted.  Then when you create the Pivot table use that column in the data
area for the Pivot table.






"Jim~C" <jdchanATnovusint.com> wrote in message
news:eTUdzYbKFHA.3512@TK2MSFTNGP15.phx.gbl...
> Deb,
>
> Thanks for your reply.
>
> Hmm...I'm trying to create a reusable "template" for data analysis of this
> data from a database. So, adding an adjacent column seems dangerous for
two
> reasons:
>
>     - The calculations would use GETPIVOTDATA function which does not fill
> down correctly
>     - What happens if the user rearranges the pivot table. Does this
destroy
> the adjacent column and it's data?
>
> It certainly helps to know that I'm barking up the wrong tree with trying
> the calculated item. I'm scratching my head for a better approach...
>
> Cheers,
> Jim
>
>
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> news:42376BF0.3040802@contexturesXSPAM.com...
> > You could do the calculation in a column adjacent to the pivot table,
but
> > you won't be able to create a calculated field to do it in the pivot
> > table.
> >
> > Jim~C wrote:
> >> Hi,
> >>
> >> Given a set of raw data shaped like this:
> >>
> >> Cat    Item    Value
> >> --------------------
> >>   1        1        45
> >>   1        2        37
> >>   1        3        40
> >>   2        1        38
> >>   2        2        34
> >>   2        3        31
> >>   3        1        36
> >>   3        2        39
> >>   3        3        40
> >> ......
> >>
> >> I created a pivot table that uses "Cat" as a row field and "Value" as a
> >> data field. I then change the summary function for the "Value" field to
> >> use "Average". Next, I add the "Value" field as a data field again and
> >> this time change the summary function to "StDev". This all works ok.
> >>
> >> I'd like to add a calculated item to my pivot table that allows me to
> >> take the standard deviation of "Value" and divide that by the average
of
> >> "Value" to calculate the coefficient of variance.
> >>
> >> When I try to add a calculated item to calculate the coefficient of
> >> variance (i.e. STDEV(value) / AVG(value) , I get the following error
from
> >> Excel:
> >>
> >> "Multiple data fields of the same field are not supported when a
> >> pivottable report has calculated items"
> >>
> >> However, even if I eliminate this problem, I get another message from
> >> Excel when trying to add a calculated item as follows:
> >>
> >> "Averages, standard deviations, and variances are not supported when a
> >> pivottable report has calculated items"
> >>
> >> Can anyone suggest how I would go about adding this calculation (i.e.
> >> STDEV(value) / AVG(value) to my pivot table?
> >>
> >> Thanks in advance,
> >> Jim
> >>
> >>
> >
> >
> > --
> > Debra Dalgleish
> > Excel FAQ, Tips & Book List
> > http://www.contextures.com/tiptech.html
> >
>
>


0
zelig (9)
3/17/2005 10:24:57 PM
Yes, that would be great except that calculating the CV is only valid when 
using the averages calculated by the pivot table.

"Zel Dolinsky" <zelig@snet.net> wrote in message 
news:Zsn_d.10960$DW.936@newssvr17.news.prodigy.com...
> It seems to me that you could create a separate column (called CV) in the
> Excel Table that calculates the CV in a specific Cell using the formula 
> you
> noted.  Then when you create the Pivot table use that column in the data
> area for the Pivot table.
>
>
>
>
>
>
> "Jim~C" <jdchanATnovusint.com> wrote in message
> news:eTUdzYbKFHA.3512@TK2MSFTNGP15.phx.gbl...
>> Deb,
>>
>> Thanks for your reply.
>>
>> Hmm...I'm trying to create a reusable "template" for data analysis of 
>> this
>> data from a database. So, adding an adjacent column seems dangerous for
> two
>> reasons:
>>
>>     - The calculations would use GETPIVOTDATA function which does not 
>> fill
>> down correctly
>>     - What happens if the user rearranges the pivot table. Does this
> destroy
>> the adjacent column and it's data?
>>
>> It certainly helps to know that I'm barking up the wrong tree with trying
>> the calculated item. I'm scratching my head for a better approach...
>>
>> Cheers,
>> Jim
>>
>>
>> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
>> news:42376BF0.3040802@contexturesXSPAM.com...
>> > You could do the calculation in a column adjacent to the pivot table,
> but
>> > you won't be able to create a calculated field to do it in the pivot
>> > table.
>> >
>> > Jim~C wrote:
>> >> Hi,
>> >>
>> >> Given a set of raw data shaped like this:
>> >>
>> >> Cat    Item    Value
>> >> --------------------
>> >>   1        1        45
>> >>   1        2        37
>> >>   1        3        40
>> >>   2        1        38
>> >>   2        2        34
>> >>   2        3        31
>> >>   3        1        36
>> >>   3        2        39
>> >>   3        3        40
>> >> ......
>> >>
>> >> I created a pivot table that uses "Cat" as a row field and "Value" as 
>> >> a
>> >> data field. I then change the summary function for the "Value" field 
>> >> to
>> >> use "Average". Next, I add the "Value" field as a data field again and
>> >> this time change the summary function to "StDev". This all works ok.
>> >>
>> >> I'd like to add a calculated item to my pivot table that allows me to
>> >> take the standard deviation of "Value" and divide that by the average
> of
>> >> "Value" to calculate the coefficient of variance.
>> >>
>> >> When I try to add a calculated item to calculate the coefficient of
>> >> variance (i.e. STDEV(value) / AVG(value) , I get the following error
> from
>> >> Excel:
>> >>
>> >> "Multiple data fields of the same field are not supported when a
>> >> pivottable report has calculated items"
>> >>
>> >> However, even if I eliminate this problem, I get another message from
>> >> Excel when trying to add a calculated item as follows:
>> >>
>> >> "Averages, standard deviations, and variances are not supported when a
>> >> pivottable report has calculated items"
>> >>
>> >> Can anyone suggest how I would go about adding this calculation (i.e.
>> >> STDEV(value) / AVG(value) to my pivot table?
>> >>
>> >> Thanks in advance,
>> >> Jim
>> >>
>> >>
>> >
>> >
>> > --
>> > Debra Dalgleish
>> > Excel FAQ, Tips & Book List
>> > http://www.contextures.com/tiptech.html
>> >
>>
>>
>
> 


0
Jim
3/19/2005 2:21:37 AM
Reply:

Similar Artilces:

Having problem of Outlook not receiving new e-mails
I have been using Outlook for over 1 year with Comcast Broadband. Suddenly last week, I received a message that Outlook 2000 was experiencing a problem when receiving new e-mails and was going to shut down. I re-opened Outlook and it looked different and then it asked for me set up a profile and I canceled it. But after that window coming up a couple of times after it shut down, I decided to put in a new name and then I could not get to my old e-mails and it acted like a new set up. Then I really got frustrated. I was eventaully able to get back to my old e-mails and have been able t...

how do I change the table from which a form obtains data
I have a form that I have set up, but I would like to re-create it several times and change the table that each copy gets data from. EG Table 1 feeds Form A. I want to copy this form to create form B but it currently gets data from table 1, so I need to change it so that it now gets data from table 2. That's probably clear as mud but hopefully someone can help me! thanks Jo, If all the tables have exactly the same fields, you just need to change the Form's RecordSource. Copy the form to another name, and change the RecordSource table, or more usually, a query against the...

Payroll calculator template
What are the tax status numbers on the payroll calculator? Any suggestions on the set up of the calculator? ...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

SharePoint (WSS) 2007 / Outlook 2007 - Alert
This is a multi-part message in MIME format. ------=_NextPart_000_00AD_01C70E47.83B45950 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We have setup a SharePoint Server (WSS) 2007 and are using the alerting = functionality. After we changes stuff on WSS, an alert message is generated and sent to = an email account. This e-mail account is hosted on an Exchange 2003 server. When using Outlook 2007 to open the e-mail, we get: Cannot open this item. Wenn using OWA the e-mail can be opene fine. Does somehow have an explanation for th...

How do I convert a word table into an excel document?
I have managed to get the info accross no problem but the formatting is all over the place. For instance - 07/10 meaning July 2010 is appearing as 07/Oct despite me going into format cells custom then enter mm/yy which has always worked previously. Any ideas? You can't use it like that regardless of formatting, you need to put in the whole date or else Excel will always assume the current year so any real date used for calculations needs to be numeric and needs a day, so you can enter (assuming US date format) 07/01/10 and use a custom format of mm/yy or if you don't need it for...

Changing Item #'s
Is it possible to change the item #'s after the item has already been entered? TMM: No there isn't in the "base" product, but MBS does have a tool that you can use called Item Modifier - this will allow you to change an Item number from one value to another. You can email this address below and they can answer any questions you have about the tool and can tell you what the cost of it is. mbsprofessionalservices@microsoft.com Hope that helps you out, JG "TMM" wrote: > Is it possible to change the item #'s after the item has already been entered? ...

An Outlook 2003 Problem
I just upgraded from Outlook 97 to Outlook 2003. However, when trying to reply, forward, and compose with Outlook 2003, I got this error message: "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." The problem persisted. I restarted Outlook 2003, but it didn't help. Any suggestions? Thanks. How did you upgrade Outlook 2003 from Outlook 97? Anyway If you use Hotmail or MSN mail, check MSKB info below. You receive a "The messaging interface has returned an unknown error " error message when you try to use Outlook to...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

help with dynamic tables
This is a bit complicated to explain but I'll try my best. In columns A, B, C I have different drop down lists. Column A has Store1, Store2, Store3, etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB, ProductC. As of right now, these lists are not dependent on each other, I can choose anything from any list regardless of the previous category. Also, the length of these lists is undefined, meaning I will constantly be adding to them in sequential rows below. And then columns D and beyond have data such as Sales, Profits, # of items, etc. What I...

Deleted items folder in o2k3
The big boss refuses to empty or archive his deleted items, now holding some 25,000 messages and approaching 3 gigs in size for the deleted items folder alone. Does Outlook treat messages in the deleted items folder any differently than messages in other folders? Would it be better for him to never delete messages since he doesn't really want to delete them? It would be better to not delete them since Deleted Items... is for things you want to get rid of. "Wowbagger" <none> wrote in message news:uKyAPby2GHA.3656@TK2MSFTNGP04.phx.gbl... > The big boss refuses ...

Exchange Server 2000 Secuirty Setup problems
Dear I have apply wrong setting to our exchange server 2000 . The wrong setting as from exchange manager . mail store , I have apply everyone send as and receive as access right, the group included remote access via offline sync. This setting once apply , all user access other mailbox without apply access right. I have try disable this setting, after all local office user no problems, but a remote offline sync laptop user does not work . The error as below: Unable to display the folder. Microsoft Outlook could not access the specified folder location. The file C:\exchange\shawnb.ost cannot b...

Publishing Layout and tables
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I am trying to copy and paste text from one cell of a table to another cell in the same table. The document is in Publishing Layout. The paste command deletes the text in the destination cell and then places a big empty text box on top of the table. I do dozens of these documents that are primarily tables and graphics. Previously I used Publisher on my old PC. Should I go back, or can this be done in Word for the mac? Hello, On 2010.01.29 8:44 AM, in article 59bb1ce2.-1@webcrossing.JaKIaxP2ac0, "Toni_T@officefor...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

manufacturing scrap not calculating on pick list and serial #'s no
I have entered my percentage of scrap on a MFG BOM and is seems to be calculating properly in the setup. When I release the components and add them to a pick list the scrap is not calculating. I also choose serial numbers and they do not carry thorugh and print on the pick list. Is there some setting that I have missed? -- Paula ...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

Duplicate personal folders problem
Hello all, I have a problem with Outlook 2003 and I hope someone can help me resolve it I imported a PST file from Outlook XP that was on a different machine, and now I have two versions of Personal Folders in my All Mail Folders list. They both have Deleted Items, Drafts, Inbox, Junk E-mail, Outbox, Sent Items and Search Folders in. One of them has an icon of a piece of paper with a clock to the top left and a house to the top right. The other one has an icon like a stack of files (like from a filing cabinet) They are clearly referencing the same thing, as the Deleted Items both have al...

Interesting Solver problem (Solver encounters an error)
Here's an interesting problem, I wonder if anyone has any thoughts o this. Recognize that my real problem is very complex (severa intermediate calculation including some iterative steps), but th problem I'm having seems similar (conceptually anyway) to this simpl problem. Given a data set: x,y 10,3.9 8,3.2 7,2.8 6,2.2 5,1.4 4.5,0.8 4,0.01 3.8,-0.4 3.6,-1 3.5,-1.4 3.4,-1.8 3.3,-2.4 3.2,-3.2 3.1,-4.6 3.05,-6 One could look at the data and say, "that looks like the curve y=ln(x) but with a different asymptote other than the y-axis and possibly scaling factor." So we choos...

Excel Problem #3
I have win2000 with Office 2000 in the network! When i try to rename one excel file my computer make restart. This hapening not to all files,and all files there are in the same folder in the network! What hapen,and what i can do so as to work out the problem? I have all the required permissions on that network folder. No one else has any of the files open. They are not shared workbooks. ...

Excel pivot table #2
i encountered an error in my pivot table. i created an olap cube using the analysis manager. the cube displays the correct data of my measures but on my pivot report, it displays #N/A.... i need help to fix this one... thanks.... =) ...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...

Problems with MFC list controls
I have a large list control with many inventory control items displayed within. When someone single clicks on an item, it displays information about that item in another part of the dialog. I have a three-fold problem, the master list has multiple columns in it, in order to make the selection work properly, only column 0 can be selected, is there a way to make a valid row selection if you select an item using any of the other columns? Second issue is the highlight of the selection. Only column 0 in the list control will highlight, is there a way to make the entire row highlight when an...

Next button problems
I have a next button in a form that I have criteria for. I have a date field that has to be entered before going to another record. When I click the next button I get the pop up that says click update to continue but the form displays the next record instead of staying on the current record that needs updating. Here is the code that I am using. ____________________________________________________________________________________ Private Sub Command39_Click() If IsNull([Post Called Customer]) Then MsgBox "You must click the UPDATE button to continue!!!" Cancel = True End If On Erro...