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:

    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.

  2. ntt


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



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

    May your Maseratis and Ferraris always enjoy cheap petrol.

    Good trading and best wishes,