Maybe I didn't provide enough information...The cell F50 example was just a
random cell I choose. Hopefully this extra information can zero in on what I
want.
Let's start with cell F84 (I am sure there are 6 hours of prior information
from D84 back to D36, but it will not always be 49 rows).
The time information (column 'D' is data collected from rain gauges recorded
every 5 minutes). The values in column 'E' record amount of rain. The end of
a rain event is defined by 6 hours of 0 rain fall (based on 6 hours and 0
values). I want the range to look back 6 hours (column 'D' and sum the values
in column 'E'). If the sum is 0, then it determines the end of the rain event
and returns a value of 1. If the sum is 0, then the rain event has not
ended and returns a value of " ", thus allowing the cycle to continue until a
value of 1 (zero rain fall for 6 hours) has occured. I have another formula
that "grabs" the begining and ending times of the rain event, but I need the
formula to define a rain event. Hopefully this clarifies, not confuses you
more.
Thanks for the reply,
Paul

"Niek Otten" wrote:

Hi Paul,

Your example doesn't work because in F50 there is no 6 hours prior. The first possible one is in row 73. I assume that you want to
sum only the numbers smaller than or equal to .3 and greater than or equal to zero.
Formula:

=SUMIF(E1:E73,"<=.3",E1:E73)-SUMIF(E1:E71,"<0",E1:E73)

You can copy this formula down, it will adjust automatically.
You'll have to decide what to do with the rows above.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Paul" wrote in message ...
|I hope someone can help...
| I can not seem to come up with a formula to do what I want.
| I think the formula will contain "IF", "SUM", and "VLOOKUP", but I am not
| 100% sure. It could also be 'SUMIF'.
| Here is the situation.
| I have a column 'D' of time. The dates range for 2 months every 5 minutes.
| I have a column 'E' of values (mostly between 0.0 and 0.3).
| In column 'F' I would like to make a formula.
| Let's say I am in cell F50...What I want the range to be is [D50:D50-0.25]
| (6 hours prior)...I want to add the values in the 'E' column that fall within
| this range I just stated.
| Does that make sense?
| I think if someone could help me with that, I could probably complete the
| rest of the formula. The rest of the formula is: if the sum of the range is
| equal to 0 then 0, if the sum of the range is 0 then 1. I have another
| formula based on the 0 &1 result.
| Thanks,
| Paul

Hi,

Maybe:

=IF(SUMPRODUCT(($D$2:$D$2000<=D2)*($D$2:$D$2000=( D2-0.25)),$E$2:$E$2000)=0,1,"")

HTH
Jean-Guy

"Paul" wrote:

Maybe I didn't provide enough information...The cell F50 example was just a
random cell I choose. Hopefully this extra information can zero in on what I
want.
Let's start with cell F84 (I am sure there are 6 hours of prior information
from D84 back to D36, but it will not always be 49 rows).
The time information (column 'D' is data collected from rain gauges recorded
every 5 minutes). The values in column 'E' record amount of rain. The end of
a rain event is defined by 6 hours of 0 rain fall (based on 6 hours and 0
values). I want the range to look back 6 hours (column 'D' and sum the values
in column 'E'). If the sum is 0, then it determines the end of the rain event
and returns a value of 1. If the sum is 0, then the rain event has not
ended and returns a value of " ", thus allowing the cycle to continue until a
value of 1 (zero rain fall for 6 hours) has occured. I have another formula
that "grabs" the begining and ending times of the rain event, but I need the
formula to define a rain event. Hopefully this clarifies, not confuses you
more.
Thanks for the reply,
Paul

"Niek Otten" wrote:

Hi Paul,

Your example doesn't work because in F50 there is no 6 hours prior. The first possible one is in row 73. I assume that you want to
sum only the numbers smaller than or equal to .3 and greater than or equal to zero.
Formula:

=SUMIF(E1:E73,"<=.3",E1:E73)-SUMIF(E1:E71,"<0",E1:E73)

You can copy this formula down, it will adjust automatically.
You'll have to decide what to do with the rows above.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Paul" wrote in message ...
|I hope someone can help...
| I can not seem to come up with a formula to do what I want.
| I think the formula will contain "IF", "SUM", and "VLOOKUP", but I am not
| 100% sure. It could also be 'SUMIF'.
| Here is the situation.
| I have a column 'D' of time. The dates range for 2 months every 5 minutes.
| I have a column 'E' of values (mostly between 0.0 and 0.3).
| In column 'F' I would like to make a formula.
| Let's say I am in cell F50...What I want the range to be is [D50:D50-0.25]
| (6 hours prior)...I want to add the values in the 'E' column that fall within
| this range I just stated.
| Does that make sense?
| I think if someone could help me with that, I could probably complete the
| rest of the formula. The rest of the formula is: if the sum of the range is
| equal to 0 then 0, if the sum of the range is 0 then 1. I have another
| formula based on the 0 &1 result.
| Thanks,
| Paul