Charting xy scatter of uneven columns of data
 

I have two columns of data, each having a different number of cells. I want
to plot them against each other to create an xy scatter chart. Is there a
way to redistribute the data in the column with the least amount of cells to
match the column with the most cells? The data I want on the x axis was
collected during an experiment once per second and the data on the y axis was
collected 4-5 per second. Both were started and stopped at the same point in
the test. I just need to evenly distribute the data that was collected once
per second over the total number of cells of data that were collected 4-5
times per second.
Thanks for your time

Charting xy scatter of uneven columns of data
 

Hi Mike,

Not sure of what you need here, but it sounds to me like you should
be plotting each data series against their equivalent seconds and not
against each other.
So you would have two series in the same chart each plotted as
Y-axis = data
X-axis = seconds
(or vice versa)

HTH
Martin




"Mike" wrote in message
...
I have two columns of data, each having a different number of cells. I
want
to plot them against each other to create an xy scatter chart. Is there a
way to redistribute the data in the column with the least amount of cells
to
match the column with the most cells? The data I want on the x axis was
collected during an experiment once per second and the data on the y axis
was
collected 4-5 per second. Both were started and stopped at the same point
in
the test. I just need to evenly distribute the data that was collected
once
per second over the total number of cells of data that were collected 4-5
times per second.
Thanks for your time

Charting xy scatter of uneven columns of data
 

On Wed, 9 May 2007, in microsoft.public.excel.charting,
Mike said:
I have two columns of data, each having a different number of cells. I want
to plot them against each other to create an xy scatter chart. Is there a
way to redistribute the data in the column with the least amount of cells to
match the column with the most cells? The data I want on the x axis was
collected during an experiment once per second and the data on the y axis was
collected 4-5 per second. Both were started and stopped at the same point in
the test. I just need to evenly distribute the data that was collected once
per second over the total number of cells of data that were collected 4-5
times per second.

You're a bit screwed there, mate. The simplest I can suggest is some
kind of VLOOKUP function that takes the exact time the x data point was
gathered, and uses it to select the y data point that was collected in
the nearest possible time. In other words, you're sampling every fourth
or fifth y, grabbing the best one you can get.

Alternatively, you might do it the other way round: come up with some
function that finds the last x data point collected before the y data
point, and the first x data point collected *after* the y data point,
and linearly interpolate the two x's to estimate what the exact value
was when the y was collected. If the x curve was smooth enough, I think
I'd prefer that to the procedure I suggested first, because then you get
more points to graph.

Getting the greatest x time that is less than the y time and the
smallest x time that is greater than the y time would be the work of
functions MAXIF and MINIF, the equivalents of COUNTIF, if such functions
existed in Excel. Since they don't, you'll have to fake it with array
functions like this

{=MAX(IF(X_RANGE<=Y_VALUE,X_RANGE,FALSE))}

where Y_VALUE is the y under consideration, and X_RANGE is the entire
range of x values. You don't have to name these, I'm just doing it
because I don't know what cells you're using. That would be a MAXIF
function, and Excel help can tell you how to get the curly brackets that
make it work as an array formula.

Having got the two times, you can then get the two x values using
VLOOKUP, and use those four parameters to calculate an exact x value
corresponding to that y. Ultimately, as with so many enquiries that
come to this newsgroup, this ends up not really being a question for
m.p.excel.charting, but one for a maths newsgroup for the basic
arithmetic, and the m.p.excel.functions newsgroup for the
implementation. Get that sorted, and the actual charting becomes
trivial.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Charting xy scatter of uneven columns of data
 

You're a bit screwed there, mate.

I passed on answering this post, because I couldn't come up with a
sufficiently elegant response. Thanks, Del, for helping us out!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
On Wed, 9 May 2007, in microsoft.public.excel.charting,
Mike said:
I have two columns of data, each having a different number of cells. I
want
to plot them against each other to create an xy scatter chart. Is there a
way to redistribute the data in the column with the least amount of cells
to
match the column with the most cells? The data I want on the x axis was
collected during an experiment once per second and the data on the y axis
was
collected 4-5 per second. Both were started and stopped at the same point
in
the test. I just need to evenly distribute the data that was collected
once
per second over the total number of cells of data that were collected 4-5
times per second.

You're a bit screwed there, mate. The simplest I can suggest is some kind
of VLOOKUP function that takes the exact time the x data point was
gathered, and uses it to select the y data point that was collected in the
nearest possible time. In other words, you're sampling every fourth or
fifth y, grabbing the best one you can get.

Alternatively, you might do it the other way round: come up with some
function that finds the last x data point collected before the y data
point, and the first x data point collected *after* the y data point, and
linearly interpolate the two x's to estimate what the exact value was when
the y was collected. If the x curve was smooth enough, I think I'd prefer
that to the procedure I suggested first, because then you get more points
to graph.

Getting the greatest x time that is less than the y time and the smallest
x time that is greater than the y time would be the work of functions
MAXIF and MINIF, the equivalents of COUNTIF, if such functions existed in
Excel. Since they don't, you'll have to fake it with array functions like
this

{=MAX(IF(X_RANGE<=Y_VALUE,X_RANGE,FALSE))}

where Y_VALUE is the y under consideration, and X_RANGE is the entire
range of x values. You don't have to name these, I'm just doing it because
I don't know what cells you're using. That would be a MAXIF function, and
Excel help can tell you how to get the curly brackets that make it work as
an array formula.

Having got the two times, you can then get the two x values using VLOOKUP,
and use those four parameters to calculate an exact x value corresponding
to that y. Ultimately, as with so many enquiries that come to this
newsgroup, this ends up not really being a question for
m.p.excel.charting, but one for a maths newsgroup for the basic
arithmetic, and the m.p.excel.functions newsgroup for the implementation.
Get that sorted, and the actual charting becomes trivial.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.