Sum up all up changes and down changes

  • Thread starter Thread starter Tetsuya Oguma
  • Start date Start date
T

Tetsuya Oguma

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
 
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
 
You are legeeeeeeeeeeeeeeeeend, mate!

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

Thanks again, Max!
 
Back
Top