Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Location
    Carson, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

    Need a better answer than I've been using for quaterly data summary effort. Had been using three pivot tables to calculate YTD spend by employee and business unit, MWBE spend by empoyee and business unit as a percent of YTD spend, and MBE spend by empoyee and business unit as a percent of YTD spend. Used to manually combine into another worksheet but there must be a better way; want this to be automatic. Sample data and desired result is attached. Pink columns are the problem calculations for me. Any suggestions?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

    Does this work for you? I have added two calculated fields.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Location
    Carson, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

    It is what I want but I'm not sure how you got the ratios to show up. Those fields aren't in the data tab. Can't pull them from the desired result tab because that was generated the way I don't want to keep doing.

    How did you get the ratios to show?

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

    Re: Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

    Click somewhere in the pivot table.
    Click on Pivot Table in the Pivot Table toolbar, and select Formulas | Calculated Field...
    Select MWBE Ratio in the Name dropdown list.
    The Formula box will display the formula for MWBE Ratio.
    Similar for MBE Ratio.

  5. #5
    New Lounger
    Join Date
    Apr 2003
    Location
    Carson, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

    Figured out how to create the calculated fields. Last portion of the question is how to get the pivot table to display in a more horizontal manner as in the solution spreadsheet. Probably simple but I don't know how. Anyone know?

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

    Re: Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

    If the various summary fields are displayed below each other in your version, the gray Data button is probably immediately to the right of the Business Unit button. Drag it to the right, and drop it on the cell that contains Total. The summary fields should now be displayed next to each other.

  7. #7
    New Lounger
    Join Date
    Apr 2003
    Location
    Carson, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

    Thanks!!! That handled the problem.

Posting Permissions

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