designing template for scatter graphs

**bagullo** · 09-30-2009, 11:58 PM

here is my problem

I have to scatter graph a variable number of series, which in turn have variable number of elements, each with its x and y coordinates, something like (A series has 2 elements, B series has 1 element)

series xvalue yvalue
A 1 2
A 3 2
B 5 4

and so on.

so far the only way I managed to do that is by hand -- but there MUST be a better way, it is really time consuming

so here is what I thought:

copy the values of the each series into separate tables, and then select the largest possible size of that series. Ideally empty cells would be ignored and cells with values will be plotted. And through some pivot tables tweaking I could even get the names of the series to update accordingly.

however in order to separate the series I have to use conditional formulas and put empty string on the cells that shoud be empty, and that seems not to play nice with the scatter graph, which then does not even recognize the x coordinate of each point and puts them just as series.

any clue on how to work arround the problem?

I tried to do copy-paste just values, but it does not quite solve the problem

Could you recommend me a different strategy to accomplish what I'm trying to do?

the only think I could come up with was to assign all the empty cells certain coordinates (say 0,0) so that all "undesired" points will go there, and then on the graph just cover them with some white shape or so, but that's quite dirty indeed :D

thanks in advance!