topic Re: Conditional formula in Discussions

Conditional formula

GroupSquareWolf — Sat, 10 Jun 2023 23:47:55 GMT

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!

Re: Conditional formula

GroupSquareWolf — Wed, 04 May 2022 10:23:38 GMT

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"

Re: Conditional formula

scott_allen — Wed, 04 May 2022 12:51:41 GMT

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

GroupSquareWolf — Wed, 04 May 2022 13:42:16 GMT

Thank you very much! It is very helpful.