How to automatically apply fixed set of calculations to data of variable column and r

**francis** · 2015-05-09, 04:20 AM

Hello,

I have raw data that ha variable set of rows and columns with a unique time and ID (see attached file). I need to apply a fixed set of calculations to the data but doing manually is huge pain. How would I automate this process.

Best,

Francis

**jaslake** · 2015-05-09, 11:48 PM

Francis

Welcome to the Forum!!!

Please include a Link to other Forums on which you've posted this Tread...it's a Forum requirement.

I count three thus far...

**Herbds7** · 2015-05-10, 01:30 AM

I have a neat solution.
Unfortunately you are a cross poster.
Here is a teaser:
http://www.mediafire.com/view/d3w8bi...o/05_09_15.pdf

**francis** · 2015-05-10, 03:03 AM

Hi,

Sorry for cross posting and I hope someone can still help with my data analysis for a biomedical research study that I am working on.

Sincerely

Francis

excelforum.com/showthread.php?t=1081719&p=4068129&posted=1#post4068129
ozgrid.com/forum/showthread.php?t=194665&p=745558#post745558

**Herbds7** · 2015-05-10, 04:56 AM

Excel 2010
What I would like to see is just the raw data
and a text description of the desired result(s).
If you just want the SNR, then a couple of new formulas can be written
and all of your present formulas can be eliminated.
Maybe my charts can illuminate this.
http://www.mediafire.com/view/aag9q7.../05_09_15.xlsx

**francis** · 2015-05-10, 02:21 PM

Hi,

If you plot signal versus time for 1 row of data you will see a peak in the signal (Columns C

E). I need to calculate a drift correction factor (Column), average, standard deviation, min, max, median, etc of the first 7 milliseconds of the background, the top of the signal peak and the time period when the signal decays (Columns DG

O, DQ

Y, EA:EI).

After this I apply the drift correction factor in column DG to every row of the data as the signal decreased 58% over the 20 period of the experiment. I then repeat the same calculations on the right side the data (IQ:JE).

Best,

Francis

**Herbds7** · 2015-05-10, 02:50 PM

You recorded data at an interval of 70 seconds, at best.
To do an analysis at the millisecond range, 10000s of data points
at that continuous rate are needed.

**francis** · 2015-05-10, 03:14 PM

Sorry, that was a typo on my part. The time is in seconds.

**Herbds7** · 2015-05-10, 07:50 PM

That helps a lot.
Now you need only an additional ~100 data points,
at one second intervals,
around record 4 (1:45:23) and record 19 (15:54:40)

**francis** · 2015-05-10, 08:18 PM

Hi.

This is data from a quality control measurement which was taken before and after sample measurements. The first step is to obtain calculations of the background and signal. The pivot table and cool charts you made will need to be applied to 1000s of lines of data which vary in length of time and the no. of lines per sample id.

Best,

Francis