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 want to calculate average profit/loss in Excel pivot table. Say the source data is in a Excel table and it looks like

Date   Net income
1/1    5
1/1    -3
1/1    7
1/2    -10
1/2    4

My solution is to add 2 extra columns with formula: IF([@Net income]>=0,[@Net income],0) and IF([@Net income]<0,[@Net income],0)

Date  Net income   Profit    Loss
1/1   5            5         0
1/1   -3           0         -3
1/1   7            7         0
1/2   -10          0         -10
1/2   4            4         0

Then in the derived pivot table, I can add 2 columsn with average value of column "Profit" and "Loss" from the source table.

Pivot table    Avg Profit     Avg Loss
1/1            4 (=12/3)      -1 (=-1/3)
1/2            2 (=4/2)       -5 (=-10/2)

I was wondering if there's a better way, so I don't need to add 2 extra columns "Profit" and "Loss". Is it possible to use calculated fields/items in this case?

share|improve this question
    
Do you want both the average profit and average loss, or just average net income? –  Raystafarian Jul 13 '13 at 11:28
    
Average profit and average loss. –  Stan Jul 14 '13 at 13:57
    
Are you open to a VBA solution? –  Raystafarian Jul 15 '13 at 9:59
    
Sure. VBA is fine. –  Stan Jul 16 '13 at 10:35

1 Answer 1

Unfortunately, the calculated fields option for pivot tables will only deal with the entirety of the data; what I mean is you can't filter data based on value after you've made the pivot table. So averageif and if functions must deal with the field rather than the cells, otherwise this would be possible with a calculated field.

You could cut that 2 columns to one with a flag column formula C2=if(B2< 0, "Loss", "Gain") and then use that as a filter on the pivot table.

Or you could set up that same flag column, and use it as a slicer on the pivot table. However, in both of these cases, you won't see both gains and losses at the same time.

Unfortunately, I don't think excel can do that work for you at this time with a function, you will need the extra columns, but you could always hide them.

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.