TWW - Excel question...

go to bottom

Message Boards » » Excel question...

Page [1]

wdprice3
BinaryBuffonary
45912 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
45912 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

Message Boards » Tech Talk » Excel question...

Page [1]

go to top

Admin Options : move topic | lock topic