7 Replies Latest reply: Oct 30, 2012 3:00 AM by Jonathan Brough RSS

    Reversing Sign visually Only and only on certain rows.

      Hi There,

       

      Ok the problem I have.

       

      I'm using a pivot table that contains positive and negative accounting journal entries.

       

      When I add up the amounts I get the correct amount:

       

      INCOME: 100

                     (50)

      Total Inc:  50

      COSTS:  (200)

                     45

      Total Costs: -165

       

      TOTAL -125

       

       

      The pivot table totals are correct.

       

      I would however like to reverse the costs so that they display as positive when negative and vice versa but still remain negative in the background.

       

      Can I set the format of the column at a row level?

       

      i.e.

       

      If Income: Don't do anything. or set the format to be #,##0.00;(#,##0.00)

       

      If Row = Costs Then Display (200) as 200 and display 45 as (45)

      e.g.

      (#,##0.00);#,##0.00

       

      Any one think of a way of doing this so the pivot chart still calculates correctly but displays COSTS as a positive value?

       

      I tried doing it in the textformat fields of the expression but didn't work.

       

      Thanks