Calculation Issues

Fix Excel Average, Percentile, and Maximum Calculation Errors

Muhammad Talha Oct 13, 2026 869 views

Question details

The user needs to correct AVERAGE and PERCENTILE calculations that return falsely inflated values, and fix duration formatting that wraps incorrectly after 31 days.

How to Fix Incorrect Average and Percentile Results in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating the average and percentile of a dataset and formatting the resulting elapsed time without it resetting at the end of a standard month.
Observed behavior: Statistical functions return a single combined total value instead of evaluating the range of cells, and elapsed time formatting wraps back to zero after 31 days due to calendar day constraints.

Before you start

Verify whether your dataset represents standard numbers or time values stored in seconds, as time conversions require dividing by 86400 (the number of seconds in a day).

Solution 1Recommended

Use Cell Ranges Instead of Addition Operators

Replace plus signs with colon-separated ranges or commas inside your functions to ensure Excel evaluates individual values rather than a single pre-calculated sum.

When you use plus signs (e.g., A1+A2) inside functions like AVERAGE or PERCENTILE.INC, Excel adds them together first. The function then tries to average a single number, which returns the sum itself, making the calculation meaningless.

Select the target cell

Click on the cell where you want the calculated average or percentile to appear.

Enter the formula with a range

Type `=AVERAGE(H3718:H3727)` or `=PERCENTILE.INC(H3718:H3727, 0.8)`. Use a colon to define the start and end of the range.

Convert seconds to days (if applicable)

If your original data is in seconds, append `/86400` to the end of the formula, like `=AVERAGE(H3718:H3727)/86400`, to format it properly as Excel time.

Apply the calculation

Press Enter to execute the formula and view the corrected calculation.

Use Cell Ranges Instead of Addition Operators

Formatting Tip: You can also hold the 'Ctrl' key while clicking cells to automatically insert commas between non-adjacent cells in your function.

Solution 2

Format Cumulative Days Bypassing the 31-Day Limit

Use the INT and TEXT functions to accurately display total elapsed time, preventing Excel's standard 'd' format from rolling over after 31 days.

Advanced Data Calculation

Perform Complex Data Calculations Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful tools and intuitive function wizards to handle averages, percentiles, and complex time formatting without the typical syntax headaches.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your raw data.
2. Access the Function Wizard: Select the target cell, navigate to the 'Formulas' tab, and click on 'Insert Function'.
3. Select the function: Search for AVERAGE or PERCENTILE.INC and click OK.
4. Select your range visually: Use your mouse to highlight the data range (e.g., H3718:H3727). The wizard automatically inserts the correct colon syntax, preventing errors.

100% compatible with Microsoft Excel formulas and time formattingBuilt-in function wizard helps prevent common syntax errors like using plus signs instead of rangesFree, lightweight, and user-friendly interface for seamless data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does my AVERAGE formula return the exact same number as a SUM?

This happens if you use plus signs (e.g., A1+A2+A3) inside the AVERAGE function. Excel evaluates the addition first, effectively asking the program to find the average of a single total number. You must use a colon (A1:A3) or commas (A1, A2, A3) to separate the values.

Why does the Excel time format reset after 31 days?

The standard 'd' format code in Excel represents the day of the month in a calendar date, not an absolute measure of elapsed time. Any elapsed time exceeding 31 days rolls over into a new 'month', causing the day count to restart.

How do I calculate percentiles for time data stored in seconds?

Use the PERCENTILE.INC function on your specific cell range of seconds, then divide the resulting value by 86400 (which is the total number of seconds in a 24-hour day) to convert the result into a standard Excel day fraction.

Excel & Data Problems