How can I use a column formula to return the value of a specific row in another column?Here is an example data table with the 4th column "Desired Formula Column" showing the outcome I am trying to get.
| Group | Sample Number | Data Value | Desired Formula Column |
| A | 1 | 35 | 35 |
| A | 2 | 2 | 35 |
| A | 3 | 1 | 35 |
| B | 1 | 22 | 22 |
| B | 2 | 4 | 22 |
| B | 3 | 2 | 22 |
| C | 1 | 40 | 40 |
| C | 2 | 2 | 40 |
| C | 3 | 3 | 40 |
I am looking for a column formula that will enter the data value for sample 1 for each group.
Any help appreciated!
Re: How can I use a column formula to return the value of a specific row in another column?Hi @user8421
this is a fun question try adding a column to the table with this formula.
If( :Sample Number == 1,
:Data Value,
Lag( :Column 5, 1 )
)
this is not robust but if you do not change row order you should be fine. otherwise just delete the formula once you have the numbers where you want them.
Re: How can I use a column formula to return the value of a specific row in another column?Hi,
Here is a simple formula that I use all the time:
If( Row() == 1,
:Data Value,
If( Lag( :Group, 1 ) != :Group,
:Data Value,
Lag( :Desired Formula Column, 1 )
)
)
