Sum up all up changes and down changes
 

Hi all,

Suppose I have a data series like below
A B
Data Chg
46.1250
47.1250 1.0000
46.4375 -0.6875
46.9375 0.5000
44.9375 -2.0000
44.2500 -0.6875
44.6250 0.3750
45.7500 1.1250
47.8125 2.0625
47.5625 -0.2500
47.0000 -0.5625

Chg is calculated from minusing the preivous data item just above from the
value in the corresponding row.

e.g., First exmaple, Chg of 1.000 is indeed 47.1250 - 46.1250

I want one and ONLY ONE function in a cell to calculate sum of all up
changes (=0) and all down changes (<0) in Chg column, ONLY using Data Column
cells as input to this function.

Can someone help?

Tetsuya

Sum up all up changes and down changes
 

With data assumed in A3:A13,

sum of all up changes (=0):
=SUMPRODUCT(--(A4:A13-A3:A12=0),A4:A13-A3:A12)

sum of all down changes (<0):
=SUMPRODUCT(--(A4:A13-A3:A12<0),A4:A13-A3:A12)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik
---
"Tetsuya Oguma" wrote:
Suppose I have a data series like below
A B
Data Chg
46.1250
47.1250 1.0000
46.4375 -0.6875
46.9375 0.5000
44.9375 -2.0000
44.2500 -0.6875
44.6250 0.3750
45.7500 1.1250
47.8125 2.0625
47.5625 -0.2500
47.0000 -0.5625

Chg is calculated from minusing the preivous data item just above from the
value in the corresponding row.

e.g., First exmaple, Chg of 1.000 is indeed 47.1250 - 46.1250

I want one and ONLY ONE function in a cell to calculate sum of all up
changes (=0) and all down changes (<0) in Chg column, ONLY using Data Column
cells as input to this function.

Can someone help?

Tetsuya

Sum up all up changes and down changes
 

You are legeeeeeeeeeeeeeeeeend, mate!

I am on the land of Merlion as well. It's pretty hot outside still :)

Thanks again, Max!

"Max" wrote:

With data assumed in A3:A13,

sum of all up changes (=0):
=SUMPRODUCT(--(A4:A13-A3:A12=0),A4:A13-A3:A12)

sum of all down changes (<0):
=SUMPRODUCT(--(A4:A13-A3:A12<0),A4:A13-A3:A12)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik
---
"Tetsuya Oguma" wrote:
Suppose I have a data series like below
A B
Data Chg
46.1250
47.1250 1.0000
46.4375 -0.6875
46.9375 0.5000
44.9375 -2.0000
44.2500 -0.6875
44.6250 0.3750
45.7500 1.1250
47.8125 2.0625
47.5625 -0.2500
47.0000 -0.5625

Chg is calculated from minusing the preivous data item just above from the
value in the corresponding row.

e.g., First exmaple, Chg of 1.000 is indeed 47.1250 - 46.1250

I want one and ONLY ONE function in a cell to calculate sum of all up
changes (=0) and all down changes (<0) in Chg column, ONLY using Data Column
cells as input to this function.

Can someone help?

Tetsuya

Sum up all up changes and down changes
 

Welcome, Tetsuya
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,900, Files: 354, Subscribers: 53
xdemechanik
---
"Tetsuya Oguma" wrote in message
...
You are legeeeeeeeeeeeeeeeeend, mate!

I am on the land of Merlion as well. It's pretty hot outside still :)

Thanks again, Max!