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

ac527 — Tue, 26 Nov 2019 04:50:29 GMT

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

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

txnelson — Tue, 26 Nov 2019 05:45:06 GMT

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

The above formula should work

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

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

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