locked
XY Scatter Plot Using Data Row Number Instead of Selected Data RRS feed

  • Question

  • I am having a hard time here.  I have searched all over the web to try to get help on this issue.  People are having the same issues as I am, but none of the fixes that are suggested are working.

    I want to make two series' in an X,Y Scatter Plot.  The data is on two separate sheets from the plot.  The data is basically a standard of where I should be (actual series), and a separate series that shows where I am.  For some reason, the data used for the standard series works fine and makes the graph as it should.  The actual series makes the X value the data point number instead of the selected data.  For example, the 10th data point should be (65, 10500) and the point that is plotted is point number 65, but with the coordinates (10, 10500).  The number comes from a cell that is an IF statement and calculates out a value.

    What I have done.

    I have used the =VALUE(IF(A1)) in all of the cells to ensure that the outcome of the formula is interpreted as a value = no change.

    I have made sure that the cells are formatted as numbers with 2 decimal points = no change.

    I have done an =ISNUMBER(IF(A1)) over all cells to ensure that the contents of the cell are numbers = TRUE.

    I have copied the cells and pasted only the value, not the formula, and made sure (as previously stated) that they are values and graphed them as the x axis = no change.

    I have contacted everyone that I know who works with graphs in Excel to try to help = no change.

    I have read through this website and many others to try to find someone who could help = no change.

    I don't know where to go next. I can't figure out why this keeps coming back incorrect.  Can anyone help me?

    Monday, July 6, 2015 3:09 AM

Answers

  • Well, I figured it out.  So one part of the information that I forgot to mention is that it is a dynamic plot.  As a last resort I tried to see if it would graph without including the blank cells.  It did.  The formula in the cells is an =IF statement such that =IF(D20="",""..., which apparently "" is not considered null in the event you have no data in that cell.  When I switched "" to #N/A, it worked fine, although it filled all my blanks in with #N/A.  I can deal with the ugliness of the #N/A over the pain that I have been going through with figuring it out.
    Monday, July 6, 2015 8:13 AM

All replies

  • Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive (https://onedrive.live.com), FileDropper (http://filedropper.com) or DropBox (https://www.dropbox.com). Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 6, 2015 5:40 AM
  • Well, I figured it out.  So one part of the information that I forgot to mention is that it is a dynamic plot.  As a last resort I tried to see if it would graph without including the blank cells.  It did.  The formula in the cells is an =IF statement such that =IF(D20="",""..., which apparently "" is not considered null in the event you have no data in that cell.  When I switched "" to #N/A, it worked fine, although it filled all my blanks in with #N/A.  I can deal with the ugliness of the #N/A over the pain that I have been going through with figuring it out.
    Monday, July 6, 2015 8:13 AM
  • Indeed, "" counts as a text value, not as a blank, even though it looks blank.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 6, 2015 11:54 AM
  • Thank you! This is something I have been trying to figure out for a long time - this works perfectly!
    Wednesday, April 4, 2018 8:30 AM
  • Thank you so much for figuring this out! I was running into the same issue as I was mapping out my graph.

    I'm working with dynamic data also and I use the OFFSET formula in the name manager to scale the data set. Using this you can still add "" instead of getting the #N/A. For example, if your data exists in A2 to possibly A300 but there is only actual data through A250 you can put the following in Name Manager:

    =OFFSET($A$2,0,0,COUNT($A$2:$A$300),1) 

    Open your the Edit Series for your graph and in the x-axis put in =Tabname!NamedRefrence.

    Viola! your series only plots A2:A250 and no ugliness :)

    Note: OFFSET is amazing for graphs using dynamic data. Just make adjustments as needed if you are not working with numerical data. Always use absolute cell references ($) in the formula.

    Hope this helps you! 

    Thursday, April 19, 2018 6:33 PM