Inter Linking Several columns

[economicaas]

Hello Add

New member here and trying to work with excel. I am sorry if I make mistake the way, I am really new to this.

What I am trying to do is: Inter link two order books ( as in an exchange) of two currency pairs; using price and volume. I have tried several times to work out a formula, but if it works for a particular situation it does not work when I change the volume. ( I am testing out my idea on excel, however, all of this will actually be on an online exchange)

I hope someone good with numbers can help me here:

Here is a preview of the issue I am facing

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Comment[/TD]
[TD]Currency A/C Price[/TD]
[TD]Currency A/C Volume[/TD]
[TD]Currency B/C Price[/TD]
[TD]Currency B/C Vol[/TD]
[TD]B/A Price[/TD]
[TD]B/A Volume[/TD]
[/TR]
[TR]
[TD]Sell order 1[/TD]
[TD]0.00002492[/TD]
[TD]11,000[/TD]
[TD]0.00000663[/TD]
[TD]10,000[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sell order 2[/TD]
[TD]0.00002491[/TD]
[TD]140,000[/TD]
[TD]0.00000662[/TD]
[TD]43127[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Market Price
3[/TD]
[TD]0.00002490[/TD]
[TD]-[/TD]
[TD]0.00000661[/TD]
[TD]-[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Buy Order
4[/TD]
[TD]0.00002489[/TD]
[TD]10,000[/TD]
[TD]0.00000661[/TD]
[TD]100,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Buy Order
5[/TD]
[TD]0.00002487[/TD]
[TD]50,000[/TD]
[TD]0.0000066[/TD]
[TD]400,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Now what I am trying to do is somehow formula these price to B/A. Price at A I calculate using =0.00002491/0.00000661 which comes to 3.7685 B for each A. However, the volume at this price is limited either by the 100,000 volume of B or 140,000 volume of A. So I use the formula =MIN((E2*F2)/G3, H4) giving me answer of 100,000. Meaning only 100,000 of B are available, at this price. Since 100,000 is all out, I need to move automatically to the row beneath containing 400,000 of B. At the same time, I need to calculate how much of 140,000 of currency A has been left for the next price at point B.
At point B, system automatically realized that all 100,000 at H4 has been used. And we have around 113,464 left out of 140,000 (F2) (also calculated with formula). So now next price should be: =E2/G5 and volume here needs to be minimum of H5 or left over of F2 converted in currency B.

I need formulas such changing the the value at H4 to 1,000,000 the system realizes that now limit of volume comes from F2 now and max vol shown in K2 is now limited to currency A volume converted into currency B.


I am sorry if I made it complicated, I will be more than happy to clarify any details if some one is kind enough to help.