Results 1 to 12 of 12
  1. #1
    Lounger
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    Lounger
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Lounger
    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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")

  7. #7
    Lounger
    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  9. #9
    Lounger
    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Pivot Calc Field (Excel 2003)

    Perhaps someone else can help. I can't, sorry.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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

  12. #12
    Lounger
    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

Posting Permissions

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