|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 16th, 2008, 02:48 AM
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
July 16th, 2008, 02:48 AM
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:A120),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
July 16th, 2008, 06:30 AM
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:A120),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
July 16th, 2008, 06:48 AM
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!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
