pivot table totals

[JLa]

I'm trying to get a pivot table to cum the subtotals within the pivot table by using a calculated field and can't figure out how to do this. I have data as listed below. The field I'm trying to insert for multiple categories within the pivot table is the CTD.

Thanks for any help you can provide. Jan Feb Mar Apr
cat 1 10 10 10 10
Tot 10 10 10 10

CTD 10 20 30 40

 

[Mark W.]

Like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Category	Value	Month					Month
2	cat1	10	Jan			Category	Data	Jan	Feb	Mar	Apr	GrandTotal
3	cat1	10	Feb			cat1	SumofValue	10	10	10	10	40
4	cat1	10	Mar				CTD	10	20	30	40
5	cat1	10	Apr			TotalSumofValue		10	10	10	10	40
6						TotalCTD		10	20	30	40
7
Sheet6

 

[JLa]

Yes, that is exactly what I need.

How were you able to do that? Thanks for your help.

 

[Mark W.]

You must reorganize your data into 1st Normal Form (1NF) as shown above. If I understand you original post your data contains a "repeating group" of values indexed by month.
This message was edited by Mark W. on 2002-11-04 14:09

 

[JLa]

Mark,

I'm not sure I understand what you mean.

I believe the data is already in 1NF (although I'm not sure exactly what that is) and the data does have repeating values for multiple categories that is indexed by month/yr.

From the example you gave me, I still don't understand where you CTD came from. Thank you very much for your help and I don't mean to be so ignorant.

 

[Mark W.]

By definition 1NF is devoid of repeating groups. You should not have separate columns for each month.

 

[JLa]

Mark,

My data is properly formatted.

Is your CTD a calculated field? I can't tell from your example how that field is being generated.

 

[Mark W.]

No, 'CTD' is created by dragging another copy of 'Value' to the PivotTable's DATA area, changing its name to 'CTD', clicking the [ Options >> ] button, and setting "Show data as" field to "Running Total in" and the "Base field" to "Month".

 

[attc]

As part of my learning from a separate post on a similar topic, I came across the following tip from the j-walk site that people may find useful:

http://www.j-walk.com/ss/excel/usertips/tip068.htm

In simple terms, this is basically a technique for organizing your data better for the purposes of summary and analysis.

 

[Mark W.]

I rather like John Carter's Programming in SQL with ORACLE, INGRES, and dBASE IV. His section on Normalization in chapter 2 is very comprehensible. The key is to put Excel data lists in 1NF to maximize the utility of PivotTables.