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

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. ...

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

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?

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

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

3. 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

4. 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

5. 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
 A1 =

A
B
C
D
E
F
G
H
I
J
1
Sales*RepCustomerDateInvoiceShip*ToAddressCategroyItem*CodeQuantitySales*Amount
2
C001DUROXS6/29/2007102369ST*CATST.*CATHARINES,*ONT.CMCM5X1NLSBAG*TI5031
3
C001DUROXS6/29/2007102369ST*CATST.*CATHARINES,*ONT.TTCM1155*CA*121269.48
4
C001DUROXS6/29/2007102369ST*CATST.*CATHARINES,*ONT.TTCM2161*BC*812101.64
5
C001DUROXS6/29/2007102369ST*CATST.*CATHARINES,*ONT.TTCM1163*TI*1212111.6
6
C001DUROXS6/29/2007102369ST*CATST.*CATHARINES,*ONT.TTCM2150*BC*825111.75
7
C001DUROXS6/29/2007102369ST*CATST.*CATHARINES,*ONT.TTCM2152*BB*825128.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.

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

7. 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.

8. 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

9. no solution?

10. 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.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•