+ Reply to Thread
Results 1 to 11 of 11

to have pivot table ignore dates with blanks being analyzed

  1. #1
    Forum Contributor
    Join Date
    01-07-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    117

    to have pivot table ignore dates with blanks being analyzed

    Hello

    I created a pivot table to sort my data set for a month

    I would like to have pivot table ignore blanks

    Currently Pivot table is giving a 1 value for each date

    maybe I filtered wrong

    see attached

    the column that I am analyzing has 22 cells for the month I have values in 11 of the cells

    the pivot table is counting all of the cells I dont know why

    In Pivot table fields I have

    Filters I have nothing in there

    Columns I have nothing in there

    Rows I have Date month and Occurance

    Values I have Occurance

    Any advice would be appreciated
    Last edited by Writingformulas; 10-05-2024 at 02:05 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    16,099

    Re: to have pivot table ignore dates with blanks being analyzed

    You appear to have "deleted" the actual pivot table from the sample file, so I wasn't sure exactly what you were trying to do with the pivot table.

    As a guess, I assumed you were using a pivot table to count the non-blank values in column I at the dates in column E. As a start, I used =COUNTA(I1:I11) to see how many non-blank cells there were in this range, and the function returned 11, suggesting that all 11 of those cells are not blank. Further testing (=ISBLANK(I1) and =I1="" copied down) suggests that each of those cells that looks blank is not really blank, but it contains a null string "character." I then observed that if I select I1:I5 and I9:I11 and pressed del to clear those cells, the COUNTA() function immediately reverted to 3 and the ISBLANK() function indicated that those cells were indeed blank.

    It appears to me that the problem is really about "blank" cells that only look blank but are not truly blank. In order to make suggestions, I think we will need to know something about where your source data are coming from and why there is a null string "character" in these cells. Then we may be able to make suggestions for how to better enter or import or process the data to account for "blank but not really blank" cells.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    01-07-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    117

    Re: to have pivot table ignore dates with blanks being analyzed

    in reality I have 4 columns I am analyzing , but I am analyzing one column at a time , is it wise to analyze all four at once and use the pivot table to filter the 4 out individually ?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    16,099

    Re: to have pivot table ignore dates with blanks being analyzed

    I don't work much with pivot tables, so I'm probably the wrong one to ask. If you think you have given enough information for someone who knows pivot tables to answer the question, then we can try to get someone more experienced with pivot tables to respond. I still think more information about your analysis will be needed to truly answer your question.

  5. #5
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,908

    Re: to have pivot table ignore dates with blanks being analyzed

    There is no PivotTable in your sample file. As mentioned by MrShorty, you may have deleted it from the sample file you attached.

  6. #6
    Forum Contributor
    Join Date
    01-07-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    117

    Re: to have pivot table ignore dates with blanks being analyzed

    this is a sample of a larger set
    Last edited by Writingformulas; 10-05-2024 at 02:04 PM.

  7. #7
    Forum Contributor
    Join Date
    01-07-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    117

    Re: to have pivot table ignore dates with blanks being analyzed

    please see attached below/above

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

    Re: to have pivot table ignore dates with blanks being analyzed

    Your Pivot table referring to [Es_Test_2_+GBH_-RBL.xlsx]Results_Test2_+GBH_-RBL'!$E$1:$L$196

    How to compare values?
    Never use Merged Cells in Excel

  9. #9
    Forum Contributor
    Join Date
    01-07-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    117

    Re: to have pivot table ignore dates with blanks being analyzed

    I unmerged cells and it is still giving me wrong calculation

  10. #10
    Forum Contributor
    Join Date
    01-07-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    117

    Re: to have pivot table ignore dates with blanks being analyzed

    the pivot table is giving a value of 1 for every day ,

    other than filtering the days which have a numeric value

  11. #11
    Forum Contributor
    Join Date
    01-07-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    117

    Re: to have pivot table ignore dates with blanks being analyzed

    what I did notice is, the two columns that are giving me an incorrect count are positive values

    while the one that is giving me an accurate count is negative

    any advise would be appreciated

+ 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. Converting Dates VBA but ignore blanks?
    By NewYears1978 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-09-2021, 10:12 PM
  2. Replies: 1
    Last Post: 08-06-2019, 10:43 PM
  3. [SOLVED] Excel Line Chart: Ignore blanks in data and in pivot table
    By TMMc in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-07-2017, 03:07 PM
  4. Grouping dates in Pivot Table that has blanks
    By kfryar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2014, 09:48 PM
  5. [SOLVED] days between dates, ignore blanks
    By dr mint in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-28-2013, 10:39 PM
  6. Replies: 3
    Last Post: 05-08-2013, 07:16 AM
  7. Get Pivot Table To Ignore Blanks
    By StevenAFC in forum Excel General
    Replies: 2
    Last Post: 09-11-2007, 05:06 AM

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