Results 1 to 12 of 12

20040603, 16:38 #1
 Join Date
 Jul 2003
 Location
 Notre Dame, Indiana, USA
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel Pivot Calc Field (Excel 2003)
Attaching pivot with underlying data. Need to make calculation on "R_INCHES" total in CLASSIFIED area of pivot. Is it possible to use a calculated field or item on ONLY this portion of the data? When I've tried, I get a calculation in all of the other areas as well.
The calculation I need is to multiply the Classified R_Inches total by .6.
Any help will be greatly appreciated. See attachment.

20040603, 16:47 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Excel Pivot Calc Field (Excel 2003)
Umm  your attachment didn't make it (yet). If it's too large (there is a 100 KB limit for attachments), zip it and attach the zip file.

20040603, 18:13 #3
 Join Date
 Jul 2003
 Location
 Notre Dame, Indiana, USA
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Pivot Calc Field (Excel 2003)
Sorry about that. I didn't realize how large my file was. I hope you can help.
Thanks,
Sherry

20040603, 18:47 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Pivot Calc Field (Excel 2003)
You still did not attach a file.
Steve

20040603, 18:55 #5
 Join Date
 Jul 2003
 Location
 Notre Dame, Indiana, USA
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Pivot Calc Field (Excel 2003)
Having a great deal of difficulty making the file small enough to attach. I think I'll have to really cut it down but will try again.

20040603, 20:35 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Excel Pivot Calc Field (Excel 2003)
Your spreadsheet contains a pivot table without the data it is based on, so we cannot do anything with it. I repeat, to reduce a large file in size, create a zip file and attach that.
This formula will retrieve the sum for "CLASSIFIED":
=GETPIVOTDATA("SUM OF R_INCHES",A4,"CATEGORY","CLASSIFIED")

20040604, 17:15 #7
 Join Date
 Jul 2003
 Location
 Notre Dame, Indiana, USA
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Pivot Calc Field (Excel 2003)
Thanks for your patience. The underlying database I was working with was HUGE. I finally pared it down to a manageble size, so you should find it attached.
Thanks again,
Sherry

20040604, 17:19 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Excel Pivot Calc Field (Excel 2003)
You have lost me completely. You have attached another Excel spreadsheet with a pivot table without the data it is based on. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20040604, 18:24 #9
 Join Date
 Jul 2003
 Location
 Notre Dame, Indiana, USA
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Pivot Calc Field (Excel 2003)
I probably should send you a bottle of aspirin. I'll try one more time. I wonder if part of the problem (other than I'm a doofus) is that the underlying data is from an Access query.
Anyway, if it doesn't come across this time, I'm not sure what else to try.
There are two pivots on one sheet. The one that I want to put a calculated field or calculated item in is the lower one. I want the total for Edition 01 to be multiplied by .6. But I want the total for Edition 21 to remain the same.
I don't know if it matters but the reason I want to do this is that the pages in our newspaper have 6 columns on the retail pages, but there are 10 columns on the classified pages. In order to report the actual advertising lineage, I need to convert classified inches to retail measure, thus the need to multiply classified inches by .6.
If the attachment doesn't come over this time, I will just give up for the week end. I appreciate your patience and your time, and I apologize for my ineptitude.
Sherry

20040604, 18:32 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Excel Pivot Calc Field (Excel 2003)
Perhaps someone else can help. I can't, sorry.

20040604, 18:44 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Pivot Calc Field (Excel 2003)
The attachment is here. The data is not. If the data is external data (you mentioned ACCESS), you will have to provide the sample of the access table for us to work with.
We can not do anything to the pivot table and refresh it since you haven't supplied the source data for the pivot table. The source seems to be:
Cocuments and SettingssstuckDesktopLineage ExpLineage.mdb
Steve

20040604, 18:47 #12
 Join Date
 Jul 2003
 Location
 Notre Dame, Indiana, USA
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Pivot Calc Field (Excel 2003)
OK. Apparently I will have to try something else. I will try creating the pivot from an export of the Access query instead of using it as an external data source.
I am sorry for all the trouble. If I knew what I was doing we wouldn't be having these conversations, now would we?
srs