Algebra or Excel problem

[grantx]

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.

 

[grantx]

Algebra problem sorted.

Grant.

 

[salska]

try

=SUMIF(I3:I19,"<0") for negative numbers

=SUMIF(I3:I19,">0") for positive numbers

 

[grantx]

Salska,

As noted above, problem solved.

I used that as you suggested but I was seeking a soloution to reconcile conflicting values.

But thank you for the suggestion anyway.

Good trading,

Grant.