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

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 %.

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

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