| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF, SUM, VLOOKUP...oh my!!
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF, SUM, VLOOKUP...oh my!!
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF, SUM, VLOOKUP...oh my!!
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) In article , Paul wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF, SUM, VLOOKUP...oh my!!
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF, SUM, VLOOKUP...oh my!!
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 "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
