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.
