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
4783 Views

Similar Articles

[PageSpeed] 4

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:

problems came up in the following areas during load
I get this message when i copy either text or a graphic from the internet (using IE)and attempt to paste it into MS Publisher. The error dialog box says "Problems During Load"...this box normally apprears 20-30 seconds after I click paste. Once I hit OK, the text or graphic will appear. It never did this before. Any suggestions? CPG Co wrote: > I get this message when i copy either text or a graphic from the internet > (using IE)and attempt to paste it into MS Publisher. The error dialog box > says "Problems During Load"...this box normally apprears 20-...

Three different fields in one record referencing to three different records in another table
Hi. I have TableA and TableB TableA has 3 fields where types of file extensions (for different purpose) are supposed to be stored. Table B has many extension type strings for these TableA three fields (.tif, .jpg, .pdf, .gif, etc). 3 combo boxes refer to Table B to get and store TableB keys in TableA appropriated fields. When I set up table retaionship, Acces 'creates' also TableB_1 and TableB_2 and show a diagram where two other similar to tabelB tables show up. So long so good. I can create records in TableA having chosen, for each different file purpose, the respective extens...

Printing problem when selecting any paper type other than plain pa
I have MSPub 2002 with XP HE and use an HP Inkjet printer cp1700. When I make my brochures I usually put a border around the edge a fill with some color. If I print one page, I have no problem, but if I ask the software to print numerous copies (anything more than 1 such as 2,3,4,etc) when it prints the second, third, forth etc copy, I always get a series of 3 lines after the top border. If I click the printer icon numerous times I do not get this problem or if I print the document in "paper type" plain paper, I do not get the problem. Does anyone ever experience this problem...

E-mail Problem
I have an e-mail server running Windows NT4 SP6a and Exchange 5.5 sp3. When a user of mines attempts to send to one agent out side of the company we receive the below error message. What could be going on. Thanks Reason: Fatal error sending message: No legal recipients Sending message to mdeaton@custard.com -----------------First 8192 bytes of------------------- ------- Unsent message enclosed after this line ------- Received: by exchange.sgic.com from localhost (mail daemon,slmail V5.1); Wed, 18 Aug 2004 13:10:11 -0500 Received: from exchange.sgic.com [10.10.10.24] by exchange.sgic.co...

Vlookup problems
I have Vlookups set up for a particular Spreadsheet. Each month I just drop the new data into the first column. Even though the number is formatted just like the previous month (i.e general, text, whatever) my vlookup formula shows #N/A. If i double click in the data cell and hit enter, the vlookup formula works. can you tell me why, or if there is a better way than clicking and hitting enter? Thanks! do you have your automatic calculation turned on? Under tools<options<calculation. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip...

Recover Deleted Items in Outlook 2000
I recently had a user that lost all items in the "Deleted Items" folder and needs to rocover them. I am using Outlook 2000 and wouls appreciate any information on how or where to view these items. Do you use Exchange server? If so, and it's configured for deleted item retention, you can get the items back if they were deleted within that time frame (however long retention is set for). If you don't use Exchange server, or even if you do and you use PST files for delivery (ick), you may be out of luck - but see http://www.outlook-tips.net/howto/recover_deleted.htm for one pos...

16-bit Migration
Hi, Im tasked with converting a 16-bit MFC app to 32-bit. Ive used the MFC migration kit as a starter, however this has proved to be pretty useless :-) Bearing in mind Ive not used MFC before in any shape or form, im struggling with a file called CChildDlg.cpp. The header appears to be a stock Microsoft header, but I cant decide if the CPP has been custom written. It loads a dialog resource, then parses it and creates the window and all the controls. But its parsing by ripping raw bytes out of the resource via an incremented BYTE* and not by using DLGITEMTEMPLATE* etc. Since it seems a comm...

'Item could not be found" and auto hypen between phone no's
1.Cannot send email with attachments to a distribution list of names because error message pops up.."The item could not be found." Note: works fine if sending to a single contact recepient. 2. In Outlook program of Office 2000 used to be able to type in 14556789 and it would automatically put in the hypens as 1-455-6789. Why not now in Outlook 2002? ...

Problem with datasheet & 64-bit installations
Once you have installed a 64-bit installation of Office, SharePoint datasheet views can't be used any longer. It's related to the ActiveX component only being compatible for 32-bit systems and not for 64-bit installations. Please refer to http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/9eb920ba-1a02-4f0c-ae08-6e808e0269c4/?prof=required for a complete thread, I wanted to share this here because you Office guys might have some more detail on this (we hope). Should be fixed on short notice if you ask me, it's blocking me from rolling out...

Problem with "Edit in Microsoft Office Project"
We have SharePoint 2007 implemented, and I am using MSO Project 2007. I have a document library in which I store a number of project documents, including the schedule. When I go to this document library and select "Edit in Microsoft Office Project", the file opens, but it always opens in Read Only mode. The only way I can open the file and have it editable is to check it out first, then select "Edit in Microsoft Office Project". And no, the document library is not configured to require check out. Any idea why this is? ...

Problem with List Control Box
I have a list control with 3 columns. For some reason when I try to insert an entry into the list control, sometimes, only the item's first value gets added, but the values for the 2nd and 3rd column are not there. If i continue to add the same entry consistently, eventually the entry will be added correctly with all three values. Why is that? For example: sometimes I will add (a,b,c) into my List control box, but only (a) gets shown. But if i keep inserting an item, eventually the entry (a,b,c) will be added... >I have a list control with 3 columns. For some reason when I try ...

Outlook 2003 rules problem on SBS 2003 server
About a month ago I lost the ability to create any new rules from client PCs running Outlook 2003, where a name from the global address list is being used. All the existing rules with GAL addresses work fine, and I am not getting any error messages, they simply do not work. All the computers are up to date, including the server. All the rules are of this variety: When message arrives from XYZ, file in folder ABC. Where the person who is XYZ is in the global address list. I am running the IMF filter. I did find the foillowing article which outlines my probem, however the solution does no...

GP should allow you to create a return for drop ship items!
What is the reason behind not being able to create a return for drop ship items? Do you think customer who receive their product via drop ship never have to return an item? This is really short sided on your part. 95% of our business is drop ship and we have return all the time. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsread...

Problem in Drawing Text with different Fonts & Charset
Dear all, I'm working in a program which will draw some string with both chinese and English character in the Window. I try to use CreateFont() to create a HFONT with Fonts "Arial" and CharSet "CHINESEBIG5_CHARSET" and then select the HFONT to the current DC. However the text drawing out isn't in Font "Arial" (It seems to be "Times New Roman" instead). After that, I try to use other available fonts on my platform, but the same outpur again. No matter what font I'm using, it give the same output!!! Does anyone know Why and how to fix it? ...

Import / DL problems
When I download my bank transactions, Money doesn't seem to grab it anymore. I used to see the pop up box that tells me I've received a file, and prompts me to place it in a certain acct. but now it just downloads it, and it doesn't import the transactions.(to any of my accounts). And it's not saving it anywhere on disk either, I did a search for any ofx file and returned nothing. What might it be? By the way, I've recently archived and removed all transactions from 2002 and 2003 from my working money file. Might this have caused some minor setting changes? Plz help?...

Excel formula problem SUMPRODUCT
Hi Guys, I have a problem with a formula adding up rows against 2 parameters. I am trying to add up rows on a report that 1) show that the actio against it is "open" and its target completion date is overdue. I have tried the following formula but it will not work. =SUMPRODUCT(--(H5:H47="OPEN")*(G5:G47="<TODAY())) This formula works if I substitute the date part with another tex parameter but will have nothing to do with dates even if I put in specific date that I know matches one from the column, I still get 0 Any ideas. Thanks in advance And -- Message ...

Item 'Last Ordered' Field updates inappropriately
The 'Last Ordered' field updates when I transfer inventory out. This is not good. If I want to know the last time I bought an item, the date of the most recent transfer does not help - especially if it was an inter-store transfer out. Ideally, separate fields for 'Last Transferred In' and Last Transferred Out' could be added as separate fields. This would reserve the 'Last Ordered' field for the last time the item was actually ordered from a supplier. Alternately, and better thant the present system, the field could simply update with purchase orders ONLY...

Problem importing data from text file
Hi, I have a comma delimited file that has twenty column names followed by six rows of data. I am trying to import this into Excel. When I do this, the data imports entirely into row one. It does import into difference cells but there is one row with 120 cells of data. I was expecting it two "Wrap" and start importing the data into cell A2. Can anyone help with this? How do I show the break between column headers and data? Thanks, Chuck ...

Refresh problem for the size grip with Windows XP style #2
Hi, I have a dialog application with a scrollbar size grip. On Windows XP with the visual style, there is a refresh problem for the size grip region when I move the application out of the screen; only if I go to the bottom of the screen. It's OK if I go out by the right side. Without the XP style, everything is OK. I have noticed the same behavior with Notepad. Do you know how could I solve this problem? Any ideas will be appreciated, Thanks It's a known problem in XP (non-client area is erased incorrectly between scrollbars). Will it ever get fixed? I would not bet on it. &qu...

Chart item reports with OUTLOOK via Excel or ?
I want to create reports for OUTLOOK items (particularly tasks). I'd like to be able to see charts that display things like the amount of time spent on tasks for various clients, the type of work done, and the dates (including time of day). I'd like to be able to look at these reports daily if possible. Is there a way that I can do this? I thought that there must be a way by exporting the items into excel then manually setting up charts but this would be time consuming to do each time. Any help is appreciated (I've spent days trying to figure this out) ...

Data Validation
What I am trying to achieve is a drop down list that would look something like this: D1a - suipplies F1g - cell phones C2a - travel E4b - computers and so on and so forth. I can do that. What I would like to happen though is in the same cell that the drop down box is in (a29) I would like to select one of those options and only have D1a, or F1f, ect show up in the cell. The words are just references to my budget, and I don't nned them after I selected the right one. Is this possible? I am somewhat new to excel and have never went into the visual basic editor and I don't have...

Pasting problems
I am using excel to link with another application (from which I am taking some data). The time to get this data can vary. If it finds the data in the time I have allowed for it, it will paste this perfectly into excel. If it does not find the data in time, I assume that it is trying to paste something which is not there. I get a runtime 1004 error Paste method of worksheet class failed. Is there anyway around this. Can I write a loop procedure to only try to paste once there is something in the clipboard. Any help would be much appreciated. Thanks Chip Pearson has some nice notes at:...

create new mail item from current item
Greetings, How can I create a new mail item from the active inspector current item? Thank you, Please provide more details on what you want to do. In other words, what does the current item have to do with the new item you want to create? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators http://www.outlookcode.com/article.aspx?id=54 "lindstrom" wrote: > Greetings, > How can I create a new mail item from the active inspector current item? > Thank you, > > --- > fr...

Deleted items
I'd like for my deleted items to be listed in date order when deleted rather than the date they were originally received. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=d66c023f-3eed-461f-bfa3-01769e...

Intermitent file problem?
Running Outlook 2003 and from time to time the frame with the Inbox list of emails and viewing pane will be blank with an error message stating the system is busy and that perhaps something else has it tied up. How can I debug and fix? ...