Find match and subtract pair

I have 2000 rows of data, arranged by date and time. The first column contains the 'buy' or 'sell' price, the second column contains the date and the third contains the time. A fourth column contains an indentifier to link the 'buy/sell' pairs (just a number so that both rows of the same buy/sell pair have the same number).

I need to write a formula that will match the column pairs and subtract the 'sell' price from the 'buy' price. I'm not sure if 'SUMIF' is the correct base - if it is I haven't been able to sort it.

Sample of data:

1.0477 01/01/2003 19:27 442
1.0477 01/02/2003 1:37 622
1.0370 01/03/2003 2:10 814
1.0387 01/03/2003 6:09 814
1.0395 01/03/2003 9:02 815
1.0413 01/03/2003 10:40 815
1.0424 01/03/2003 12:17 816
1.0423 01/05/2003 18:19 1
1.0442 01/05/2003 23:58 816
1.0442 01/05/2003 23:58 1
1.0437 01/06/2003 2:40 104
1.0453 01/06/2003 3:14 104
1.0472 01/06/2003 4:28 105
1.0444 01/07/2003 1:13 271
1.0472 01/08/2003 12:09 271
1.0509 01/08/2003 13:01 442

There is probably a nicer solution, but it works:

B/S/O: =CHOOSE(1+MAX((D2=$D$2:$D$17)*(ROW()<>ROW($D$2:$D$17))+(D2=$D$2:$D$17)*((B2&C2)<($B$2:$B$17&$C$2:$C$17))),"Open","Sell","Buy")

+/-: =SUM(IF((E2="Sell")*(D2=$D$2:$D$17)*($E$2:$E$17="Buy"),A2-$A$2:$A$17,""))

Price.........Date.........................Time........Order.......B/S/O.......+/-
1.0477.....01/01/2003..............19:27.......442..........Buy..........0
1.0477.....01/02/2003..............01:37.......622..........Buy..........0
1.037.......01/03/2003..............02:10.......622..........Sell..........-0.0107
1.0387.....01/03/2003..............06:09.......814..........Open.......0


Ola Sandstrom


Note !
Since Both formulas are Array formuls you Must end the formulas by holding down
Ctrl+Shift and then press Enter. Othervise B/S/O= will be wrong and +/- will be #VALUE!

The idea was to...
1. see what kind of order it was. If Time1<Time2-->Buy,Sell Else Open.
2. Only calculate +/- after Sell

Formula(s) works like a charm! Except...every time I drag the formula along, Excel crashes and closes. Even dragging it a few rows without crashing, it crashes when I try to save it.



Thanks for sorting that for me.

Thanks for the feedback. I'm glad you like it.
Can't tell you Y Excel crashes, but Array formulas are quite cpu demanding though.
Maybe some of the veterans can tell us why?

Ola

Managed to get it all sorted - in that everything is in place and saved. I suspect the issue is indeed hardware related.

Sadly, the results of the countless calculations are sending me back to the drawing board with my buying and selling.