Want to COUNTIF with cell value greater than cell reference 1

[DancingDave]

Am trying to calculate the PERCENTILE values for several series of data at various PERCENTILE values i.e. 90th percentile, 95th percentile etc.

I have the following formula to do this

=PERCENTILE(R4:R368,$Q$371/100)
(Where R4:R368 is my data set and Q371 is where I set my percentile e.g. for 95th percentile, I set value to 95)

I want to know how many data are above the percentile that I set i.e. how many times do I exceed the 95th percentile?

I know that I can simply take the value of the above percentile formula and type it directly into a COUNTIF formula...

=COUNTIF(R4:R368,">100")
(Where 100 is the value of e.g. the 95th percentile)

...,however, I have several data sets to do this for and want to easily change the percentile and get the latter formula to automatically update.

I have tried putting the cell reference for the percentile into the equation, but anything I have tried either throws up an error or returns 0.

Any ideas?

DD

PS have already tried the following:

Returns 0
=COUNTIF(R4:R368,">Q371")
=COUNTIF(R4:R368,">(=Q371)")
=COUNTIF(R4:R368,">(=value(Q371))")
=COUNTIF(R4:R368,R4:R368>Q371)

Returns an error
=COUNTIF(R4:R368,>Q371)

 

[Foundy]

Hi,

I think you would need to do something like this.

=COUNTIF(R4:R368,">"&Q371)

Hope this helps.

 

[DancingDave]

Magic. Thanks and have a star!

DD