Custom Formula in Pivot Table Row

[Cam]

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

[Tom Ogilvy]

No.

--
Regards,
Tom Ogilvy


"Cam" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Cam]

Hello Tom,

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


Regards,
Cam

[Tom Ogilvy]

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.

--
Regards,
Tom Ogilvy



"Cam" <[email protected]> wrote in message
news:[email protected]...
> Hello Tom,
>
> Do you have any other suggestions for getting an "instant' Income
> Statement out of a Pivot Table?
>
>
> Regards,
> Cam
>

[Cam]

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