wdprice3
BinaryBuffonary
45908 Posts
user info
edit post |
I have a worksheet set up for calculating water flow and rainfall depths. However, I need the duration of the rainfall (from first non-zero reading to last non-zero reading for rainfall). However, the flow continues long after the rain has stopped (the 0s under both columns don't matter until after the last non-zero reading)
sort of like:
date & time flow rainfall depth
1/1/10 0:00 0 0
1/1/10 0:05 1 1
1/1/10 0:10 3 0
1/1/10 0:15 0 1
1/1/10 0:20 4 0
1/1/10 0:25 2 1
1/1/10 0:30 2 2
1/1/10 0:35 0 1
1/1/10 0:40 2 0
1/1/10 0:45 1 0
1/1/10 0:50 0 0
Thus, in this example, I need to return the rainfall duration of 35 minutes (these are 5-minute summaries and not intervals, thus there are 6 intervals, but 7 readings, therefore 7*5 minutes = 35 minutes). I've got about 160 of these sheets where I need to calculate the rainfall duration.
How can I do this? I'd prefer not to use VBA because some sheets a slightly different.
Each sheet also calculates the total rainfall... so is there a way to count the number of cells (zero and non-zero) until this total has been reached?
[Edited on April 14, 2010 at 1:44 PM. Reason : dammit, that's sounding like a loop in VBA]
[Edited on April 14, 2010 at 1:47 PM. Reason : not that I really know VBA anyways] 4/14/2010 1:39:48 PM
|
wdprice3
BinaryBuffonary
45908 Posts
user info
edit post |
ah, easy.
=((MAX(IF(RAINFALL>0,ROW(RAINFALL),"ERROR"))-MIN(IF(RAINFALL>0,ROW(RAINFALL),"ERROR")))*5/60 4/14/2010 5:52:22 PM |
