Calculation Issues

How to Calculate Percentage-Based mm:ss Pace Ranges in Excel

Khadija Khan Oct 20, 2026 868 views

Question details

The user wants to calculate and display running pace ranges based on a 100% baseline pace using percentage steps and specific time intervals.

How to Calculate Percentage-Based mm:ss Pace Ranges in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic pace chart or running schedule where time boundaries need to be computed based on a target 100% pace and specific percentage increments.
Observed behavior: A combined time range such as '6:16-6:32' is treated as text by Excel, making direct time calculations impossible. The upper and lower bounds must be calculated separately before combining.

Before you start

Ensure that your baseline pace and interval times are entered and formatted as real Excel time values (e.g., 00:05:55) rather than plain text, as this is required for mathematical formulas to work correctly.

Solution 1Recommended

Calculate Lower and Upper Limits in Separate Cells

Compute the exact mm:ss bounds for your pace ranges using Excel's mathematical functions before joining them into a text string for display.

Excel cannot perform mathematical calculations on text strings like '6:16-6:32'. To accurately calculate pace ranges, you must compute the lower and upper time limits in separate columns. Once calculated, they can be concatenated for display purposes.

Set up your baseline and intervals

Store your 100% baseline pace as a real time value (e.g., use TIME(5,55,0) or type 5:55). Define your percentage step (e.g., 5% or 1/20) and time interval (e.g., 18 seconds) in separate reference cells.

Determine the percentage position

Assuming your current pace value is in cell D2, calculate its relative position using a formula like =1-(D2-TIME(5,55,0))/(20*TIME(0,18,0)). This identifies the step multiplier.

Calculate lower and upper percentage limits

In two new columns, compute the lower limit with =ROUNDDOWN(E2*20,0)/20 and the upper limit with =ROUNDUP(E2*20,0)/20 (assuming your percentage position is in E2).

Compute the final mm:ss times

Calculate the exact time boundaries by applying the limits back to the baseline. Use =(1-F2)*20*TIME(0,18,0)+TIME(5,55,0) for one bound, and swap F2 for G2 for the other.

Format cells as mm:ss

Highlight your result cells, right-click and choose 'Format Cells'. Go to the 'Custom' category and enter 'mm:ss' to display only minutes and seconds.

Calculate Lower and Upper Limits in Separate Cells

Combining for Display: You can safely combine the separate limit cells into a single text display using a formula like =TEXT(H2, "mm:ss") & "-" & TEXT(I2, "mm:ss").

Use WPS Spreadsheet

Easily Calculate Complex Time Formulas in WPS Spreadsheet

WPS Spreadsheet provides robust time calculation features and full compatibility with Excel functions. You can seamlessly calculate running pace percentages, format cells as mm:ss, and generate detailed pacing charts.

1. Open WPS Spreadsheet: Launch WPS Office and open a new Spreadsheet to build your pace table.
2. Input baseline data: Enter your target times and percentage intervals, ensuring they are formatted correctly as Time values.
3. Apply calculation formulas: Use the TIME, ROUNDDOWN, and ROUNDUP functions in separate cells to process the upper and lower limits of your pacing.
4. Format and display: Use the Custom Format menu to set cells to mm:ss, or combine them using the TEXT function for a neat visual range.

Fully compatible with Microsoft Excel TIME, ROUNDDOWN, and TEXT functionsAdvanced custom cell formatting for precise mm:ss displaysLightweight, fast, and free alternative to Microsoft OfficeFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #VALUE! error when subtracting times like 6:32-6:16?

If you typed '6:32-6:16' directly into a single cell, Excel treats the entire cell as a text string rather than numerical time values. You cannot perform subtraction on text. You must store 6:32 and 6:16 in separate cells to use them in calculations.

How do I format a cell to only show minutes and seconds?

Select the cell, right-click, and choose 'Format Cells'. In the Number tab, select the 'Custom' category, type 'mm:ss' into the Type field, and click OK.

Can I combine two calculated time cells into one display cell?

Yes. Once you have mathematically calculated your lower and upper limits in separate cells (e.g., H2 and I2), you can combine them for display using the TEXT function. For example: =TEXT(H2, "mm:ss") & " - " & TEXT(I2, "mm:ss").

Excel & Data Problems