# Excel 2010 Pivot table to calculate average profit/ loss

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?

-
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

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.