Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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. #1
    Board Regular
    Join Date
    Feb 2005
    Posts
    230

    Default 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. #2
    Board Regular
    Join Date
    Mar 2006
    Location
    UK
    Posts
    974

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2005
    Posts
    230

    Default

    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. #4
    Board Regular
    Join Date
    Mar 2006
    Location
    UK
    Posts
    974

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2005
    Posts
    230

    Default

    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*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. #6
    Board Regular
    Join Date
    Feb 2005
    Posts
    230

    Default

    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. #7
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,673

    Default

    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

  8. #8
    Board Regular
    Join Date
    Feb 2005
    Posts
    230

    Default

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

    Sheet1

    *ABCDEFGHIJK
    3*****DateData****
    4*****2006**2007**
    5Sales RepCustomerShip ToAddressCategroyQuantitySales Amount% Difference from PYQuantitySales Amount% Difference from PY
    6C001BENGARMISCEL(blank)MP2.00****-100.00%
    7**MISCEL Total**2.00****-100.00%
    8**(blank)(blank)MM5.00****-100.00%
    9****MP3.00****-100.00%
    10**(blank) Total**8.00****-100.00%
    11*BENGAR Total***10.00****-100.00%
    12*BROLANMISCEL(blank)FP25.0092.25*36.00247.6244.00%
    13**MISCEL Total**25.0092.25*36.00247.6244.00%
    14**(blank)CAMBRIDGE, ONCB33.001,307.61*11.00409.25-66.67%
    15****CM24,134.00141,822.31*18,558.00124,514.09-23.10%
    16****FP15,977.0054,412.23*11,739.0042,257.21-26.53%
    17****LM155.001,715.82*72.001,877.16-53.55%
    18****ML***5,472.0039,361.78#DIV/0!
    19****MM205.00**944.006,212.50360.49%
    20****MP568.003,010.62*716.004,976.1126.06%
    21****TT30,857.00104,598.79*33,596.00119,460.958.88%
    22****WM45.00401.50*10.00108.80-77.78%
    23****WS37.00104.41*201.00608.00443.24%
    24**(blank) Total**72,011.00307,373.29*71,319.00339,785.85-0.96%
    25*BROLAN Total***72,036.00307,465.54*71,355.00340,033.47-0.95%
    26*CRANES(blank)STRATFORD, ONCP45,679.0050,389.93***-100.00%
    27**(blank) Total**45,679.0050,389.93***-100.00%
    28*CRANES Total***45,679.0050,389.93***-100.00%
    29*DRAGNAMISCEL(blank)CM***90.00585.30#DIV/0!
    30**MISCEL Total*****90.00585.30#DIV/0!
    31**(blank)SCARBOROUGH, ONCB591.0021,497.18*454.0016,747.76-23.18%
    32****CM10,003.0055,651.98*8,869.0043,337.87-11.34%
    33****FP1,740.0012,441.20*1,245.0010,686.03-28.45%
    34****LM170.003,341.30*173.001,849.331.76%
    35****MM39.00**50.00*28.21%
    36****TT5,928.009,663.32*1,061.005,944.27-82.10%
    37**(blank) Total**18,471.00102,594.98*11,852.0078,565.26-35.83%
    38*DRAGNA Total***18,471.00102,594.98*11,942.0079,150.56-35.35%
    39*DRAMISMISC(blank)MM1.00****-100.00%
    40****TT1.00****-100.00%
    41**MISC Total**2.00****-100.00%
    42**(blank)MISSISSAUGA, ONCB529.0018,960.39*769.0027,182.6245.37%
    43****CM3,387.0016,792.87*2,669.0014,337.33-21.20%


    Excel tables to the web >> Excel Jeanie HTML 4

  9. #9
    Board Regular
    Join Date
    Feb 2005
    Posts
    230

    Default

    no solution?

  10. #10
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,673

    Default

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com