Announcement

Collapse
No announcement yet.

Convert Positive Numbers To Negative

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert Positive Numbers To Negative

    I have created a Pivot table from a source data file that includes a document status column ('Type I Or C') (I for Invoice & C for Credit) and an Invoice / Credit value column (Invoiced Price). However the values for both invoices and credits are positive so when summed distort the total values.

    I am attempting to create an additional column within the Pivot table to convert the credit note values to negative by inserting a calculated field under the formula tab, i.e.

    =IF('Type I Or C'="C",-'Invoiced Price','Invoiced Price')

    The above formula works fine in a standard worksheet but it is not reckonsing the "C" within the pivot table. If I manually change the value to numeric (i.e. 1 for Invoices and 2 for credits) it works fine, i.e =IF('Type I Or C'="2",-'Invoiced Price','Invoiced Price')

    so it appears to be an alpha issue??
    Last edited by shg; January 9th, 2008, 02:17. Reason: title spelling

  • #2
    Re: Pivot Table Formulas

    Simply make the appropriate one negative in the PivotTable source. Enter -1 into any cell, select the numbers and use Edit>Paste Special - Values - Multiply

    Comment


    • #3
      Re: Convert Postitive Numbers To Negative

      I was trying to avoid having to amend the source file. The source file is generated by an ERP system - i.e. auto generated excel file of sales info, and the pivot table summarises the data. There could be thousands of lines in the source file, so this is why I was trying to automate by selecting out all records with a type C and then converting the value column to negative in these rows.

      Could you throw any light on why it is that when I change the invoice / credit status to a 1 or 2 from an I or C the formula works in the pivot table?

      Comment


      • #4
        Re: Convert Positive Numbers To Negative

        Using the paste special method takes only 10 seconds and will take you far less time that implementing work-arounds. You should always address problems at the "source" and not cater to them.

        Comment


        • #5
          Re: Convert Postitive Numbers To Negative

          Originally posted by Markw
          Could you throw any light on why it is that when I change the invoice / credit status to a 1 or 2 from an I or C the formula works in the pivot table?
          if you want to post some data then maybe we could comment.
          2 + 2 = 9, My Arithmetic Is Mental

          Comment


          • #6
            Re: Convert Positive Numbers To Negative

            A way could be, from your original DataBase to add an extra column:
            New Type with a formula to change Type I Or C to -1 or 1: =IF(B2="I",-1,1)
            Then in the Pivot Table add the calculated Field: New_Price ='Invoiced Price' *'New Type'
            Attached Files
            Triumph without peril brings no glory: Just try

            Comment

            Working...
            X