Excel Formula to Calculate Percentage of Assignments Completed Within 3 Days
Question details
The user needs an Excel formula to calculate the percentage of tasks or assignments submitted within a three-day window of their service date, ignoring records with no submission date.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking task completion rates and analyzing service-level agreement (SLA) compliance based on submission dates.
- Observed behavior
- A calculation formula is required to yield a percentage value of on-time submissions against the total number of submitted assignments.
Ensure your service dates and submission dates are formatted properly as Date values in your spreadsheet before applying the formula.
Use the SUMPRODUCT and COUNT Functions
This method combines SUMPRODUCT to count assignments meeting the 3-day criteria and COUNT to find the total submitted assignments.
The SUMPRODUCT function evaluates multiple conditions across arrays. By checking if the submission date is not blank and is less than or equal to the service date plus three, we can isolate the on-time submissions. Dividing this by the total number of submissions gives the completion percentage.
Click on an empty cell where you want the final percentage result to appear.
Type the formula =SUMPRODUCT((E2:E100<>"")*(E2:E100<=D2:D100+3))/COUNT(E2:E100) into the formula bar. Assume D2:D100 contains your service dates and E2:E100 contains your submission dates. Adjust these ranges to match your actual data.
Press the Enter key to apply the formula. The cell will display a decimal number representing the ratio.
Select the result cell, navigate to the Home tab on the ribbon, and click the '%' (Percentage Style) button in the Number group to format the decimal as a percentage.
Use WPS Spreadsheet for Advanced Formula Calculations
WPS Spreadsheet provides full support for advanced array formulas like SUMPRODUCT, making it easy to track SLAs, calculate percentages, and manage large datasets efficiently.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your service and submission dates.
- 2. Apply the formula: Type the SUMPRODUCT formula into your target cell to calculate the completion ratio.
- 3. Format the output: Use the 'Number Format' dropdown on the Home tab to apply the Percentage format instantly.
Frequently Asked Questions
Why does my formula return a #VALUE! error?
This usually happens if there is text in your date columns. Ensure that all cells in the service date and submission date ranges (e.g., D2:D100 and E2:E100) contain valid date values or are completely blank, not text strings.
How can I change the formula to calculate assignments completed within 5 days?
Simply change the '+3' in the formula to '+5'. The updated formula will look like this: =SUMPRODUCT((E2:E100<>"")*(E2:E100<=D2:D100+5))/COUNT(E2:E100).
Does COUNT(E2:E100) include blank cells?
No, the COUNT function only counts cells that contain numbers (including dates). It automatically ignores blank cells, which ensures your percentage is based only on assignments that have actually been submitted.
