topic Re: Taking column average based on some condition on other column in Discussions

Taking column average based on some condition on other column

Neal85 — Sat, 10 Jun 2023 23:33:42 GMT

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	-
.	.	.	.
.	.	.	.

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

txnelson — Mon, 12 Jul 2021 01:46:48 GMT

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 ),
	.
)

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

Neal85 — Thu, 15 Jul 2021 07:29:26 GMT

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

txnelson — Thu, 15 Jul 2021 08:22:36 GMT

Your question has a couple of ways to be interpreted.

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 ),
	.
)

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

Neal85 — Tue, 20 Jul 2021 04:25:32 GMT

Thanks Jim.