On Tue, Sep 23, 2008 at 2:47 AM, Adrian Try <[EMAIL PROTECTED]> wrote:
> Hi Mark - I'm just trying to understand and simplify things here. I'm sorry
> if my comments are a bit thick.
>
> I need to get the sum of column 1 * column 2 but only when the value
>> in column 1 is either negative or positive.
>
>
> If the value is positive or negative, you want to multiply the values in the
> two columns.
>
> But what if the value is zero. You don't want the product to be included in
> the sum? But the product would be zero, and adding zero would be the same as
> not adding the product at all.
>
> I'm sorry if I've misunderstood, but if I'm right, you want to sum the
> products whether they are positive, negative or zero. That sounds a whole
> lot simpler.
>
> Adrian
>
Hi Adrian,
No, I really don't want the sum of them all, I want the sum of the
group that has a negative value in the first column, and I want a
second sum of the group that has a positive value in the first column.
This data is stock market stuff. the first column is the number of
option contracts I have bought or sold. the second column (and the
3rd, 4th, & 5th columns) are parameters that describe the way these
options are working. When the first column is negative those are
option contracts I've sold to someone else. The parameters are a
measure of how much they are working against me. When the column is
positive those are contracts I've purchase and the parameters measure
how they are working for me. What I was after was a single number for
my side of the trade comprised of everything I own (positive in column
1) and a second number for the other side of the trade.
The input from Brian, Joe, Ely and others was to use these
equations entered as an array functions:
My side long -> {=SUM((A1:A4>0)*A1:A4*B1:B4)
Their side short -> {=SUM((A1:A4<0)*A1:A4*B1:B4)
which indeed does work. I don't understand why it needs to be an array
function but I can learn about that over time.
There are columns with '0' in the first column which do not get
included in either summation but that's OK since they don't contribute
anything anyway.
Hope this helps explain what I'm doing and why. If you have any
interest in option trading and the actual content of the columns then
check out the use of Black-Sholes equations.
Cheers,
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
