+ Reply to Thread
Results 1 to 6 of 6

Pivot table not calculating negative numbers properly + not sorting months accurately

  1. #1
    Registered User
    Join Date
    08-10-2019
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    3

    Pivot table not calculating negative numbers properly + not sorting months accurately

    I have data with months, years and profits that need to be summed per month and year and sorted from January to December per year. I have year and month in the rows and profits in the value field. I am experiencing 2 problems.
    1. Excel is not calculating the negative numbers as negative and instead is turning them into positive numbers. In the data sheet there are positive and negative numbers. If I sum these numbers manually using the autosum function, excel calculates everything correctly. But once I use the data for the pivot table Excel changes the negative numbers to positive, which causes my total profits per month to be inaccurate. I have the months and years in the rows and profit in the value field. In the settings I formatted the field to number in settings, hoping this would solve my problem, but it didn't. Formatting it to accounting didn't help either. All profits are still positive, even though the data sheet shows negative profits. So for example for January I have 1, -2, 6 dollar profit which would be 5 dollar in total for January, but Excel gives me a result of 9 dollar. Why is excel doing this and how can I fix it?
    2. Excel is not sorting the months correctly. I am not sure based on what it's sorting because per year it has April at the top and October at the bottom. It's not alphabetically sorted either, because after April comes September, very odd. I looked through the sort options to see if I could get it to sort from Jan-Dec, but I could only find an alphabetical sort option. How can I get excel to sort the data from Jan-Dec per year?

  2. #2
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    R.D.C.
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    7,397

    Re: Pivot table not calculating negative numbers properly + not sorting months accurately

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    R.D.C.
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    7,397

    Re: Pivot table not calculating negative numbers properly + not sorting months accurately

    Hi and welcome
    although we value privacy as much as anyone else, it could be important that members have a rough idea of your location. You might in the future post questions which are bound to your regional settings.
    So, please update your profile to something more precise then "Home" ( country will suffice, no need to be more precise).
    Thank you for helping us to help you

  4. #4
    Registered User
    Join Date
    08-10-2019
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    3

    Re: Pivot table not calculating negative numbers properly + not sorting months accurately

    Thank you for your response. I will upload a sample workbook tonight.

  5. #5
    Registered User
    Join Date
    08-10-2019
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    3

    Re: Pivot table not calculating negative numbers properly + not sorting months accurately

    I managed to solve the problems myself. For anyone who might be interested, I deleted, re-entered and re-formatted the original profit column and now the pivot table gives me the correct monthly profits. Apparently there was something with the original column, although I don't know exactly what because I entered and formatted everything the same way. As for the sorting of the months, I was able to manually drag the months in the correct order using the drag sort option.

  6. #6
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    R.D.C.
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    7,397

    Re: Pivot table not calculating negative numbers properly + not sorting months accurately

    Thanks for the update.
    It appears that you had text looking like numbers instead of real numbers then? Formatting will not affect underlying values only what XL shows you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1