sum negative and positive values separately

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bora - 04 Dec 2006 15:59 GMT

Hi,
I have a time series like this:

1995, -.05
1996, -2.1
1997, 3.6
1998, 4.3
1999, -0.7
2000, -0.08

and I would like to sum just the positive and negative value separately
in this way

1995, -2.15
1997, 7.9
1999, -0.78

And I don't know if it is

Reply to this Message

Bernard Liengme - 04 Dec 2006 16:08 GMT

To sum only positive values use =SUMIF(B1:B10,">0") and to sum only negative
values =SUMIF(B1:B10,"<0")
But I cannot see how you get the second tables from the first.
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Hi,
> I have a time series like this:
[quoted text clipped - 14 lines]
>
> And I don't know if it is

Reply to this Message

bora - 04 Dec 2006 16:15 GMT

I know how to use SUMIF,
The second table show the sum of consecutive positive or negative
values at thihe first time of occurrence.
regards

Bernard Liengme ha scritto:

> To sum only positive values use =SUMIF(B1:B10,">0") and to sum only negative
> values =SUMIF(B1:B10,"<0")
[quoted text clipped - 23 lines]
> >
> > And I don't know if it is

Reply to this Message

Dave Peterson - 04 Dec 2006 16:49 GMT

And to get the sum of all the 0's:

=sumif(b1:b10,0)
or
just
0

<well, it made me smile>

> To sum only positive values use =SUMIF(B1:B10,">0") and to sum only negative
> values =SUMIF(B1:B10,"<0")
[quoted text clipped - 23 lines]
> >
> > And I don't know if it is

Signature

Dave Peterson

Reply to this Message

Bernard Liengme - 04 Dec 2006 18:27 GMT

Surely a problem that complex needs SUMPRODUCT <bg>

Signature

Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address

> And to get the sum of all the 0's:
>
[quoted text clipped - 33 lines]
>> >
>> > And I don't know if it is

Reply to this Message

PapaDos - 05 Dec 2006 05:13 GMT

Signature

Regards,
Luc.

"Festina Lente"

> Hi,
> I have a time series like this:
[quoted text clipped - 14 lines]
>
> And I don't know if it is

Reply to this Message

PapaDos - 05 Dec 2006 06:03 GMT

Try this ARRAY FORMULA in D1

=IF( ROWS($1:1) < SUM( --( SIGN( $B$2:$B$6 ) <> SIGN( $B$1:$B$5 ) ) ) + 2,
INDEX($A:$A, IF( ROWS($1:1) = 1, ROW( $B$1:$B$5 ), SMALL( ( SIGN( $B$2:$B$6 )
<> SIGN( $B$1:$B$5 ) ) * ROW( $B$2:$B$6 ), ROWS($1:1) + SUM( --( SIGN(
$B$2:$B$6 ) = SIGN( $B$1:$B$5 ) ) ) - 1 ) ) ), "" )

And this ARRAY FORMULA in E1

=IF( ISNUMBER( D1 ), SUMPRODUCT( ( $A$1:$A$6 >= D1 ) * ( $A$1:$A$6 < IF(
ISNUMBER( D2 ), D2, MAX( $A$1:$A$6 ) + 1 ) ) * ( $B$1:$B$6 ) ), "" )

Drag/Fill down as needed...

Signature

Regards,
Luc.

"Festina Lente"

> Hi,
> I have a time series like this:
[quoted text clipped - 14 lines]
>
> And I don't know if it is

Reply to this Message

PapaDos - 05 Dec 2006 06:13 GMT

These forums are driving me nuts, I have been having trouble posting
correctly tonight...

My last reply's second formula (E1) is NOT an array formula.

An alternative to it is this ARRAY FORMULA in E1

=IF( ISNUMBER( D1 ), SUM( ( $A$1:$A$6 >= D1 ) * ( IF( ISNUMBER( D2 ),
$A$1:$A$6 < D2, 1 ) ) * ( $B$1:$B$6 ) ), "" )

Signature

Regards,
Luc.

"Festina Lente"

> Try this ARRAY FORMULA in D1
>
[quoted text clipped - 28 lines]
> >
> > And I don't know if it is

Reply to this Message