+ Reply to Thread
Results 1 to 8 of 8

Excel pivot table from external SP List - Problem with average duration #DIV/0!

  1. #1
    Registered User
    Join Date
    04-22-2020
    Location
    Georgia
    MS-Off Ver
    Office365 ProPlus
    Posts
    4

    Excel pivot table from external SP List - Problem with average duration #DIV/0!

    I have a SP list that I use as the data source for a PowerApp. For reporting, I have an Excel spreadsheet in OneDrive linked to that SP List. In that spreadsheet, I build Pivot tables and charts for reporting purposes. All of that works. Here is my problem for which I need help - this is blocking me from moving forward on a critical project:

    -In the SP list, there is a duration calculation column based on two dates in other columns. That column properly sends over to the Excel spreadsheet as a General column. There are no blanks, but there are some zeroes. (I've tried making in numeric as well.)
    -In one of my Pivot tables, I want to show the average duration for the records in a set of areas.
    -That Pivot table results in the #DIV/0! error on all rows (a row for each of the set of areas, and a grand total)

    I have looked and looked and set most every setting I can find, and nothing makes this work. I've tried deleting and recreating after refreshing multiple times.

    I somehow suspect that this has something with the data being pulled NOT being what I think is being pulled. I've tried filtering out parts of the data set that could be blank. The zeroes in the duration column need to stay because the duration was indeed 0 days. Plus that would be in the numerator of the Average calculation, not the denominator, so that can't be it.

    Can anyone help? Thank you so much!!!!

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,125

    Re: Excel pivot table from external SP List - Problem with average duration #DIV/0!

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Registered User
    Join Date
    04-22-2020
    Location
    Georgia
    MS-Off Ver
    Office365 ProPlus
    Posts
    4

    Re: Excel pivot table from external SP List - Problem with average duration #DIV/0!

    Thank you for the warm welcome!

    And I did see the yellow post, but I cannot figure out how to scrub this enough to post it. It is linked up to an internal SP list and then linked up to full data in another worksheet.

    It is internal to my company and they will NOT be okay if I post this publicly.

    Anyway, I can send a screenshot, even though I know it says not to do so.

    Detail that may help you help me

    -Industry is the set of areas I mention - I want to know the average duration between the time a record is created and when it is sent to the SE Leader
    -DurationtoSELeader is a calculated field in Sharepoint
    =IF(ISBLANK(DatetoSELeader),"0",DatetoSELeader-DateCreated)
    -Status is solely to filter out anything that would not have yet been sent to the SE Leader, so that date is blank. (Is this the problem? While I filter this in my PivotTable and Chart, I can't filter it out of the source data because I need to be able to report on them too, just not in this table/chart.)

    Thanks again!!!

    PS - Also have posted what that data looks like in the linked up spreadsheet of all raw data...
    Last edited by carolekv; 04-22-2020 at 10:05 AM.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Excel pivot table from external SP List - Problem with average duration #DIV/0!

    if you post a small excel file I will take a look at it.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    04-22-2020
    Location
    Georgia
    MS-Off Ver
    Office365 ProPlus
    Posts
    4

    Re: Excel pivot table from external SP List - Problem with average duration #DIV/0!

    Actually I was able to recreate my problem in a small simple unlinked spreadsheet! Attached...

  6. #6
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,125

    Re: Excel pivot table from external SP List - Problem with average duration #DIV/0!

    As far as I can see, the data under DurationToSELeaedr seems to be text instead of real numbers ( it is left aligned) if you have not aligned the data manually.

    Try selecting the data Click the Data tab - Text to columns, and Finish. If all is well the contents will now right-align showing they are numbers.
    DO NOT try to format as numbers, it will not work

  7. #7
    Registered User
    Join Date
    04-22-2020
    Location
    Georgia
    MS-Off Ver
    Office365 ProPlus
    Posts
    4

    Re: Excel pivot table from external SP List - Problem with average duration #DIV/0!

    That fixed it! I so appreciate your help! I had tried everything but that, but I had no idea that Text to Columns would do this fix. Is there a good training somewhere that I could learn about this kind of thing? I am fully self-taught in Excel, so I know there are gaps in my knowledge that could be solved.

    Thank you!!!

  8. #8
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,125

    Re: Excel pivot table from external SP List - Problem with average duration #DIV/0!

    Most welcome
    One way of training is trying to help on this forum and reading the solutions proposed by our members and trying to understand how the functions work
    If your question is solved please click "thread solved" in the "Thread tools" drop-down

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Average of a duration in a pivot chart
    By augustinlesg in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-12-2018, 05:43 PM
  2. Is it possible to add duration field in pivot table?
    By Goodstart14 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-11-2015, 05:53 PM
  3. add a duration column in pivot table
    By Goodstart14 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-18-2015, 10:03 AM
  4. Excel 2010 pivot table with external data source
    By thavarajah in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-24-2015, 11:50 AM
  5. Replies: 6
    Last Post: 07-31-2014, 12:56 PM
  6. Average problem with Pivot Table
    By brotherwo in forum Excel General
    Replies: 10
    Last Post: 10-04-2010, 12:24 PM
  7. How to create Pivot table with external source(SSAS Cube) via Excel Add-in
    By VenkatExcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2009, 07:39 AM

Tags for this Thread

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