post the formula you are using and some example data, you could use an IF statement something like
IF(iserror(formula),"100%",formula)
This is a discussion on Percent change of 100% to show 100% in pivot table not DIV/0 within the Excel Questions forums, part of the Question Forums category; Hi there, i want to make a calculation in a pivot table where a percent difference is calculated by year. ...
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?
post the formula you are using and some example data, you could use an IF statement something like
IF(iserror(formula),"100%",formula)
Thanks Shippey
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
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
Thanks Shippey
here is some data from my sheet...
******** ******************** ************************************************************************>
Microsoft Excel - QSHIPBEN.xls ___Running: xl2002 XP : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=
A B C D E F G H I J 1 Sales*Rep Customer Date Invoice Ship*To Address Categroy Item*Code Quantity Sales*Amount 2 C001 DUROXS 6/29/2007 102369 ST*CAT ST.*CATHARINES,*ONT. CM CM5X1NLSBAG*TI 50 31 3 C001 DUROXS 6/29/2007 102369 ST*CAT ST.*CATHARINES,*ONT. TT CM1155*CA*12 12 69.48 4 C001 DUROXS 6/29/2007 102369 ST*CAT ST.*CATHARINES,*ONT. TT CM2161*BC*8 12 101.64 5 C001 DUROXS 6/29/2007 102369 ST*CAT ST.*CATHARINES,*ONT. TT CM1163*TI*12 12 111.6 6 C001 DUROXS 6/29/2007 102369 ST*CAT ST.*CATHARINES,*ONT. TT CM2150*BC*8 25 111.75 7 C001 DUROXS 6/29/2007 102369 ST*CAT ST.*CATHARINES,*ONT. TT CM2152*BB*8 25 128.75
QSHBEN06 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
not sure how to post the pivot table with this html maker, i made a pdf but i dont have anywhere to post it
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.
Richard Schollar
Using xl2013
wow that is a lot nicer than HTML maker! thanks for pointing that out.
Sheet1
* 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%
Excel tables to the web >> Excel Jeanie HTML 4
no solution?
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.
Richard Schollar
Using xl2013
Like this thread? Share it with others