Pivot table
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Pivot table

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I would like to modify the SUM formula in my pivot table to round to the dollar and say -SUM since my souce data is all the opposite sign of how I want it to appear on my pivot table. Is the only way to accomplish this with a calculated field or is there some way to define exactly the SUM formula I want to use in the table?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could add a column to the original data using the absolute formula this will create all positive numbers than use that coulmn in your pivot

    =Abs(a5)

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Using the data...
    ******>
    FruitQty
    Apples$10.10
    Apples$15.25
    Oranges$20.50
    Oranges$25.80
    Oranges$30.00


    ...a Calculated field using the formula...

    =-ROUND(Qty,0)

    ...produced...

    ******>
    Data
    FruitSum of QtySum of -Qty
    Apples25.35-25
    Oranges76.3-76
    Grand Total101.65-102




    [ This Message was edited by: Mark W. on 2002-04-09 14:24 ]

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, maybe I am missing something but wouldnt it be easier to double click the data field in layout, then number and then to 0 decimal places. Or did you want both numbers??

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 14:29, EdE wrote:
    Ok, maybe I am missing something but wouldnt it be easier to double click the data field in layout, then number and then to 0 decimal places. Or did you want both numbers??
    Formatting alone doesn't alter the precision of underlying values.

    Also, a Calculated Field (in this case =ROUND(QTY,0)-QTY) allows you to produce PivotTables with derived results...

    ******>
    Sum of Rounded Diff
    FruitTotal
    Apples($0.35)
    Oranges($0.30)
    Grand Total$0.35



    [ This Message was edited by: Mark W. on 2002-04-09 15:23 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the info, but it wasn't quite what I was looking for. Here is a sample of my data set

    Acct Dept Amount
    3000 SiteA -5000
    3005 SiteB -3500
    3010 Admin -50
    3300 Admin -25
    4000 SiteA 2000
    4005 SiteB 1000
    4500 SiteA 700
    4505 SiteB 300
    4600 SiteA 200
    4605 SiteB 75
    4750 SiteA 45
    5000 Admin 1000

    What I'm looking for is to have rows for the Acct's, columns for the Dept's and then to have the amounts rounded to zero and reverse the sign. I hope this helps.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-10 08:52, 2077delta wrote:
    Thanks for the info, but it wasn't quite what I was looking for. Here is a sample of my data set

    Acct Dept Amount
    3000 SiteA -5000
    3005 SiteB -3500
    3010 Admin -50
    3300 Admin -25
    4000 SiteA 2000
    4005 SiteB 1000
    4500 SiteA 700
    4505 SiteB 300
    4600 SiteA 200
    4605 SiteB 75
    4750 SiteA 45
    5000 Admin 1000

    What I'm looking for is to have rows for the Acct's, columns for the Dept's and then to have the amounts rounded to zero and reverse the sign. I hope this helps.
    What do you mean by "have the amounts rounded to zero..."?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I must apologize, my sample data was all rounded to zero decimals, but the actual data is all in dollar and cents. I would like to round all the dollar and cents to just whole dollars.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-10 09:29, 2077delta wrote:
    I must apologize, my sample data was all rounded to zero decimals, but the actual data is all in dollar and cents. I would like to round all the dollar and cents to just whole dollars.
    Okay, this PivotTable...

    ******>
    Sum of Amount2Dept
    AcctAdminSiteASiteBGrand Total
    3000 5000 5000
    3005 35003500
    301050 50
    330025 25
    4000 -2000 -2000
    4005 -1000-1000
    4500 -700 -700
    4505 -300-300
    4600 -200 -200
    4605 -75-75
    4750 -45 -45
    5000-1000 -1000
    Grand Total-925205521253255



    ...uses a Calculated Field, 'Amount2', defined as...

    =-ROUND(Amount,0)

    [ This Message was edited by: Mark W. on 2002-04-10 10:04 ]

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thank you. Your answer is exactly what I was looking for. One of those "it was so simple I didn't see" it type of things.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com