How to ue offset function with average function

Hello experts,

Many thanks in advance for your helps with number of questions I have on my data(every treatment has 15 replicates). Here is how my data looks like.

data.png

Could I get cormula for each of my questions below?

- I want to have G in 3rd column if Raw data(2nd column) is greater than average-1.5*std or smaller than average+1.5*std. B if not.
- I want to have Raw data in 4th column(Filtered data) if 3rd column is G.
- I want to have averaged data in 5th column(Averaged filtered data) with Filtered data for every 5 rows.

Please attach a workbook. Nobody will want to manually type in all the data you show in your attached image.

To attach a file, first make sure to remove any sensitive/proprietary data and then click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.

Also here are some questions:

1)

"every treatment has 15 replicates".

I count 15 rows of "cont" but 18 rows of "trt1". What do you mean by 15 replicates?

2)

"if Raw data(2nd column) is greater than average-1.5*std".

What is "std" - standard deviation?? What are you averaging? - the raw data in all of the "cont" rows?

3) "

want to have Raw data in 4th column(Filtered data) if 3rd column is G"

. Rows 7 and 15 in your example don't seem to comply with this rule?

Sorry for any mistake and thanks for the reply. To answer your questions,

- That’s my mistake. Trt should have had 15 rows as well.
- Yes. I meant standard deviation. I want to average 15 replicates.
- I made another mistake while making an example dataset.

Please add a workbook as requested (not a picture). Thanks

OK, As I had begun looking at this anyway, I did the typing.

Reference the following picture when reading the following description:

treatment.png

I need one helper column F for the averaged raw data for each treatment and a second helper column G for the standard deviation of the raw date for each treatment.

Average in column-F is calculated as follows in F2 and copied down

Formula:

Please Login or Register  to view this content.

Standard deviation is calculated in column-G As there is no StddevIF formula I use row numbers to determine the correct range of cells to use for the stddev. This works only because each treatment is always exactly 15 rows long. TitleRows is a named cell K1 set to 1, because I wanted to make this easy to change as I don't know the OP's desired layout.

In G2 and copied down:

Formula:

Please Login or Register  to view this content.

Column-C gets "G" if the raw data is within 1.5 standard deviations from the average, "B" otherwise. In C2 and copied down:

Formula:

Please Login or Register  to view this content.

Column-D simply gets only the data that is within 1.5 standard deviations from the average. In D2 and copied down:

Formula:

Please Login or Register  to view this content.

Column-E provides an average of the data that is within 1.5 standard deviations from the average for each set of five rows. Again, row numbers are used to determine where this column should be blank and where an average should be displayed. In E2 and copied down:

Formula:

Please Login or Register  to view this content.

That's it! See the attached workbook