Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Convert Positive Numbers To Negative

  1. #1
    Join Date
    8th January 2008
    Posts
    2

    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 at 01:17. Reason: title spelling

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    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

  3. #3
    Join Date
    8th January 2008
    Posts
    2

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    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.

  5. #5
    Join Date
    20th December 2007
    Posts
    24

    Re: Convert Postitive Numbers To Negative

    Quote 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

  6. #6
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,654

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: July 1st, 2008, 23:23
  2. Replies: 6
    Last Post: February 17th, 2008, 10:52
  3. Replies: 11
    Last Post: November 20th, 2007, 10:54
  4. Negative numbers to positive
    By wsim in forum EXCEL HELP
    Replies: 10
    Last Post: April 1st, 2006, 04:50
  5. Replies: 2
    Last Post: February 7th, 2006, 17:10

Bookmarks

Posting Permissions

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