helicopter airtime, log total

[virtuavan]

hi could anyone help me with writing a "simple" formula/function.
many thanks in advance.

i have a log file that writes data to a csv. spread sheet every 60secs.
it reports the running time of a simulator program, in seconds.
each time the program is launched anew, the counter will begin back to zero.

so it is in the form

sample no time
1----------------0
2----------------0
3----------------0
4----------------60
5----------------120
6----------------180
7----------------0
8----------------0
9----------------60
10---------------120
11---------------0
12---------------0

and so on for days

i want the total running time of the program.

i am guessing to give me this the combined result of the formular/syntax/function must be something like:

(if value is 0 or smaller than last value, then the previous value is a subtotal.), (now i want to add all the subtotals together) to give airtime.

can anyone tell me exactly what arguments/formula i should use process my data. so i can enter it as function and automate the process

many thanks

 

[taigovinda]

Welcome to the board!

Try this, where A1:A13 is the data set:

Excel Workbook
	A	B	C
1	0		432
2	0
3	60
4	120
5	137
6	0
7	60
8	120
9	180
10	0
11	60
12	115
13	0
Sheet1

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(A1:A13,--(A2:A14))

 

[virtuavan]

thats great thanks, seems to do the job, perfectly.

if you have the time, id value a description in words..so i can see how you got there......if not ill just have to work it out


thanks again

 

[taigovinda]

Check out this great tutorial on sumproduct: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Basically, the formula adds up A1:A13, but only when the cell below is less than A1:A13. I tried to type an explanation but somehow it didn't come thru on the board... If you take the time to digest that tutorial, you will find a great deal of versatility in sumproduct(). Cheers.<a1)*a1><a2)*a2><a13)*a13><a(n-1)) evaluates="" either="" (true)="" or="" (false).="" it="" adding="" up="" a1:a13="" only="" when="" cell="" below="" less.="" --="" is="" called="" double="" unary="" coerces="" true="" false="" 1="" 0="" so="" sumproduct="" can="" sum="" products...="" if="" take="" time="" digest="" tutorial="" you="" will="" find="" a="" great="" deal="" versatility="" with="" sumproduct.="" cheers="" -="" and="" all="" thanks="" to="" the="" writer="" of="" that="" tutorial!=""></a(n-1))></a13)*a13></a2)*a2></a1)*a1>