Applying a formula to triplicate samples with a large dataset

**a_coe** · 01-30-2013, 01:45 PM

Hi,

I have an experiment where I have triplicate samples (A-C) per time point (in this case time point is in hours) and I need to calculate the average and standard deviation and place these in a either a new worksheet or in a clear area on the same worksheet.

For example I have time points 0, 5, 10, 15, 20, 25 as headers in 18 columns and I have triplicate samples (A-C) per time point (so there are 3 wells for time point zero and they contain data for A, B, and C). I would like to make one column for each time point (0, 5,...etc) that displays the average and st dev away from the dataset.

Currently I have been manually copying and pasting the equation to the new area on the worksheet for the avg table, but the formula does not shift every three column wells. Instead it shifts just one well. How can I ask it to apply the formula every 3 wells? Right now I have to manually move the fill handle over to the triplicate wells I need, but I would like to cut out the potential for human error on my large datasets.

I saw a thread about using OFFSET, but I think that would only apply if I had already calculated the averages and needed to move them to a new area. I'm trying to calculate and move the data at the same time.

Thanks!

**ChemistB** · 01-30-2013, 02:00 PM

Assuming your data to be in Sheet1!B2:O500, your headers in B1:O1, Sample ID in A2:A500, Your new averages in Sheet2!B2:G500, 0,5,10,15,20,25 in B1:G1, Sample ID's in A2:A500

In Sheet2B2
=AVERAGE(OFFSET(Sheet1!$A$1,MATCH($A2, Sheet1!$A$2:$A$500,0),MATCH(B$1, Sheet1!$B$1:$O$1,0),1,3))
Similar formula for STDEV
Questions? A working workbook would help me be more specific.

**a_coe** · 01-30-2013, 05:23 PM

Hi,

I was trying to do this on the same page if possible. I assume there is a way and I tried based on the formula you suggested less the term "MATCH". It didn't work for me so clearly I'm doing something wrong. I have included a sample worksheet that has triplicate reps (A-C), three time points, and data for those time points. I also included a little table where the averages and stdev would go as this would be similar to my layout if I can stay on the same worksheet and do this. If not, I can try your method for moving onto a second worksheet.

Thanks!

**ChemistB** · 01-31-2013, 09:30 AM

My formula will work on a single sheet (is actually simplier)
I modified your sheet a little. I assume there will be more than 1 sample set so added a sample 1 and sample 2 and then in the new table, I used Sample 1 and Sample 2 in Col E.
To get AVERAGE in F10 for example,

Formula:

Please Login or Register  to view this content.

To Get Standard deviation in F11

Formula:

Please Login or Register  to view this content.

You would need to copy and paste those formulas down every other row, a pain in the neck if you have lots of samples. I combined the formulas with an IF statement that looks to see if the row is odd or even and then does the appropriate formula

Formula:

Please Login or Register  to view this content.

Alternately, you could set it up a little different as I did in the table in E17. Lots of options depending on how much data you have and how important it is to have the average and Std dev together.

**a_coe** · 02-01-2013, 11:32 AM

Hi,

So I'm still confused and am trying to figure out the formula you sent for just one sample set. If I can understand how it applies to one row then I can worry about multiple sample sets (rows). I tried to break down your formula to what I thought should work for one sample set, but it is not working for me.
What is wrong with the following equation?

=AVERAGE(OFFSET($E$5,MATCH($E10, $E$6,0),MATCH(F$9, $F$4:$N$4,0),1,3))

I tried this by cutting and pasting just one of the sample sets and applying this equation (see worksheet...note I obviously had to use different row numbers since it is below your example), but it wouldn't work for me. When I cut out the "If" and the "stdev" from the equation on your example (see the formula for well F10) it still worked. So it doesn't make sense that it wouldn't work for "MY ATTEMPT" (well F31) unless I have entered the equation in wrong and/or I'm missing something.

Thanks so much for your help!

**ChemistB** · 02-01-2013, 11:58 AM

No problem

Formula:

Please Login or Register  to view this content.

Your issue is with the first MATCH Function. This is the number of rows you want to count off from your original cell (E26). I used MATCH because I figured there will be multiple samples and I put the Sample ID's into Col E to search for. Your formula is trying to Match what's in E31 ("Avg") with what's in E27 ("Sample 1"). They don't match. With a single sample, you can remove the whole MATCH Function and put a 1 here, or better yet, start in E27 and put a zero here. The rest of your equation is fine.
Hope that helped.