MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Subtracting in a Pivot Table


Posted by Chris II on April 11, 2001 9:00 AM

I have a worksheet that queries a database file of financial information. The fields relevent to this question are the Obligations and TransactionType.

When there is an expenditure it is totaled under the Obligations and is represented by an L in TransactionType. When there is a credit it is totaled under the Obligations and is represented by an M in TransactionType. Both are positive values which is causing me problems in getting the total expeditures which would be Obligations of TransactionType L - Obligations of TransactionType M.

What I need to have is the TransactionType as a Column Heading in the Pivot Table and a column that is the difference or just a Total column that is the difference.


Posted by Mark W. on April 11, 2001 12:19 PM

Chris, would it be possible to define an additional
column in your database as:

=IF('TransactionType'="L",-1,1)*'Obligations'

Posted by Chris II on April 11, 2001 12:35 PM

If at all possible I would like to avoid doing this because there are over 15000 entries so adding columns greatly increases the size of the file, but that is what I am currenly doing.