Subrange

In range a (part of the column) I have series of values. I want to calculate
sum of positive and negative increments. Formula
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) works O.K. apart from the
fact that I should skip first row. Instead of a I should calculate with
subrange of a - range a without a first row.

If anybody can help...

Thanks, Oscar.

Can you post an example and what result you expect?

--
Biff
Microsoft Excel MVP


"Oscar" wrote in message
...
In range a (part of the column) I have series of values. I want to
calculate
sum of positive and negative increments. Formula
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) works O.K. apart from
the
fact that I should skip first row. Instead of a I should calculate with
subrange of a - range a without a first row.

If anybody can help...

Thanks, Oscar.

range A = A2:A5
A2=6; (A2 - A1 = 6; change from A1 to A2 is out of range A)
A3=3; (A3 - A2 = -3; OK)
A4=13; (A4 - A3 = 10; OK)
A5=5; (A5 - A4 = -8; OK)

sum of negative increments:
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) = -3 (OK) - 8 (OK) = -11
sum of positive increments:
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) = 6 (not wanted) + 10
(OK) = 16 - I expect 10 so that: -11 (sum of negative increments) + 10 (sum
of positive increments) = -1 = 5 (first value in range A) - 6 (last value in
range A).

Oscar.

"T. Valko" wrote:

Can you post an example and what result you expect?

--
Biff
Microsoft Excel MVP


"Oscar" wrote in message
...
In range a (part of the column) I have series of values. I want to
calculate
sum of positive and negative increments. Formula
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) works O.K. apart from
the
fact that I should skip first row. Instead of a I should calculate with
subrange of a - range a without a first row.

If anybody can help...

Thanks, Oscar.

Try these:

Negative total:

=SUMPRODUCT(--(A3:A5A2:A4),A3:A5-A2:A4)

Positive total:

=SUMPRODUCT(--(A2:A4A3:A5),A3:A5-A2:A4)

--
Biff
Microsoft Excel MVP


"Oscar" wrote in message
...
range A = A2:A5
A2=6; (A2 - A1 = 6; change from A1 to A2 is out of range A)
A3=3; (A3 - A2 = -3; OK)
A4=13; (A4 - A3 = 10; OK)
A5=5; (A5 - A4 = -8; OK)

sum of negative increments:
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) = -3 (OK) - 8 (OK)
= -11
sum of positive increments:
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) = 6 (not wanted) + 10
(OK) = 16 - I expect 10 so that: -11 (sum of negative increments) + 10
(sum
of positive increments) = -1 = 5 (first value in range A) - 6 (last value
in
range A).

Oscar.

"T. Valko" wrote:

Can you post an example and what result you expect?

--
Biff
Microsoft Excel MVP


"Oscar" wrote in message
...
In range a (part of the column) I have series of values. I want to
calculate
sum of positive and negative increments. Formula
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) works O.K. apart
from
the
fact that I should skip first row. Instead of a I should calculate with
subrange of a - range a without a first row.

If anybody can help...

Thanks, Oscar.

Range A is changing regulary, that's why I'm looking how to work with "range
A without first row". Of course I can define new dinamic range but I try to
avoid having to many ranges.

Oscar.

"T. Valko" wrote:

Try these:

Negative total:

=SUMPRODUCT(--(A3:A5A2:A4),A3:A5-A2:A4)

Positive total:

=SUMPRODUCT(--(A2:A4A3:A5),A3:A5-A2:A4)

--
Biff
Microsoft Excel MVP


"Oscar" wrote in message
...
range A = A2:A5
A2=6; (A2 - A1 = 6; change from A1 to A2 is out of range A)
A3=3; (A3 - A2 = -3; OK)
A4=13; (A4 - A3 = 10; OK)
A5=5; (A5 - A4 = -8; OK)

sum of negative increments:
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) = -3 (OK) - 8 (OK)
= -11
sum of positive increments:
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) = 6 (not wanted) + 10
(OK) = 16 - I expect 10 so that: -11 (sum of negative increments) + 10
(sum
of positive increments) = -1 = 5 (first value in range A) - 6 (last value
in
range A).

Oscar.

"T. Valko" wrote:

Can you post an example and what result you expect?

--
Biff
Microsoft Excel MVP


"Oscar" wrote in message
...
In range a (part of the column) I have series of values. I want to
calculate
sum of positive and negative increments. Formula
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) works O.K. apart
from
the
fact that I should skip first row. Instead of a I should calculate with
subrange of a - range a without a first row.

If anybody can help...

Thanks, Oscar.

Well, you have to define a range to use one way or another so if it'd be
easier to define a dynamic range then do that.

--
Biff
Microsoft Excel MVP


"Oscar" wrote in message
...
Range A is changing regulary, that's why I'm looking how to work with
"range
A without first row". Of course I can define new dinamic range but I try
to
avoid having to many ranges.

Oscar.

"T. Valko" wrote:

Try these:

Negative total:

=SUMPRODUCT(--(A3:A5A2:A4),A3:A5-A2:A4)

Positive total:

=SUMPRODUCT(--(A2:A4A3:A5),A3:A5-A2:A4)

--
Biff
Microsoft Excel MVP


"Oscar" wrote in message
...
range A = A2:A5
A2=6; (A2 - A1 = 6; change from A1 to A2 is out of range A)
A3=3; (A3 - A2 = -3; OK)
A4=13; (A4 - A3 = 10; OK)
A5=5; (A5 - A4 = -8; OK)

sum of negative increments:
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) = -3 (OK) - 8 (OK)
= -11
sum of positive increments:
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) = 6 (not wanted) +
10
(OK) = 16 - I expect 10 so that: -11 (sum of negative increments) + 10
(sum
of positive increments) = -1 = 5 (first value in range A) - 6 (last
value
in
range A).

Oscar.

"T. Valko" wrote:

Can you post an example and what result you expect?

--
Biff
Microsoft Excel MVP


"Oscar" wrote in message
...
In range a (part of the column) I have series of values. I want to
calculate
sum of positive and negative increments. Formula
SUMPRODUCT(--(a-OFFSET(a,-1,0)0),a-OFFSET(a,-1,0)) works O.K. apart
from
the
fact that I should skip first row. Instead of a I should calculate
with
subrange of a - range a without a first row.

If anybody can help...

Thanks, Oscar.