Calculate average sales without cancellations

Hello,

I want to calculate the average of sales without the cancellations of the transaction in a Pivot. Example:

Type Client Sales
DEB A 100
DEB B 50
DEB C 100
DEB D 200
CRE B -50
CRE C -100

DEB(Debit)
CRE(Credit)

When I create the Pivot and make average of Sales it will give me 50 (300 divided by 6 lines), however it should give me 150 (300 divided by 2 real, not cancelled sales = clients A and D).

Is there an easy workaround? I have the possibility to use invoice numbers for each transaction in case I need to match DEB and CRE (each CRE has the same reference as the DEB). Hope my issue is clear?

Cheers
Chris

Here. Use this helper column and reference to it.

H8=g8/(countif(d7:d12,">0")-countif(d7:d12,"<0"))

Please Login or Register  to view this content.