Find number of particular values over an interval

C

CarterTC

Hi,

I have 22 columns consisting of about 5500 rows aligned by date wit
each column containing hourly temperatures for a different subject ove
a several month period. I need to determine the percentage of hourl
records that fall above a particular upper temperature limit (26 degree
C in this case) every 7 days for each of the 22 subjects.

Any help would be greatly appreciated.

Thanks,
Carte
 
H

Harlan Grove

CarterTC said:
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.
Click to expand...
....

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

CarterTC

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!

Carte
 
C

CarterTC

Harlan

Will your formula return a "0" or an error if there are no values for
particular 1 week period? Is it possible to have it return a numeri
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 numbe
of numeric values that exist within a particular range? I have a fe
gaps in my data due to failed temperature loggers

Thanks agai
 
Top