PivotTable Totals in Excel - doubling totals

[cmrobinson]

Hello all,
I am using a PivotTable to generate totals. The problem is that it is doubling the totals on me. I create a formula from my fields and when I have more than one line of the same item, it is doubling the sum of the formula.

For Example:
Course X
Max students 10
Course tuition 100

I create a formula in the PivotTable to calculate the Max Tuition Rev = Max Students*Course tuition. This field goes in the data area of my pivottable. When there is more than one row of info for a particular course I get a doubling on the sum. In the example above, it will give me 4000 vs 2000 if I had two courses with the same data.

Anybody out there have an idea? I may just not be seeing something in my own logic.

Thanks,
Carrie

 

[SkipVought]

Hi,

It's because your table is not normalized. Your source data should not have repeating numeric values that you want to sum/count/averabe etc.

Why do you have multiple entries per course?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tav1035]

cmrobinson,
Insert another column called Course. This will allow you to seperate your criteria from your values.
Your data should look like this.
ie.

Course Max students Course tuition
X 10 100
Y 15 100
Z 10 100

tav

 

[cmrobinson]

SkipVought and tav1035,
Thanks for the ideas but I guess I missed one important aspect of my problem. I may have several sections of the same course. So I may have 3 different rows for course X, all unique in the number of students they have. It works great if I only have one record per course.

I want to be able to sum those 3 rows to come up with a total for that course and I want it to be done on the fly. Hence the pivottable.

I understand normalization and agree it doesn't follow. I'm starting to think I have to move to Access and create proper tables and normalize.

Your opinions?

Thanks
Carrie

 

[SkipVought]

Give me an example of your 3 rows of data for one course including headings

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tav1035]

cmrobinson,

Pivots are all about seeing the unique value on the fly.
Try this-> Notice that there are reoccurring values.

Course Max students Course tuition
X 10 100
Y 15 100
Z 10 100
X 10 150
X 11 110


Create a sample that looks like this and play with it.
From the Chart tab....
Pull the course to the bottom of your pivot chart and pull the tuition to the middle of the chart, then finally pull the Max students to the legend area (on the right side).
I believe this will do what your asking.

Like Skip said, your data is not normalized.
There is also ways to normalize your data through scripts, which is where the other gurus take over.
tav

 

[cmrobinson]

Thanks all. Here is an example of my data and what results I am after.

Course Section Max Students Tuition
X 1234 10 $100
X 1236 10 $100
Y 2345 20 $200
Z 3456 10 $150
Z 2458 10 $150

I want to have, in my data portion of the pivottable, the calculated field of Max Students * Tuition, Course is the row field and I don't need a column field. I want to see the total tuition revenue for each course, therefore having the sections added together. And with pivottables I can also drill down to look at each individual section when I want more detail. It seems like the ideal solution??

 

[SkipVought]

So my PivotTable shows...

Sum of AMOUNT	
Course Total
X       2000
Y       4000
Z       3000
Gr Tot  9000

What am I missing?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tav1035]

cmrobinson,
Same question....
If your data is exactly in the layout that you submitted, then I don't see a problem.
When you say "Course is the row field and I don't need a column field", this contridicts the data layout you provided. A row field (persay) is not the way to go.
The data (layout) that we provided is very simuilar to what your showing.
If this isn't working for you, we must not be understanding your question.
tav

 

[cmrobinson]

This is weird, because I do the exact same thing and I get doubling totals. This is what mine looks like.

Data:
Course CRN Students Tuition
x 1234 10 100
x 1236 10 100
y 2345 20 200
z 3456 10 150
z 3458 10 150

PivotTable:
Sum of Amount
Course Total
x 4000
y 4000
z 6000
Grand Total 42000

My Amount formula = Student*Tuition

It doubles all the time... I don't get it. At least I know I'm not crazy in thinking it should work the way I think now. I'm using Excel XP SP2, what are you guys using? I've tried it on 2 machines but with the same load. I will try and find a 2000 version for test.

Thanks for all your help. Greatly appreciated! The mystery continues.
cmrobinson

 

[SkipVought]

Hey,

Send me your workbook and I'll take a look this evening

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[combo]

Hi cmrobinson,
I'm afraid that the problem is inside pivot table calculations. Whenever your source data is summed into one row (sums, subtotals or totals), the formula result is not sum of products, but product of sums for aggregated fields.
So instead of formula field, rather add formula for course tuition in the source data table, as tav1035 suggested.

combo