+ Reply to Thread
Results 1 to 4 of 4

Plot scatter plot without including blank cells or cell with #n/a or #value

  1. #1
    Registered User
    Join Date
    01-05-2018
    Location
    New Zealand
    MS-Off Ver
    MsExcel 2016
    Posts
    12

    Plot scatter plot without including blank cells or cell with #n/a or #value

    Hi,

    I want to plot several scatter plots without including blank cells or cell with #n/a or #value. Assume A2:A8000 AND B2:B8000 are the cells containing the two variables to be plotted.

    Any help is highly welcome. Kindly find attached the spreadsheet showing the data requiring scatter plots for each pair.

    Cheers.
    Attached Files Attached Files
    Last edited by cdad; 02-07-2018 at 04:09 PM.

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Plot scatter plot without including blank cells or cell with #n/a or #value

    Charts already ignore errors and empty cells.

    In the particular case of cells that have an empty text string -- "" -- then maybe wrap a helper function that throws an error code, and chart of off that (in C2:C8000 say)?
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Registered User
    Join Date
    01-05-2018
    Location
    New Zealand
    MS-Off Ver
    MsExcel 2016
    Posts
    12

    Re: Plot scatter plot without including blank cells or cell with #n/a or #value

    I am afraid, no, by default, it does not. Kindly see attached spreadsheet showing the data requiring scatter plots for each pair.
    Attached Files Attached Files

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

    Re: Plot scatter plot without including blank cells or cell with #n/a or #value

    None of the cells appear to be blank or empty (meaning that ISBLANK(cell reference) returns TRUE). The ISTEXT() function suggests that all of the cells that look blank contain some kind of invisible text string -- and scatter plots treat text (even empty text "") as the number 0. The usual strategy for ignoring cells that contain a formula is to have the formula return the N/A error [NA() function]. Full discussion here: https://peltiertech.com/mind-the-gap...g-empty-cells/ The solution I see, as ben_hensel suggests, is to use a formula to replace those invisible text strings with N/A.
    Last edited by MrShorty; 02-07-2018 at 08:30 PM.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Replies: 9
    Last Post: 01-19-2018, 03:57 AM
  2. Plot X & Y value on scatter plot based on cell value
    By Nick2512 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-14-2017, 12:17 AM
  3. How to plot 3 variables in scatter plot with excel formula
    By tanvir in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-13-2017, 04:10 AM
  4. Excel 2010 - X Y scatter plot will not plot empty cells as gap
    By bjeffers0306 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-12-2013, 12:56 AM
  5. Replies: 2
    Last Post: 07-26-2012, 09:27 AM
  6. Scatter Plot that omits blank cells?
    By dsklein85 in forum Excel General
    Replies: 1
    Last Post: 05-03-2012, 11:32 AM
  7. Converting XY Scatter plot to Line Plot and back
    By SPDavern in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2005, 10:06 PM

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