How to plot discontinuous/mixed data sets as scatter graphs?

**Anubeon** · 08-20-2008, 08:25 AM

Greetings All!

I'm in need of a function or methodology which would allow me to select data series from discontinuous data within a data set. I'm sure that none of you have a clue what I'm talking about so I'll illustrate

Please Login or Register  to view this content.

In this example I wish to plot all of the Retention Factors for the Compound TRP as a single series on a scatter graph. If there perhaps a way of using VLOOKUP to achieve this, if so please elaborate since it has been a while since I used VLOOKUP (or in fact anything more complicated than AVERAGE and STDEV).

Please bear (sp?) in mind that my data set is extremely long and while it is possible for me to manually select disjointed data such as these when selecting a scatter graph data series it's frankly an ar*e ache. :P

Any help you could provide would be greatly appreciated.

Kind Regards,

Nubey.

**sweep** · 08-20-2008, 08:33 AM

Select all the data, go to Data > Filter > Autofilter.

Click the dropdown arrow in the Compound cell, and select TRP.

Then plot the graph

**Anubeon** · 08-20-2008, 08:43 AM

My word that's so simple; I feel like a complete moron now. Me thinks I need to get reacquainted with excel soon.

Anyway thank you kindly to sweep for saving me hours of needless point & clickage.

Best Wishes,

Nubey.

**sweep** · 08-20-2008, 09:15 AM

You're welcome. Happy chromotography.

**Anubeon** · 08-20-2008, 01:46 PM

Hello again,

I've identified a slight problem with sweeps approach (using filters) in that the selected data series only remains valid so long as the filter remains applied within the original spread sheet.

In MS Excel 2007 filters seem to work by hiding rows which do not fit the criterion of the filter. Thus:

MINO
TRP
TERB
CAF
MINO
TRP
TERB
CAF
etc...

becomes

TRP
TRP
etc...

when I invoke a filter for TRP only. If I then select the column (which at this point appears to comprise only the TRP dataset) and plot a scatter graph the result is as desired. However when I revoke the above mentioned filter the hidden rows reappear and those datasets that reside between the two extremes of the TRP dataset are added to the scatter graph dataset thus the desired dataset

TRP
TRP
TRP
TRP
etc...

becomes

MINO
TRP
TERB
CAF
MINO
TRP
TERB
CAF
MINO
TRP
etc...

After I revoke the filter. This of course renders the filter approach (at least so far as MS Excel 2007 is concerned) a non option since I wish to plot each dataset individually (i.e. not just TRP) which will require me to invoke/revoke several filters.

Any other ideas? I could always use scattered columns for my retention factors although that's not as asthetically pleasing.

Kind Regards,
Nubey

**Andy Pope** · 08-21-2008, 04:51 AM

Charts have an option to plot visible cells only, which is how the filtering works to create the chart you need. That is until you remove the filtering.

The attached uses a helper column to determine which records to use.
This is then used to construct chartable data.

The final thing you need is a named range to limit the amount of data, if any, used in the chart.

You mentioned scatter charts but with only 1 value in your data example it's not clear what the X value should be.

Post back if you need more info.

**Anubeon** · 08-21-2008, 05:01 AM

Hi Andy,

The x axis is a relative time plot.

That all seems a little too complicated, I guess I'll just have to sort the columns alphabetically to make the datasets continuous.

I was hoping to keep the data ordered chronologically but meh.

Thanks for the info though Andy, it could come in handy some day.

Regards,

Nubey.

**Andy Pope** · 08-21-2008, 05:13 AM

The chart data is in the same order as the original.

Sorting the data will make some parts easier. What happens when you have new records and the start and finish positions of a group changes?