The UK's Number 1 for Microsoft Office Training

Sitemap

Bookmark page

Printable version

Course updates
due to adverse
weather conditions

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pivot Tables and Calculated Fields

	98.3 % recommend us
	More live stats...

Bookmark this page

Share this with a friend or colleague:

The UK's most regular
instructor-led training courses.

Training information:

Pivot Tables and Calculated Fields

Resolved · Urgent Priority · Version 2003

ReplyThu 26 Nov 2009, 14:17Delegate Paul said...

Pivot Tables and Calculated Fields

Hi

I have three colums of data in an excel sheet

A TOTAL Revenue
B % of revenue apportioned to Business Line 1
C % of revenue apportioned to Business Line 2

I want to create a pivot table report that evaluates the total revenue due to BL1 and BL2 based on the data in Columns A,B and C. I have tried making a calculated field but that keeps coming up with the wrong vales, from what I can see on the internet that is because the formula A*B or A*C is applied to the SUM of columns A and B, not the individual entries. Is there a workaround please?

This is an urgent enquiry

regards

Paul Fisher

For upcoming training course dates see: Pricing & availability

ReplyFri 27 Nov 2009, 11:07Trainer Stephen said...

RE: Pivot Tables and Calculated Fields

Hi Paul

Thanks for your question

If my understanding is correct Columns B and C contain percentage figures showing what proportion of total revenue they take.

The simplest solution would be to add two extra columns to the source data and calculate the actual values there. You could then manipulate that data in a pivot table.

If this approach is not suitable, then please get back to us so we can try something different

Regards

Stephen

ReplyFri 27 Nov 2009, 12:02Delegate Paul said...

RE: Pivot Tables and Calculated Fields

Hi

This approach is not appropriate as the array of data involved is actually more complex than I described. Actually I have many business lines and assets supporting those business lines and I need to be able to report instantaneously, by dragging a field into the data array on the pivot table, what the revenue per business line is. I am also using a pivot chart to rapidly generate the charts that I need (revenue or margin by asset/business line/geographic region/period etc;) The pivot table / chart approach seesm to me to be the simplest way of achieving this.

regards

Paul

ReplyMon 30 Nov 2009, 16:16Trainer Simon said...

RE: Pivot Tables and Calculated Fields

Hi Paul,

Thank you for your question and sorry for the delay in responding.

I have attached a spreadsheet showing you what I have achieved.

Is this what your goal was?

Regards

Simon

Attached files...

Pivot example.xls

Excel articles

· The Key Settings In Excel 2003's Options Panel
· Improving Navigation Of Your Worksheets In Excel
· Share And Share Alike - Use Excel To Collaborate As A Winning Team!
· VBA Excel 2007
· You're Hired! Raising Your Chances Of Employment With Excel

Please browse our web site to find out more about
excel-xp-training and other Microsoft training courses.

Excel tip:

Customize the toolbar in Excel 2010

You can create your own toolbar which contains your favourite or most used tools. This will make using Excel much more efficient. To do this, you need to click on View, then select Customize Quick access Toolbars and then select Customize. A list of tools will then appear on the screen of which you can add or remove them as you please.

View all Excel hints and tips

I have two queries:-

1. In the options menu, there is an exceptions box which you cannot seem to remove. I want the options I have set for Word to apply for all my documents but this does not seem possible and I have noticed with older documents that I..