Percent change of 100% to show 100% in pivot table not DIV/0

[Kyle775]

Hi there, i want to make a calculation in a pivot table where a percent difference is calculated by year. The % difference from calculation does not show an increase from the previous year as 100% but a DIV/0 error. Can i make a custom formula that will use the year base field? or something like this?

 

[shippey121]

post the formula you are using and some example data, you could use an IF statement something like

IF(iserror(formula),"100%",formula)

 

[Kyle775]

Right now i have nothing as a formula, excel does the calculation on its own, but shows DIV/0, i would like to use an if statment to calculate the % difference from 2006 to 2007, but i dont know what variable to use for "base item"
Simply telling the table to use 100% for errors will not work because it needs to calculate the 100% for a total in the table.

i guess the formula would be something like this
=IF(AND(2007=0,2006=0),"0.00%",IF(AND(2007=0,2006>0),"-100.00%",(2007-2006)/ABS(2007)))

but im not sure this would calculate the subtotals and totals properly

 

[shippey121]

can you post some data please,

some actual DATA and a part of the pivot table, this way we can see how things are laid out and will be able to offer a solution easier

 

[Kyle775]

here is some data from my sheet...

QSHIPBEN.xls
	A	B	C	D	E	F	G	H	I	J
1	SalesRep	Customer	Date	Invoice	ShipTo	Address	Categroy	ItemCode	Quantity	SalesAmount
2	C001	DUROXS	6/29/2007	102369	STCAT	ST.CATHARINES,ONT.	CM	CM5X1NLSBAGTI	50	31
3	C001	DUROXS	6/29/2007	102369	STCAT	ST.CATHARINES,ONT.	TT	CM1155CA12	12	69.48
4	C001	DUROXS	6/29/2007	102369	STCAT	ST.CATHARINES,ONT.	TT	CM2161BC8	12	101.64
5	C001	DUROXS	6/29/2007	102369	STCAT	ST.CATHARINES,ONT.	TT	CM1163TI12	12	111.6
6	C001	DUROXS	6/29/2007	102369	STCAT	ST.CATHARINES,ONT.	TT	CM2150BC8	25	111.75
7	C001	DUROXS	6/29/2007	102369	STCAT	ST.CATHARINES,ONT.	TT	CM2152BB8	25	128.75
QSHBEN06

 

[Kyle775]

not sure how to post the pivot table with this html maker, i made a pdf but i dont have anywhere to post it

 

[Richard Schollar]

You can use Excel Jeanie rather than the html maker to post pivot tables. Without the pivot table, it is really difficult to appreciate what is happening.

 

[Kyle775]

wow that is a lot nicer than HTML maker! thanks for pointing that out.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
3						Date	Data
4						2006			2007
5	Sales Rep	Customer	Ship To	Address	Categroy	Quantity	Sales Amount	% Difference from PY	Quantity	Sales Amount	% Difference from PY
6	C001	BENGAR	MISCEL	(blank)	MP	2.00					-100.00%
7			MISCEL Total			2.00					-100.00%
8			(blank)	(blank)	MM	5.00					-100.00%
9					MP	3.00					-100.00%
10			(blank) Total			8.00					-100.00%
11		BENGAR Total				10.00					-100.00%
12		BROLAN	MISCEL	(blank)	FP	25.00	92.25		36.00	247.62	44.00%
13			MISCEL Total			25.00	92.25		36.00	247.62	44.00%
14			(blank)	CAMBRIDGE, ON	CB	33.00	1,307.61		11.00	409.25	-66.67%
15					CM	24,134.00	141,822.31		18,558.00	124,514.09	-23.10%
16					FP	15,977.00	54,412.23		11,739.00	42,257.21	-26.53%
17					LM	155.00	1,715.82		72.00	1,877.16	-53.55%
18					ML				5,472.00	39,361.78	#DIV/0!
19					MM	205.00			944.00	6,212.50	360.49%
20					MP	568.00	3,010.62		716.00	4,976.11	26.06%
21					TT	30,857.00	104,598.79		33,596.00	119,460.95	8.88%
22					WM	45.00	401.50		10.00	108.80	-77.78%
23					WS	37.00	104.41		201.00	608.00	443.24%
24			(blank) Total			72,011.00	307,373.29		71,319.00	339,785.85	-0.96%
25		BROLAN Total				72,036.00	307,465.54		71,355.00	340,033.47	-0.95%
26		CRANES	(blank)	STRATFORD, ON	CP	45,679.00	50,389.93				-100.00%
27			(blank) Total			45,679.00	50,389.93				-100.00%
28		CRANES Total				45,679.00	50,389.93				-100.00%
29		DRAGNA	MISCEL	(blank)	CM				90.00	585.30	#DIV/0!
30			MISCEL Total						90.00	585.30	#DIV/0!
31			(blank)	SCARBOROUGH, ON	CB	591.00	21,497.18		454.00	16,747.76	-23.18%
32					CM	10,003.00	55,651.98		8,869.00	43,337.87	-11.34%
33					FP	1,740.00	12,441.20		1,245.00	10,686.03	-28.45%
34					LM	170.00	3,341.30		173.00	1,849.33	1.76%
35					MM	39.00			50.00		28.21%
36					TT	5,928.00	9,663.32		1,061.00	5,944.27	-82.10%
37			(blank) Total			18,471.00	102,594.98		11,852.00	78,565.26	-35.83%
38		DRAGNA Total				18,471.00	102,594.98		11,942.00	79,150.56	-35.35%
39		DRAMIS	MISC	(blank)	MM	1.00					-100.00%
40					TT	1.00					-100.00%
41			MISC Total			2.00					-100.00%
42			(blank)	MISSISSAUGA, ON	CB	529.00	18,960.39		769.00	27,182.62	45.37%
43					CM	3,387.00	16,792.87		2,669.00	14,337.33	-21.20%
Sheet1

 

[Kyle775]

no solution?

 

[Richard Schollar]

Hi

Where you have #DIV/0! that is because you have no value for the prior year and since the percentage change is given by:

=(New Value - Old Value)/Old Value

you end up with a denominator of zero (ie Old Value) and hence the error.