Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find number of particular values over an interval
Hi,
I have 22 columns consisting of about 5500 rows aligned by date with each column containing hourly temperatures for a different subject over a several month period. I need to determine the percentage of hourly records that fall above a particular upper temperature limit (26 degrees C in this case) every 7 days for each of the 22 subjects. Any help would be greatly appreciated. Thanks, Carter |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find number of particular values over an interval
CarterTC wrote...
I have 22 columns consisting of about 5500 rows aligned by date with each column containing hourly temperatures for a different subject over a several month period. *I need to determine the percentage of hourly records that fall above a particular upper temperature limit (26 degrees C in this case) every 7 days for each of the 22 subjects. .... So each column is a different subject? Hourly temperatures means 168 cells per 7 day period? Do you need the percentages of temperatures above 26C separately for each week? I'll assume so. Further, I'll assume the data is in C3:X5002. C5004: =COUNTIF(INDEX(C$3:C$5002,168*ROWS(C$5004:C5004)-167):INDEX(C$3:C $5002,168*ROWS(C$5004:C5004)), "26")/168 Fill C5004 right into D5004:X5004. Select C5004:X5004 and fill down as far as needed. Format these cells as %. |
#3
|
|||
|
|||
Hi Harlan,
I believe that did the trick! I will proof a few and see for certain, but it definitely looks like it worked. I can't thank you enough! Carter |
#4
|
|||
|
|||
Harlan,
Will your formula return a "0" or an error if there are no values for a particular 1 week period? Is it possible to have it return a numeric value only if there are more than some number of records, say 24? Edit: Actually, instead of dividing by 168, can it divide by the number of numeric values that exist within a particular range? I have a few gaps in my data due to failed temperature loggers. Thanks again Last edited by CarterTC : February 7th 12 at 08:55 PM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check the interval of a cell withni a range of values | Excel Discussion (Misc queries) | |||
Random Number Generation at Interval | Excel Discussion (Misc queries) | |||
How to find row in interval? | Excel Discussion (Misc queries) | |||
Averaging Values Embraced In an Interval Between Two Dates | Links and Linking in Excel | |||
counting values within given interval - countif? | Excel Discussion (Misc queries) |