Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with condition range
Hello,
I need some help! I'm trying to count the number of times a specific pressure is not just reached, but maintained for a certain time period. I have a trend list where a measurement program has dumped data into Excel for certain points in time. For example: Column A Column B Pressure 1/1/08 11:29AM 53.50 1/1/08 3:29PM 83.76 1/1/08 7:29PM 89.54 1/2/08 11:29PM 50.12 So, what I need to be able to do is count the number of times that the pressure not just goes above 80, but stays there for at least 8 hours, let's say (so it would not count the above example, because it only stayed above 80...which I realize is not a perfect example because they're 4 hour increments and it could have theoretically stayed above 80 longer...anyway). Any ideas? Any help is appreciated--thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with condition range
This seems to work...
First thing, your times are not true Excel times. A true Excel recognized time has a space before AM/PM: 11:29AM = not a recognized time 11:29 AM = recognized time So, assuming you get the times straightened out... Another assumption: the data is in ascending order like the sample you posted... The row above your data needs to be empty... So, assuming your data starts in cell A2... Enter this formula in D2: =IF(AND(C1=80,C2=80),((A2+B2)-(A1+B1))*24,"") Enter this formula in E2: =IF(D2="","",IF(D3="",SUM(D$2:D2)-SUM(E1:E$2),"")) Select both D2 and E2 and copy down to the end of data. To get the count that meets your conditions: =COUNTIF(E:E,"=8") -- Biff Microsoft Excel MVP "Sam" wrote in message ... Hello, I need some help! I'm trying to count the number of times a specific pressure is not just reached, but maintained for a certain time period. I have a trend list where a measurement program has dumped data into Excel for certain points in time. For example: Column A Column B Pressure 1/1/08 11:29AM 53.50 1/1/08 3:29PM 83.76 1/1/08 7:29PM 89.54 1/2/08 11:29PM 50.12 So, what I need to be able to do is count the number of times that the pressure not just goes above 80, but stays there for at least 8 hours, let's say (so it would not count the above example, because it only stayed above 80...which I realize is not a perfect example because they're 4 hour increments and it could have theoretically stayed above 80 longer...anyway). Any ideas? Any help is appreciated--thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with condition range
So far, works PERFECTLY! This will be a huge help. Thanks so much!
"T. Valko" wrote: This seems to work... First thing, your times are not true Excel times. A true Excel recognized time has a space before AM/PM: 11:29AM = not a recognized time 11:29 AM = recognized time So, assuming you get the times straightened out... Another assumption: the data is in ascending order like the sample you posted... The row above your data needs to be empty... So, assuming your data starts in cell A2... Enter this formula in D2: =IF(AND(C1=80,C2=80),((A2+B2)-(A1+B1))*24,"") Enter this formula in E2: =IF(D2="","",IF(D3="",SUM(D$2:D2)-SUM(E1:E$2),"")) Select both D2 and E2 and copy down to the end of data. To get the count that meets your conditions: =COUNTIF(E:E,"=8") -- Biff Microsoft Excel MVP "Sam" wrote in message ... Hello, I need some help! I'm trying to count the number of times a specific pressure is not just reached, but maintained for a certain time period. I have a trend list where a measurement program has dumped data into Excel for certain points in time. For example: Column A Column B Pressure 1/1/08 11:29AM 53.50 1/1/08 3:29PM 83.76 1/1/08 7:29PM 89.54 1/2/08 11:29PM 50.12 So, what I need to be able to do is count the number of times that the pressure not just goes above 80, but stays there for at least 8 hours, let's say (so it would not count the above example, because it only stayed above 80...which I realize is not a perfect example because they're 4 hour increments and it could have theoretically stayed above 80 longer...anyway). Any ideas? Any help is appreciated--thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with condition range
You're welcome. Thanks for the feedback!
I have to admit, this was quite a challenge. I was racking my brain trying to do this with a single formula and was getting nowhere fast! So, I decided to go with the helper formula route. -- Biff Microsoft Excel MVP "Sam" wrote in message ... So far, works PERFECTLY! This will be a huge help. Thanks so much! "T. Valko" wrote: This seems to work... First thing, your times are not true Excel times. A true Excel recognized time has a space before AM/PM: 11:29AM = not a recognized time 11:29 AM = recognized time So, assuming you get the times straightened out... Another assumption: the data is in ascending order like the sample you posted... The row above your data needs to be empty... So, assuming your data starts in cell A2... Enter this formula in D2: =IF(AND(C1=80,C2=80),((A2+B2)-(A1+B1))*24,"") Enter this formula in E2: =IF(D2="","",IF(D3="",SUM(D$2:D2)-SUM(E1:E$2),"")) Select both D2 and E2 and copy down to the end of data. To get the count that meets your conditions: =COUNTIF(E:E,"=8") -- Biff Microsoft Excel MVP "Sam" wrote in message ... Hello, I need some help! I'm trying to count the number of times a specific pressure is not just reached, but maintained for a certain time period. I have a trend list where a measurement program has dumped data into Excel for certain points in time. For example: Column A Column B Pressure 1/1/08 11:29AM 53.50 1/1/08 3:29PM 83.76 1/1/08 7:29PM 89.54 1/2/08 11:29PM 50.12 So, what I need to be able to do is count the number of times that the pressure not just goes above 80, but stays there for at least 8 hours, let's say (so it would not count the above example, because it only stayed above 80...which I realize is not a perfect example because they're 4 hour increments and it could have theoretically stayed above 80 longer...anyway). Any ideas? Any help is appreciated--thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count cells with condition | Excel Worksheet Functions | |||
count between two date with one more condition | Excel Worksheet Functions | |||
count col-A if col-B = condition | Excel Worksheet Functions | |||
count on condition / Formula | Excel Worksheet Functions | |||
Count the occurence of more than one condition | Excel Worksheet Functions |