Graph Coordinates in Excel 2011

[Ice~Queen]

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!

 

[Cybbe]

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.

 

[GrendelsBane]

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.

That would work beautifully. But you could just use the "Text to Columns" command (in the Data Menu) using the ":" as the delimiting character.

 

[Ice~Queen]

That would work beautifully. But you could just use the "Text to Columns" command (in the Data Menu) using the ":" as the delimiting character.

@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?

 

[GrendelsBane]

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.

 

[Ice~Queen]

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.

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!

 

[GrendelsBane]

Does this help:

http://www.mrexcel.com/forum/showthread.php?t=14040

?