400116_en-US

liliconcom

Taking column average based on some condition on other column

I am looking for a method to take average of column data based on some condition on another column.

The table below shows what I want to achieve.

 

I want to take time averaged intensity value and tabulate a new column with the averaged value.

The average will be taken based on 'Step' & Group as a trigger i.e all intensity values from same Group & same Step will

be averaged and tabulate once. I tried Col mean function  but it keeps on inserting the averaged value multiple times.

Btw, is it possible to create a new table with summary(averaged value). The table will include the step that has been averaged, the group it belongs to and average value.(just the data in red)

 

Time stamp(s)	Step	Intensity	Group	(Want average) For e.g
1	1	110	A	119.7
2	1	115	A	-
3	1	123.5	A	-
4	1	124	A	-
5	1	126	A	-
3	2	132	B	205.73
4	2	140	B	-
5	2	345.2	B	-
1	2	10	A	18.33
2	2	20	A	-
3	2	25	A	-
.	.	.	.
.	.	.	.

Consumer and Market ResearchRe: Taking column average based on some condition on other column

Thanks Jim.

Re: Taking column average based on some condition on other column

Your question has a couple of ways to be interpreted.

1. Do not include in the calculation of the Group Mean, any value of Intensity > 100

   If( Row() == 1 | Lag( :Group ) != :Group | Lag( :Step ) != :Step,
    Col Mean( If( :Intensity > 100, :Intensity, . ), :Group, :Step ),
    .
   )

2. If the first value for Intensity in the group is > 100 do not calculate the group mean

   If( (Row() == 1 | Lag( :Group ) != :Group | Lag( :Step ) != :Step) & :Intensity > 100,
    Col Mean( :Intensity, :Group, :Step ),
    .
   )

Re: Taking column average based on some condition on other column

Thank you the formula worked. Btw, is it possible to nest this 'If' formula into another 'If' such that this formula executes only when 'intensity' is greater than 100. How will the formula look like then?

Re: Taking column average based on some condition on other column

A slight modification to the formula will give you what you want

If( Row() == 1 | Lag( :Group ) != :Group | Lag( :Step ) != :Step,
 Col Mean( :Intensity, :Group, :Step ),
 .
)