Calculate if a position is increasing or decreasing based on multiple criteria in an excel spreadsheet

Question

I have data in Excel like:

Open or Close?  Fruit   Person  Quantity
Open            Apple   Ann     -21900
Open            Banana  Fred    -1500
Open            Banana  Martha  -300
Close           Banana  Fred     0
Open            Apple   Fred     700
Close           Apple   Ann      0
Open            Apple   Fred    -1000
Open            Banana  Ann     -11000
Open            Apple   Ann     -61000
Open            Banana  Martha  -81000

I need to code in whether the latest transaction based on certain criteria is "opening" or "closing" a position. So, if Ann has -700 bananas and sells 200 more bananas, she now has -900 bananas and is "opening" a position. If she has -700 bananas and bought 200 bananas, she now has -500 bananas, and is "closing" a position.

I need to dynamically populate the "Open or Close?" column. I filled it in up there so people would know what the right answers were.

Code should consider the "Person" and the "Fruit". In words, I imagine the function would be something along the lines of "Take the absolute value of the value of 'Quantity' (we'll call it quantity A). Find the latest row until now where 'person' and 'fruit' match, and if the absolute value of that 'quantity' (quantity b) is greater then the absolute value of the quantity A, the answer should be "open". If not, it should be closed.

Answer 1

This returns what you want with the data you have provided:

=IFERROR(IF(INDEX(D:D,AGGREGATE(14,6,ROW($D$1:D1)/(($B$1:B1 = B2)*($C$1:C1 = C2)),1))<D2,"Close","Open"),"Open")