I’d like some advice on improving my data analysis workflow. I work in a chemistry lab and am beginning a program which will generate a large amount of time-course data. I’ve provided a simpler example to try and explain the current workflow. Using Excel 2016 but could get my hands on O365 if needed.
Say we run a series of experiments determining the speed a car accelerates around a given track. We have the time and velocity as the measured variables, and can calculate the acceleration and distance traveled based on that data. The data is collected in a program, processed in a workbook and several parameters (max acceleration, max velocity, total distanced traveled) are also calculated.
Say I have two experimental data sets for a Honda Odyssey and a Ferrari and I want to compare the velocity, acceleration and distance between the two data sets (see example data sets in the first two screenshots).
Ferrari.png
The fastest method I have devised so far is to place both charts in excel, copy the series from Honda plot to the Ferrari plot, creating a new plot with both series. I can compare the Max Speed, Acceleration, etc by then pasting in the appropriate data into a table and making a bar chart.
Data Comparison.png
This method is fine for a handful of experiments, but for dozens of conditions with multiple trials for each (40-100 experiments, 8 to 10 plots each, 5,000+ data points/plot), creating these comparison plots becomes unwieldy. Previously I have created giant documents with every data set in it, and just powered through it with formulas. It ain’t fun.
Ultimately, I am looking for a solution in which:
- I can use something like a splicer to select several experiments to compare, and a x-y scatter plot is updated (say velocity vs time) comparing the selected experiment sets.
- I can use power query to keep a database of completed experiments by adding files to a folder (which then updates the database)
This would allow me to quickly compare data sets without having to hunt down/copy paste the original data. I thought that Pivot Charts would be my answer, but they don’t allow scatter plots (wtf) so they are effectively useless for this application. I don’t need a fleshed out solution, any advice pointing me in the right direction would be appreciated. I know I am probably pushing excel to the limits with this, but Excel is so much easier to integrate into reports and presentations than R/MatLab/Mathematica.
Bookmarks