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?
Gross or Absolute Grand totalsGrand totals
Posted 14 May 2016 - 08:36 PM
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.
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