Choose Language Hide Translation Bar
ac527
Community Member

Formula to average of two cells in a column if a cell is same between rows

I am trying to write a script or formula that will average two cells, at times t and t-1 stored on different rows, if the item ID matches. I think the steps would be as follows

If Item row 2 = Item row 1

(value row 2 + value row 1)/2

store result in average row 2

else leave blank

 

Please see the table below for an example. I put the expected answers in the Average column. I am fine with a scripting answer. Thank you.

 

 ItemDateValueAverage
1AA02/28/201912 
2AA05/31/20191413
3AA08/31/20191615
4AB03/31/201018 
5AB06/30/20102019
6AB09/30/20102221
7............

 

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Formula to average of two cells in a column if a cell is same between rows

If(:item == lag(:item), sum(:value, lag(:value))/2);

The above formula should work 

Jim

View solution in original post

1 REPLY 1
txnelson
Super User

Re: Formula to average of two cells in a column if a cell is same between rows

If(:item == lag(:item), sum(:value, lag(:value))/2);

The above formula should work 

Jim

View solution in original post