countif function

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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