Search code examples
excelgraphdata-cleaning

Excel data - cleaning data with multiple values for numerous instances

I have a data set which is related to force applied vs distance traveled.

When the data was created the measurement software has provided multiple values for distance traveled as the force increases, then in some cases the data has no values for distance at the force values.

I have several data sets which look like this.

The data looks like this

Raw data

I want to 'clean the data so I can create a graph with all 3 samples in columns the same height so it is easy to edit and make scatter graphs from.

I tried to clean the data by using VLOOKUP to create a column of force values at each 0.5N, but when I do this I end up with a large table that has lots of missing data points, when I make the graph from this there are lots of blank areas which don't seem to plot correctly.

The VLOOKUP data looks like this

VLOOKUP combined data

The graph looks like this

VLOOKUP graph

Is there a better way to do this which will give me a better looking data set which is better for creating a graph from?

I have about 30 sets of data, so any info that you have would be greatly appreciated.

Solution

  • Why make the columns equal length.

    If you plot the three samples with the data as given, an XY graph should look OK:

    enter image description here

    If there's some other reason to make the columns equal length, I'd "fill in the blanks" using the FORECAST or GROWTH functions, or use a trendline.