Excel or algebra problem

First, a possible algebra solution (my solution doesn’t seem to work. See attachment).

Total volume = 27,165
net volume (buys – sales) = -829.

How do you calculate buys and sales from the above figures; plus the same if net is positive?

Possible Excel problem/solution.

Please refer to attachment (only a small part is shown). A bit untidy but this is the “rough idea” sheet.

Col G = last price
Col H = volume
Col I = volume bought (-sold)

The table (Price, volume distribution), columns N – S is self-explanatory.

Rows 17 focus (ignore others).

Col O (number of), trades is determined by array function, eg trades for 108.170 = 742:

{=FREQUENCY(G3:G20000,N4:n1000)}
The figures are correct.

net (-829) is:

=SUMIF(G$3:G$20000,N17,I$3:I$20000). Again, the figures are correct.

I need to determine the figures for buys and sales (the totals at Q1 and R1 should correspond with I1 and I2 and are just a check).

Is it possible to use the =SUMIF()as above (with modifications) to extract only positive and separately, negative, figures from column I? I’ve tried, but in vain as you can see . If an algebraic solution is possible, we don’t need to bother.

Thank you in anticipation.

Grant.

 

buys = .5*(total volume + net volume)
sales = .5*(total volume - net volume)

 

ntt,

That's it. You're a Gentleman, Sir.

May your Maseratis and Ferraris always enjoy cheap petrol.

Good trading and best wishes,

Grant.