Example 95th Percentile Calculation
Note: Excel spreadsheets are used for demonstration purposes, however, there is a slight difference in the opReports percentile() formula and the Excel percentile() formula:
- opReports truncates to an integer to get index at 95th percentile, for example:
95% of 276 records = 262.2
opReports determines the 95th percentile to be INT(262.2)=262nd record, with records sorted ascending. - Excel use a ratio based on value at that index opReport uses and the value at next index to account for the fractional part of the index, for example:
95% of 276 records = 262.2
Excel calculates a final 95th percentile being a ratio of the 262nd and 263rd records, with records sorted ascending.
The ratio that Excel uses is not a simple ratio:
reverse engineering an actual Excel 95th percentile result provides that the ratio used for 276 records is '262.25'
and not the '262.2' that is the exact result of the equation '95% of 276'.
See spreadsheet http://dl-omk.opmantek.com/jira/WAN-Utilisation-Distribution-Report-95th_percentile_calc_20210305-1.xlsx
which contains an opReports WAN Utilisation Distribution Report on first sheet and data and 95th percentile calculation on second sheet.
See newer example spreadsheet http://dl-omk.opmantek.com/jira/WAN-Utilisation-Distribution-Report-95th_percentile_calc_20210421-1.xlsx
which also contains an opReports WAN Utilisation Distribution Report on first sheet and data and 95th percentile calculation on second sheet.