| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
