Custom Formula in Pivot Table Row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cam - 26 Jan 2006 12:48 GMT

Hello all,

I use Pivot Tables (Excel 2003) to create Profit & Loss statements.
I would like to insert a custom formula to calculate 1) Gross Profit
and 2) Gross Profit %.

My pivot tables look something like this:

Acc Type Account Budget Actual Variance
Sales 5001 | 10000 12000 2000
5002 | 5000 4000 -1000
5003 | 2000 2500 500
-------------------------------------------------
Sales Total 17000 18500 1500
-------------------------------------------------
COS 6001 | 8000 8500 500
6002 | 2000 1500 -500
6003 | 500 1200 700
-------------------------------------------------
COS Total 10500 11200 700
-------------------------------------------------
Grand Total 27500 29700 2200

What I want to do is to rename the "Grand Total" row to "Gross
Profit".
Then, I want this row to subtract COS Total from Sales Total (for each
column).
I don't want to use a formula row outside of the pivot-table.

Is this possible?

Cam

Reply to this Message

Tom Ogilvy - 26 Jan 2006 13:24 GMT

No.

Signature

Regards,
Tom Ogilvy

> Hello all,
>
[quoted text clipped - 28 lines]
>
> Cam

Reply to this Message

Cam - 26 Jan 2006 14:36 GMT

Hello Tom,

Do you have any other suggestions for getting an "instant' Income
Statement out of a Pivot Table?

Regards,
Cam

Reply to this Message

Tom Ogilvy - 26 Jan 2006 14:42 GMT

I usually use the pivot table to to the lion share of the work, then copy it
to another sheet, do an edit copy, then edit pastespecial values to make it
not a pivot table. Then dress it up to get the final product. This is for
static reports. You implied that you don't want anything but the pivot
table, so I have no suggestions for that unless you can add columns to your
original data to give you what you want, but it didn't sound like it to me.
I will admit, that I have had little use or understanding of the Percentage
options in pivot tables.

Maybe you can find something useful at Debra Dalgleish's site.

Debra Dalgleish
http://www.contextures.com/tiptech.html

Look under P for Pivot Tables. She is releasing a book on Pivot Tables, so
you might find some useful information there.

Signature

Regards,
Tom Ogilvy

> Hello Tom,
>
[quoted text clipped - 3 lines]
> Regards,
> Cam

Reply to this Message

Cam - 27 Jan 2006 09:10 GMT

Thanks for your reply Tom.
I looked at the site you mentioned (which is quite good), but it
doesn't provide any solution for my problem.

Currently, the "copy and paste values" solution you mentioned is
exactly what I am doing.
However, the number of reports I'm creating and the speed at which they
are required mean that this solution is taking too long.

Thanks anyway for your help!

Regards,

Cameron

Reply to this Message