484020_en-US

liliconcom

Conditional formula

In the following table, I would like to create a column in which each sample value is divided by the average of controls 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!

 

Data TableScriptingRe: Conditional formula

I got what I want indirectly, but would still appreciate to know the right way to do it.

 

This is what I did

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

2 . Then multiply "Type ID" with Col Mean 

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

 

Re: Conditional formula

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

 

 

 

Re: Conditional formula

Thank you very much! It is very helpful.