Graph Coordinates in Excel 2011

Discussion in 'Mac Apps and Mac App Store' started by Ice~Queen, Sep 5, 2011.

  1. Ice~Queen macrumors newbie

    Joined:
    Sep 5, 2011
    #1
    I'm trying to figure out if I can graph this and if so how, because so far I can't figure it out....

    In my excel file I have a list of coordinates X:Y in one column, which go from 0-600.

    For example:
    368:274
    369:274
    424:280
    244:280
    41:327
    49:6
    476:308
    6:14

    Its all in one column as X-value:Y-value, so x-value is 368, y-value is 274.
    I want to take these coordinates and graph them like a map kinda. I really don't want to go through and separate these into two columns since its A LOT of data I have. Is there a way to tell excel that in that column its X:Y. like an option when making graph, or a formula or something? Is there a way to graph this and how? Thanks!
     
  2. Cybbe macrumors 6502

    Joined:
    Sep 15, 2004
    #2
    I would just do this:

    Leave your list of coordinates in column A.

    In column B, use the following code:

    =LEFT(A1,FIND(":",A1)-1)

    In column C, use the following code:

    =RIGHT(A1,LEN(A1)-FIND(":",A1))

    Expand as appropriate.

    Likely to be other ways as well.
     
  3. GrendelsBane macrumors newbie

    Joined:
    Jun 1, 2011
    #3
    That would work beautifully. But you could just use the "Text to Columns" command (in the Data Menu) using the ":" as the delimiting character.
     
  4. Ice~Queen thread starter macrumors newbie

    Joined:
    Sep 5, 2011
    #4
    @GrendelsBane
    Yes that would work, but that means excel would spilt the numbers into 2 columns and I want to keep the original numbers. Which to do that then I would have to copy and paste those coordinates in another column and do the text to columns on that copy.

    @Cybbe
    That works, but how do I tell the formula to skip blank cells so that I do not have the annoying #VALUE! since there is no value for it to use LOL

    Also do I then need to tell the graph to skip those blank too?
     
  5. GrendelsBane macrumors newbie

    Joined:
    Jun 1, 2011
    #5
    Use IFERROR:

    =IFERROR(LEFT(A1,FIND(":",A1)-1),"")

    That will return a blank if the formula would result in an error code.

    Be aware that IFERROR is a new function and only works in the later versions of Excel. Otherwise use IF plus ISERROR.
     
  6. Ice~Queen thread starter macrumors newbie

    Joined:
    Sep 5, 2011
    #6

    That doesn't quiet to what I was hoping.... I want it to skip blanks. The IfError just hides the #Value, but its still doing the equation on blank cells and flagging them since formula refers to empty cell. I think I need some kind of an If statement for if there is a value then perform the formula, just having hard time with writing the correct formula... hopefully you can help me...

    My first number is in A2- 230:430
    =IF(ISBLANK(A2),"",((LEFT(A2,FIND(":",A2)-1))*1))
    =IF(ISBLANK(A2),"",((RIGHT(A2,LEN(A2)-FIND(":",A2)))*1))

    So IF value1 IsBlank(A#) is True, then ""
    if False, then else ((LEFT(A2,FIND(":",A2)-1))*1) or ((RIGHT(A2,LEN(A2)-FIND(":",A2)))*1)) depending on which side your working on. I have both Right formula and Left formula multiplied by 1 since it kept wanting to store as a text so multiplying it by 1 makes it a number format again.

    So for A2 Isblank(A2) is False, so then formula gets 230 for B2 and 430 for C2

    Now when I get to A8 I have a blank cell and this is what the formula shows as happening in Formula Builder...

    IsBlank(A8) is True, then ""
    Else ((LEFT(A2,FIND(":",A2)-1))*1) --> #VALUE!
    Result: ""

    So looking at that, it looks like I wrote a formula that works, but the Big problem occurs when I go to graph. Because the blank cells are flagged for Formula refers to Empty cells, its screwing up all my data points on scatter plot.

    It looks like its mostly plotting the correct Y-values, but the x-values its showing completely different numbers.
    For example, I have the coordinate for 244:202, but on the graph its showing Series 1 Point "244" (10,202)
    Then it appears to be trying to plot the blank cells, since I see data points for Series 1 Point 23 (23,0) or Series 1 Point 7 (7,0)
    Obviously because these cells are showing error of some sort, excel does not see them as blank cells so therefore the chart doesn't see empty cells as gaps since they not empty. For Select Data Source, I even went in and tried to correct it making sure that my B column is my x-values and my C column is my Y values, but I can't figure it out. It has to do with the empty cells, because when I tested is with data X(B2-B7) and Y(C2-C7), which does not have any empty cells, if graphs perfectly.
    :-(

    I hope you can help me!
     

Share This Page