Conditional formula

[#1]

In the following table, I would like to create a column in which each "Sample" value is divided by the average of "Control" grouped by X.

For examples, each "Sample" in "X1" will be divided by average of "Control" from "X1", each "Sample" in "X2" will be divided by average of "Control" from "X2".

I can get the mean of "Control" by creating a formula that calculates Col Mean grouped by "X" and "Type". 

How can I create a formula that spits out the averages of "Control" for all the rows within each "X" value? In this case 10 for all the rows contain X1, and 1000 for all the rows contain X2.

The data file is attached. 

Thank you for your help!

[#2]

I got what I wanted indirectly, but would still appreciate to know a more direct way to do it.

This was what I did

1. I created a conditional formula in :"Type ID", assigning "Sample" as 0 and "Control" as 1

2 . Then multiplied "Type ID" with :"Mean Y by X and Type"

3.  created a new column to fill every row with ColMax grouped by "X"

[#3 · Accepted solution]

Hi GroupSquareWolf-

I love working with these types of formula columns, they are like puzzles!

Here is a formula that works for your example. It essentially does the same thing that your multiple columns do, but in a single column. It only uses the Y, X, and Type columns without having to build any additional calculation or counter columns.

I am sure there are other solutions out there but here is the one I came up with:

It is saved as "New Formula" in the data table.

Hope this helps!

-Scott

[#4]

Thank you very much! It is very helpful.