IF, SUM, VLOOKUP...oh my!!

[Guest]

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 [D5050-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

 

[Niek Otten]

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

|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 [D5050-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

 

[JE McGimpsey]

One way:

=--(SUMPRODUCT(--($D$1:$D$10000<=D50),--($D$1:$D$10000>=(D50-0.25)),
--($E$1:$E$10000>=0),--($E$1:$E$10000<=0.3),--($E$1:$E$10000<>""))>0)

 

[Guest]

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

 

[Guest]

I don't know if I need to REpost this question or wait for a reply. Please
refer to my original post and my follow up information.
Thanks for all your help...
Paul

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

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

|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 [D5050-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