Power Pivot Grand Total Calculation Error

[Avra]

I have a Power Pivot Table with a Grand Total calculation error. Here is the database:

MR Excel.xlsm
	A	B							I							P												AB				AF			AI							AP	AQ
1									Mailing List 1							Capitalization 2												Records 3				Convertible Class A Voting 4			Units Summary 5							Capital Summary 6
2
3		Mr Excel Database
4
5		Name							1							2												3				4			5							6
6		Mr Excel
7		Mrs Excel
8		Excel Jr
9		Total
10
Mr Excel Database

Cell Formulas
Range	Formula
I1	I1	=C4&" "&ZeoThermalDatabase[[#Headers],[1]]
P1	P1	=J4&" "&ZeoThermalDatabase[[#Headers],[2]]
AB1	AB1	=Q4&" "&ZeoThermalDatabase[[#Headers],[3]]
AF1	AF1	=AC4&" "&ZeoThermalDatabase[[#Headers],[4]]
AI1	AI1	=AG4&" "&ZeoThermalDatabase[[#Headers],[5]]
AP1	AP1	=AJ4&" "&ZeoThermalDatabase[[#Headers],[6]]

Here is the Pivot Table:

MR Excel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Mr Excel Technologies LLC Capitalization Summary 2014
3			UNDILUTED BASIS		FULLY DILUTED BASIS
4			Class A Voting		Convertible Class A Voting			Units Summary		Capital Summary
5
6		Date of Purchase	All
7
8		Investor Name	Undiluted Basis Class A Units	Ownership Percent	From Convertible Debt	From Outstanding Warrants	Total Units Controlled	Fully Dilluted Units	Diluted Ownership Percent	Class A Invested Capital	Convertible Debt Invested Capital	Warants Invested Capital	Other Promissory Notes Payable Net of Interest Due	Total Invested Capital	Capital Invested Percent
9		Excel Jr	2,500,000	33.33%	0	0	2,500,000	2,500,000	33.33%	$ 25,000	$ -	$ -	$ -	$ 25,000	33.33%
10		Mr Excel	2,500,000	33.33%	0	0	2,500,000	2,500,000	33.33%	$ 25,000	$ -	$ -	$ -	$ 25,000	33.33%
11		Mrs Excel	2,500,000	33.33%	0	0	2,500,000	2,500,000	33.33%	$ 25,000	$ -	$ -	$ -	$ 25,000	33.33%
12		Grand Total	7,499,999	100.00%	0	0	7,499,999	7,499,999	100.00%	$ 75,000	$ -	$ -	$ -	$ 75,000	100.00%	What's causing this error?
13
14
Mr Excel

Why is the Grand Total calculation in error. The Grand Total should be 7,500,000.

 

[alz]

I have a Power Pivot Table with a Grand Total calculation error. Here is the database:

MR Excel.xlsm
	A	B							I							P												AB				AF			AI							AP	AQ
1									Mailing List 1							Capitalization 2												Records 3				Convertible Class A Voting 4			Units Summary 5							Capital Summary 6
2
3		Mr Excel Database
4
5		Name							1							2												3				4			5							6
6		Mr Excel
7		Mrs Excel
8		Excel Jr
9		Total
10
Mr Excel Database

Cell Formulas
Range	Formula
I1	I1	=C4&" "&ZeoThermalDatabase[[#Headers],[1]]
P1	P1	=J4&" "&ZeoThermalDatabase[[#Headers],[2]]
AB1	AB1	=Q4&" "&ZeoThermalDatabase[[#Headers],[3]]
AF1	AF1	=AC4&" "&ZeoThermalDatabase[[#Headers],[4]]
AI1	AI1	=AG4&" "&ZeoThermalDatabase[[#Headers],[5]]
AP1	AP1	=AJ4&" "&ZeoThermalDatabase[[#Headers],[6]]

Here is the Pivot Table:

MR Excel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Mr Excel Technologies LLC Capitalization Summary 2014
3			UNDILUTED BASIS		FULLY DILUTED BASIS
4			Class A Voting		Convertible Class A Voting			Units Summary		Capital Summary
5
6		Date of Purchase	All
7
8		Investor Name	Undiluted Basis Class A Units	Ownership Percent	From Convertible Debt	From Outstanding Warrants	Total Units Controlled	Fully Dilluted Units	Diluted Ownership Percent	Class A Invested Capital	Convertible Debt Invested Capital	Warants Invested Capital	Other Promissory Notes Payable Net of Interest Due	Total Invested Capital	Capital Invested Percent
9		Excel Jr	2,500,000	33.33%	0	0	2,500,000	2,500,000	33.33%	$ 25,000	$ -	$ -	$ -	$ 25,000	33.33%
10		Mr Excel	2,500,000	33.33%	0	0	2,500,000	2,500,000	33.33%	$ 25,000	$ -	$ -	$ -	$ 25,000	33.33%
11		Mrs Excel	2,500,000	33.33%	0	0	2,500,000	2,500,000	33.33%	$ 25,000	$ -	$ -	$ -	$ 25,000	33.33%
12		Grand Total	7,499,999	100.00%	0	0	7,499,999	7,499,999	100.00%	$ 75,000	$ -	$ -	$ -	$ 75,000	100.00%	What's causing this error?
13
14
Mr Excel

Why is the Grand Total calculation in error. The Grand Total should be 7,500,000.

What formula in C12, and G12,H12?

 

[MARK858]

Also what do the values in C9,C10 and C11 show if you format the cells to 15 decimal places?

 

[Avra]

What formula in C12, and G12,H12?

There are no formulas. Row 12 is a Grand Total row generated through the creation of a Pivot Table.

 

[Avra]

Also what do the values in C9,C10 and C11 show if you format the cells to 15 decimal places?

2,500,000.000000000000000, etc... The pivot table pulls from the database above which has keyed in data.

 

[Avra]

I just realized that my database had most of the columns collapsed. Here is the full database:

MR Excel.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
1		Mailing List						Mailing List 1	Capitalization						Capitalization 2	Records											Records 3	Convertible Class A Voting			Convertible Class A Voting 4	Units Summary		Units Summary 5	Capital Summary						Capital Summary 6
2
3	Mr Excel Database
4		Mailing List							Capitalization							Records												Convertible Class A Voting				Units Summary			Capital Summary
5	Name	First Name	Contact	Street Address	City ST 9-Digit Zip	Email	Cell Phone	1	Date of Purchase	Capital	Unit Price	Units Owned	Ownership Percent	Class	2	Record %	Inventory Record	Confidentiality Agreement	W-9	Joinder	Present in CAP	Joinder Match CAP	Certificate Created	Certificate on File	Certificate Delivered	Certificate Number	3	From Convertible Debt	From Outstanding Warrants	Total Units Controlled	4	Fully Dilluted Units	Diluted Ownership Percent	5	Class A Invested Capital	Convertible Debt Invested Capital	Warants Invested Capital	Other Promissory Notes Payable Net of Interest Due	Total Invested Capital	Capital Invested Percent	6
6	Mr Excel								November 26, 2014	$ 25,000	$ 0.01	2,500,000	33.33%	A		100.00%	1	1	1	1	1	1	1	1	1	1		0	0	2,500,000		2,500,000	33.33%		$ 25,000.00	0	0	0	$ 25,000	33.33%
7	Mrs Excel								November 26, 2014	$ 25,000	$ 0.01	2,500,000	33.33%	A		100.00%	1	1	1	1	1	1	1	1	1	2		0	0	2,500,000		2,500,000	33.33%		$ 25,000.00	0	0	0	$ 25,000	33.33%
8	Excel Jr								November 26, 2014	$ 25,000	$ 0.01	2,500,000	33.33%	A		100.00%	1	1	1	1	1	1	1	1	1	3		0	0	2,500,000		2,500,000	33.33%		$ 25,000.00	0	0	0	$ 25,000	33.33%
9	Total									$ 75,000	$ 0.01	7,500,000	100.00%															0	0	7,500,000		7,500,000	100.00%		$ 75,000.00	0	0	0	$ 75,000	100.00%
Mr Excel Database

Cell Formulas
Range	Formula
C1,J1,Q1,AC1,AG1,AJ1	C1	=C4
I1	I1	=C4&" "&ZeoThermalDatabase[[#Headers],[1]]
P1	P1	=J4&" "&ZeoThermalDatabase[[#Headers],[2]]
AB1	AB1	=Q4&" "&ZeoThermalDatabase[[#Headers],[3]]
AF1	AF1	=AC4&" "&ZeoThermalDatabase[[#Headers],[4]]
AI1	AI1	=AG4&" "&ZeoThermalDatabase[[#Headers],[5]]
AP1	AP1	=AJ4&" "&ZeoThermalDatabase[[#Headers],[6]]
AG6:AG8	AG6	=[@[Units Owned]]
AH6:AH8	AH6	=[@[Fully Dilluted Units]]/SUM([Fully Dilluted Units])
AG9	AG9	=SUBTOTAL(109,[Fully Dilluted Units])
AH9	AH9	=SUBTOTAL(109,[Diluted Ownership Percent])
AN6:AN8	AN6	=SUM(ZeoThermalDatabase[@[Class A Invested Capital]:[Other Promissory Notes Payable Net of Interest Due]])
AO6:AO8	AO6	=[@[Total Invested Capital]]/SUM([Total Invested Capital])
N6:N8	N6	=[@[Units Owned]]/SUM([Units Owned])
Q6:Q8	Q6	=SUM(ZeoThermalDatabase[@[Inventory Record]:[Certificate Delivered]])/(COLUMNS(ZeoThermalDatabase)-32)
AE6:AE8	AE6	=[@[Units Owned]]+SUM(ZeoThermalDatabase[@[From Convertible Debt]:[From Outstanding Warrants]])
AJ6:AJ8	AJ6	=[@Capital]
K9	K9	=SUBTOTAL(109,[Capital])
L9	L9	=SUBTOTAL(104,[Unit Price])
M9	M9	=SUBTOTAL(109,[Units Owned])
N9	N9	=SUBTOTAL(109,[Ownership Percent])
AC9	AC9	=SUBTOTAL(109,[From Convertible Debt])
AD9	AD9	=SUBTOTAL(109,[From Outstanding Warrants])
AE9	AE9	=SUBTOTAL(109,[Total Units Controlled])
AJ9	AJ9	=SUBTOTAL(109,[Class A Invested Capital])
AK9	AK9	=SUBTOTAL(109,[Convertible Debt Invested Capital])
AL9	AL9	=SUBTOTAL(109,[Warants Invested Capital])
AM9	AM9	=SUBTOTAL(109,[Other Promissory Notes Payable Net of Interest Due])
AN9	AN9	=SUBTOTAL(109,[Total Invested Capital])
AO9	AO9	=SUBTOTAL(109,[Capital Invested Percent])

 

[alz]

I am not sure But it could be consequence of Subtotal function 109 . by hidden roles

 

[Avra]

I am not sure But it could be consequence of Subtotal function 109 . by hidden roles

Alz,

There are no hidden rows. Also, since the total is generated by a pivot table, I cannot change the Subtotal function.

 

[RoryA]

It would probably help if you could put your actual file somewhere (eg OneDrive or Dropbox) for us to look at.

 

[Avra]

Hi Rory,

Here is the link:

MR Excel.xlsm

drive.google.com