countif function
 

Please somebody!!

Having immense trouble with countif function. My situation is that I have a
range of values through time. I want to calculate the number of values below
the 95 percentile for those cells. I have used a formula of the form:
countif(C3:C263, "<percentile(C3:263, 0.95)") and tried removing speak-marks,
adding them in, tried calculating the percentile value in a seperate cell and
probably a lot more. Any ideas? Any help would be much appreciated.

countif function
 

Try:
=sumproduct(--(c3:c263<percentile(c3:c263,.95))
--
Jim


"Charles" wrote:

Please somebody!!

Having immense trouble with countif function. My situation is that I have a
range of values through time. I want to calculate the number of values below
the 95 percentile for those cells. I have used a formula of the form:
countif(C3:C263, "<percentile(C3:263, 0.95)") and tried removing speak-marks,
adding them in, tried calculating the percentile value in a seperate cell and
probably a lot more. Any ideas? Any help would be much appreciated.

countif function
 

Hi Jim,

Thanks a lot. Missing a bracket but the progrma fixed that anyway. I'll try
to figure out how that code works later but at the moment I have a thesis to
complete.

Thanks again

"JBoulton" wrote:

Try:
=sumproduct(--(c3:c263<percentile(c3:c263,.95))
--
Jim


"Charles" wrote:

Please somebody!!

Having immense trouble with countif function. My situation is that I have a
range of values through time. I want to calculate the number of values below
the 95 percentile for those cells. I have used a formula of the form:
countif(C3:C263, "<percentile(C3:263, 0.95)") and tried removing speak-marks,
adding them in, tried calculating the percentile value in a seperate cell and
probably a lot more. Any ideas? Any help would be much appreciated.

countif function
 

Another question now: I want to count the number of cells in the column I
mention below that are below the 95th percentile only if on another column in
that same row the value there is below its 95th percentile.

Basically I have two columns containing stock returns. I want to calculate
the number of times both stocks return an amount below their 95th percentile
return on the same day. Maybe that makes it easier to understand.

Thanks again

"Charles" wrote:

Please somebody!!

Having immense trouble with countif function. My situation is that I have a
range of values through time. I want to calculate the number of values below
the 95 percentile for those cells. I have used a formula of the form:
countif(C3:C263, "<percentile(C3:263, 0.95)") and tried removing speak-marks,
adding them in, tried calculating the percentile value in a seperate cell and
probably a lot more. Any ideas? Any help would be much appreciated.

countif function
 

Just add another criteria test like this:
=SUMPRODUCT(--(C3:C263<PERCENTILE(C3:C263,0.95)),--(F3:F263<PERCENTILE(F3:F263,0.95)))
--
Jim


"JBoulton" wrote:

Try:
=sumproduct(--(c3:c263<percentile(c3:c263,.95))
--
Jim


"Charles" wrote:

Please somebody!!

Having immense trouble with countif function. My situation is that I have a
range of values through time. I want to calculate the number of values below
the 95 percentile for those cells. I have used a formula of the form:
countif(C3:C263, "<percentile(C3:263, 0.95)") and tried removing speak-marks,
adding them in, tried calculating the percentile value in a seperate cell and
probably a lot more. Any ideas? Any help would be much appreciated.