# How to convert the percentile value in CDF to be exclusive in R script?

Go to solution Solved by Gaia Paolini,

## Recommended Posts

I'm creating a CDF with various percentiles that consist P97, P97.5, P98, P98.5, P99, P99.5 using R script. I would like to know if we want to convert the percentile to be exclusive (PERCENTILE.EXC function in excel), may I know is it change the percentiles / 100 to (percentiles - 0.0001) / 100? (I tried this change, but it doesn't work). I would appreciate any advice or suggestion.

# define a list of percentile values

percentiles = c(97, 97.5, 98, 98.5, 99, 99.5)

# extract unique values of grouping column

groups = unique(df[,groupingColumnName])

# loop through groups

for (gg in groups) {

# subset the data frame to include only the rows corresponding to the current group

x = df[df[groupingColumnName] == gg, analysisColumnName]

# sort the values and remove missing values

x = sort(x, decreasing = FALSE, na.last = NA)

N = length(x)

# loop through the list of percentiles and calculate the corresponding percentile values and store in a list

percentile_values = lapply(percentiles, function(p) quantile(x, p/100))

# create a new data frame containing the sorted values and corresponding cumulative probabilities and percentile values

cdfTable_g = data.frame(

group = rep(gg, N),

prob = ((1:N) - 1) / (N - 1),

value = x

)

for (i in 1:length(percentiles)){

cdfTable_g[paste0("percentile_",percentiles)] = percentile_values[]

}

# modify the cumulative probabilities based on the current percentile values

for (i in 1:length(percentiles)){

cdfTable_g\$prob[cdfTable_g\$value > percentile_values[]] = percentiles / 100

}

# append the data frame to the master cdfTable using rbind

if (gg == groups[1]) {

cdfTable = cdfTable_g

} else {

cdfTable = rbind(cdfTable, cdfTable_g)

}

}

##### Share on other sites

it is a lot of code, and without data, context and clarification of the goals it is difficult to debug.

##### Share on other sites

Hi, thanks a lot for your reply. Appreciate it. I have attached .dxp file and data.

I would like to convert the percentile to be exclusive (this is similar to the PERCENTILERANK.EXC function in Excel).

Refer to the data as attached, the 95th percentile of the TotalTestTime (Inclusive) is 375.274.

If we use the PERCENTILERANK.EXC function in Excel to convert the percentile to be exclusive, the 95th percentile of the TotalTestTime is 375.6325.

Therefore, it's interested to know how can we convert the percentile to be exclusive in Spotfire using R.

##### Share on other sites

• Solution

Interpreting your question purely as 'how to calculate PERCENTILE_EXC in R':

The PERCENTILE_EXC formula is for calculating the percentile values.

Using this: https://www.statology.org/percentile-exc-vs-percentile-inc-excel/ , the short answer is to add 'type=6' in your calculation of the percentile values.

percentile_values = lapply(percentiles, function(p) quantile(x, p/100))

you would use this:

percentile_values = lapply(percentiles, function(p) quantile(x, p/100,type=6))

This would give 375.6325 as your 95% percentile.