General Topics
Markets
Technical Topics
Brokerage Firms
Company Specific
Community Lounge
Site Support

# Excel or algebra problem

Discussion in 'Trading Software' started by Grant, Nov 10, 2007.

1. ### Grant

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.

File size:
40.5 KB
Views:
102
2. ### ntt

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

3. ### Grant

ntt,

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

May your Maseratis and Ferraris always enjoy cheap petrol.