|
||||||||||||||||||||
|
|||||||||||||||||||||
|
|||||||
| Notices |
 |
|
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
08-20-2008, 09:25 AM
|
|||
|
|||
How to plot discontinuous/mixed data sets as scatter graphs?
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 Code:
Compound | Retention Factor
----------------------------------------
MINO 0.122
TRP 0.234
TERB 0.455
CAF 1.223
MINO 0.131
TRP 0.256
TERB 0.501
CAF 1.119
MINO 0.119
TRP 0.246
TERB 0.478
CAF 1.299
etc...
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. 
|
|
#2
08-20-2008, 09: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
|
|
#3
08-20-2008, 09: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.
|
|
#5
08-20-2008, 02:46 PM
|
|||
|
|||
|
Problem
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
|
|
#6
08-21-2008, 05: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.
|
|
#7
08-21-2008, 06: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.
|
|
#8
08-21-2008, 06: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?
|
|
| Bookmarks |
New topics in Excel Charting
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| If statements using multiple data validations sets | stuxtruth | Excel Worksheet Functions | 3 | 07-19-2008 09:12 AM |
| How can I speed up this slow macro? | rs2k | Excel Programming | 6 | 07-18-2008 09:34 PM |
| reconcile 2 data sets with multiple variables | Brigitte | Excel Worksheet Functions | 3 | 09-07-2007 05:53 PM |
| scatter plot, get units of measure from a worksheet | JustJill54 | Excel Charting | 1 | 02-26-2007 05:11 PM |
| Using a formula (I think) to copy 'raw data' into a 'scatter chart data' sheet | bsmith69 | Excel Worksheet Functions | 1 | 09-28-2006 03:07 AM |
Linear Mode
