Chart question

[GC]

Hello
I have a data collecting system that automatically goes and polls 4 seperate
systems and populates 1 worksheet for each system. Certain systems will not
always produce data, leaving gaps in the data viewed from a date
prospective. I have 1 chart with 4 series. Before I plot this data, I scan
the 4 worksheets and set the category (X) of my chart to reflect the series
that has the most days of data in it. All data will be displayed in my chart
but will not be charted against the date that it occurred except for the
series I choose to be category (X).

Example:

System 1

Jan 1,data1

Jan 3,data2



System 2

Jan 1,data1

Jan 2,data2

Jan 3,data3



I would use System 2 for the category (X) and system 2 data would be
plotted correctly relative to the date. System 1 will be plotted so data1
and data2 occurred on Jan1 and Jan2. What I need is data1 plotted against
Jan1 and Jan2 be blank and data3 against Jan3. System 2 would have data for
Jan 1, 2 and 3.

I know this is because of how I set my category (X).

Is there a way to tell excel there are 4 category (X) series to this 1 chart
but only display the one that is most dense to cover all data?



Thanks in advance,

Cheers!

 

[joel]

What you need to do is create a new table to use for plotting that has
only one series in it, but has a formula that takes the the correct
column (1 of 4) from your original table. You can use the function
Offset to get the data from one of 4 columns



So if you wanted to get the data the is either 0,1,2,3 from A1 you
would use this

=OFFSET(A1,0,X,1,1)

You can then put a formula into X that determines which column has the
results you are looking for


You can use Chip Pearson's web page to help you get the exact formula
you need. He has a large number of formulas that you can use.

www/cpearson.com

 

[GC]

Thank you Joel.

Gerry