Using Excel Percentile Functions with Correlated Data in WPS Office Spreadsheets
Question details
The user wants to understand how Excel PERCENTILE.INC and PERCENTILE.EXC functions behave when observations are highly correlated or not statistically independent, and whether correlation changes the percentile result.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is evaluating percentile formulas for a dataset where observations may be correlated or not statistically independent.
- Observed behavior
- The user needs to know whether correlation changes the PERCENTILE.INC or PERCENTILE.EXC result and how to interpret percentile statistics with correlated data.
Open the workbook in WPS Office Spreadsheets and confirm the dataset is in a continuous range or array. Decide whether you need an inclusive or exclusive percentile based on your analysis requirements.
Calculate Percentiles with PERCENTILE.INC or PERCENTILE.EXC in WPS Office Spreadsheets
Use this when you need a percentile value directly from a dataset; correlation or dependence does not change these formulas.
The functions operate only on the supplied values. They do not adjust for whether observations are correlated, independent, or drawn from a complex sampling design.
Open WPS Office Spreadsheets and click the blank cell where the percentile value should appear.
Type =PERCENTILE.INC(range,k) or =PERCENTILE.EXC(range,k), replacing range with your data range and k with the percentile as a decimal.
Press Enter to calculate the percentile and check the displayed value.
Interpret Percentile Results Correctly When Data Are Correlated
Use this when the analysis involves uncertainty, confidence intervals, or sampling error with correlated observations.
Calculate Excel-Compatible Percentiles in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use PERCENTILE.INC or PERCENTILE.EXC to get percentile values. WPS keeps the same formula behavior as Microsoft Excel while remaining free, lightweight, and fully compatible with .xlsx files.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the .xlsx file containing your data.
- 2. Select the result cell: Click a blank cell where the percentile should appear.
- 3. Enter the percentile formula: Type =PERCENTILE.INC(A2:A100,0.9) or use the Formulas tab > Insert Function to find PERCENTILE.EXC.
- 4. Run the calculation: Press Enter to calculate and display the percentile.
- 5. Save in Excel format: Save the workbook as .xlsx to keep compatibility with Microsoft Excel.
Frequently Asked Questions
Do Excel PERCENTILE.INC and PERCENTILE.EXC adjust for correlated data?
No. They calculate percentiles from the supplied values only. Correlation does not change the formula result.
When should I use PERCENTILE.INC instead of PERCENTILE.EXC?
Use PERCENTILE.INC when k can include 0 and 1. Use PERCENTILE.EXC when k must be within 1/(n+1) through n/(n+1), excluding the absolute minimum and maximum.
How does correlation affect percentile analysis?
Correlation affects uncertainty, confidence intervals, and sampling error. It does not affect the percentile point estimate itself.
Can I open an Excel workbook with percentile formulas in WPS Office?
Yes. WPS Office Spreadsheets opens .xlsx files and supports Excel percentile functions such as PERCENTILE.INC and PERCENTILE.EXC.
What values can I use for k in a percentile formula?
For PERCENTILE.INC, k must be between 0 and 1 inclusive. For PERCENTILE.EXC, the valid range depends on the sample size, from 1/(n+1) to n/(n+1).
