Wei Keong Tan Posted April 5, 2023 Share Posted April 5, 2023 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 valuespercentiles = c(97, 97.5, 98, 98.5, 99, 99.5)# extract unique values of grouping columngroups = unique(df[,groupingColumnName])# loop through groupsfor (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) }} Link to comment Share on other sites More sharing options...

Gaia Paolini Posted April 6, 2023 Share Posted April 6, 2023 it is a lot of code, and without data, context and clarification of the goals it is difficult to debug. Link to comment Share on other sites More sharing options...

Wei Keong Tan Posted April 6, 2023 Author Share Posted April 6, 2023 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. Link to comment Share on other sites More sharing options...

Solution Gaia Paolini Posted April 6, 2023 Solution Share Posted April 6, 2023 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.So instead of 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. Link to comment Share on other sites More sharing options...

Wei Keong Tan Posted April 7, 2023 Author Share Posted April 7, 2023 Hi, that's great! I verified it, it works! Didn't know we can calculate the exclusive quantile through the parameter of quantile()! Thank you very much Gaia! Link to comment Share on other sites More sharing options...

## Recommended Posts