Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using Excel 2010, Pivot Table feature.

I would like to sum up the range of agent total sales. The problem is some of the sales person has the credit note. It means it shows negative in the figure. I would like the sum positive and negative number regardless their sign.

I manage to do it by manual enter {=SUM(ABS(B96:B102))} As the image show below.

enter image description here

However, My Pivot table is filter by monthly and product. Different filtering will have different row records. If I hard code the formula, it is not flexible as well. How can I make it flexible? I did try with GetPivotData, but it does not support by range.

Anyone can help?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Well, it depends. The formula you've entered in the question can be achieved by using a Calculated Field. Just define a new Calculated Field for your pivot table with the formula =ABS(Sales Amount) and display that instead of Sum of Sales Amount.

However, if your raw data contains both positive and negative values for a specific sales agent you need to take a different approach. The above Calculated Field operates on the already summed raw data.

If we for example have the following raw data:

Name Value
A     3
A    -2
B     4
B    -5

Then we create a pivot table and add a Calculated Field AbsValue with the formula =ABS(Value) we get the following result

  Sum of Value Sum of AbsValue
A  1           1
B -1           1

To get a summation of all the absolute values from the raw data, you need to add a new column to the raw data and perform the ABS function there:

Name Value AbsValue
A     3    3
A    -2    2
B     4    4
B    -5    5

  Sum of Value Sum of AbsValue
A  1           5
B -1           9
share|improve this answer
    
This is the alternative I did just now, I found out no way can direct access the pivot table range. –  Shiro Apr 30 '12 at 10:04

Under the field settings for the row label you're using, go to the second tab and click the option that's something like show field for no data. Then, the row labels will stay static and if there is no data associated with it it will just be blank, but your formulas will work.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.