- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 | - |
. | . | . | . | |
. | . | . | . |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 ),
.
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Taking column average based on some condition on other column
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 ), . )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 ),
.
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Taking column average based on some condition on other column
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 ), . )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Taking column average based on some condition on other column
Thanks Jim.