+ Reply to Thread
Results 1 to 6 of 6

#DIV/0! in Pivot Table

  1. #1
    Forum Contributor
    Join Date
    04-30-2013
    Location
    D.C.
    MS-Off Ver
    Excel 2010
    Posts
    192

    #DIV/0! in Pivot Table

    How can I make the data show in percentage when one item in a list of inventory are both zero in the pivot table instead of getting #DIV/0! error? (a row includes two columns: Cost: $0.00 and Sales: $0.00 and rest of the rows includes two columns: cost and sales varying from $.01 to $1,000,000?)

  2. #2
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    118

    Re: #DIV/0! in Pivot Table

    Can you provide a workbook example?

  3. #3
    Forum Contributor
    Join Date
    04-30-2013
    Location
    D.C.
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: #DIV/0! in Pivot Table

    WorkbookExample.xlsx

    Here it is.

  4. #4
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    118

    Re: #DIV/0! in Pivot Table

    Yea, I figured it was part of a formula. In your data tab, in the percentage column, add iferror(...,0). So:

    =IFERROR((D2-C2)/D2,0)

    This will return 0s for all errors. However, you should not average percentages, it gives the wrong percentage in the pivot table considering percentages are weighted numbers dependent on the numbers being calculated. For example:

    Ex1:
    Cost: 1
    Sales: 2
    %: 50%

    Ex2:
    Cost: 40
    Sales: 100
    %: 60%

    If you averaged these, you'd get 55%, which would inaccurately represent your data given that ex2 contains several more data points. Combining the two into Cost: 41, Sales: 102 give you %:~60%.


    If your formula for percentage on the data page is =(Sales-Cost)/Sales, then that should be what it is on the pivot table as well. Instead of the average column in your pivot table, you should make a calculated column with the formula =(Sales-Cost)/Sales.

    Hope this helps

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: #DIV/0! in Pivot Table

    Didn't look exxample but try:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    04-30-2013
    Location
    D.C.
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: #DIV/0! in Pivot Table

    Thanks!!! It worked well.

    And also, thanks for the extra insight about mathematical perspective!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1