Choose Language Hide Translation Bar
Highlighted
Level I

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

 Item Date Value Average 1 AA 02/28/2019 12 2 AA 05/31/2019 14 13 3 AA 08/31/2019 16 15 4 AB 03/31/2010 18 5 AB 06/30/2010 20 19 6 AB 09/30/2010 22 21 7 ... ... ... ...

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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
``If(:item == lag(:item), sum(:value, lag(:value))/2);``