Jump to content


Photo

Gross or Absolute Grand totals

Grand totals

  • Please log in to reply
2 replies to this topic

#1 winner49r

winner49r

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 14 May 2016 - 03:51 AM

I have inventory adjustments data with minus dollars and plus dollars.  Within my pivot table I have a grand total but what I need is another column with an absolute or gross dollar adjustment.  This sum would ignore negative values and would give me a Gross sum grand total treating all values as positive values so I can see total dollars adjusted plus or minus.  Is that possible?



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 14 May 2016 - 08:36 PM

Hi winner49r,

 

The ABS function will convert any values into positive values. For example, =ABS(-10) will return 10 and =ABS(5) will return 5.

 

I'm sure you'll have no difficulty using this function, but for the benefit of anyone who's new to Excel functions there's a video lesson explaining how to use them in: Essential Skills Lesson 2-13 Create functions using Formula AutoComplete.

 

The best approach is probably to add a new column to your pivot table's source data with the ABS formula and then use the new column in your pivot table as necessary. If you're not able to edit your pivot table's source data, you could also use a Calculated Field, as shown in the video lesson: Expert Skills Lesson 5-17 Add a calculated field to a pivot table.

 

I hope this offers a solution to the problem, but please feel free to reply if you need any more help with this. If you'd like, you can attach an example workbook by following the instructions here.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 winner49r

winner49r

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 11 June 2016 - 08:52 PM

Added a column with the ABS formula, used it in my pivot table and it works perfectly.  Thank You







Also tagged with one or more of these keywords: Grand totals

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users