Calculation Issues

How to Calculate Excel Scores by Redistributing N/A Percentages

Nimra Malik Oct 10, 2026 868 views

Question details

The user needs to calculate a final score in an Excel worksheet where some criteria are marked as 'N/A', requiring their percentage weights to be redistributed among the remaining applicable criteria.

Calculate Excel Scores by Redistributing N/A Percentages

Product: Spreadsheet
Device & OS: not provided
Scenario: Creating a scoring or grading worksheet where certain evaluation criteria might not apply to every subject.
Observed behavior: The total score needs to accurately reflect 100% by ignoring the 'N/A' weights and proportionally redistributing them to the scored criteria.

Before you start

Ensure your scoring data is organized with criteria weights in one column and achieved scores in another, and verify that non-applicable criteria are clearly marked with 'N/A'.

Solution 1Recommended

Use SUM and SUMIF to Calculate Proportional Scores

Build a formula that sums the achieved scores and divides it by the total weight of only the applicable criteria.

To redistribute the weight of 'N/A' categories, you must calculate the total achieved score and divide it by the sum of the weights of the criteria that were actually evaluated. This ensures the remaining scores are scaled up to represent 100% of the applicable weight.

Organize your data columns

Identify the column containing your 'Criteria Weights' (e.g., Column B) and the column containing your 'Achieved Scores' (e.g., Column C).

Sum the achieved scores

Calculate your total achieved points using the SUM function on your score column. For example, use =SUM(C2:C10).

Sum the applicable weights

Calculate the total weight of only the applicable criteria using the SUMIF function. Use =SUMIF(C2:C10, "<>N/A", B2:B10) to sum the weights in Column B only if the corresponding score in Column C is not 'N/A'.

Divide to find the final adjusted score

Combine the two formulas to get the final percentage. Enter =SUM(C2:C10)/SUMIF(C2:C10, "<>N/A", B2:B10) in your final score cell, then format the cell as a Percentage.

Use SUM and SUMIF to Calculate Proportional Scores

Sharing Workbooks for Support: If you are requesting help and sharing a sample workbook via cloud storage (like Google Drive), make sure the file permissions are set to 'Anyone with the link can view' so it can be accessed without requiring a sign-in.

Professional Spreadsheet Tool

Easily Manage Scoring Worksheets with WPS Spreadsheet

WPS Spreadsheet provides powerful built-in functions like SUMIF and SUMPRODUCT to effortlessly handle complex scoring calculations, including redistributing N/A weights.

1. Open your scoring workbook: Launch WPS Spreadsheet and open your existing grading or scoring file.
2. Select the target cell: Click on the cell where you want the final adjusted score to appear.
3. Apply the calculation formula: Enter your customized =SUM(...)/SUMIF(...) formula to exclude 'N/A' values.
4. Format as a percentage: Press Enter to calculate, then go to the Home tab and click the '%' icon to format the result.

Fully compatible with Microsoft Excel (.xlsx) formatsSupports advanced logical and mathematical formulas for dynamic scoringFree and lightweight alternative for seamless data analysisBuilt-in cell formatting for percentages and custom grading scales

microsoft office alternative - wps office

Frequently Asked Questions

What happens if all criteria are marked as 'N/A'?

If all criteria are marked as 'N/A', the SUMIF formula will result in a zero denominator, causing a #DIV/0! error. You can wrap your calculation in an IFERROR function, like =IFERROR(your_formula, "No Score"), to display a custom message instead of an error.

Can I use SUMIFS if I have multiple conditions to exclude?

Yes, the SUMIFS function allows you to specify multiple criteria. You can use it to exclude both 'N/A' text and blank cells by adding additional condition ranges to your formula.

How do I ensure my shared cloud spreadsheet doesn't require a sign-in?

If you are sharing a file via Google Drive or OneDrive, right-click the file, select 'Share', and change the general access settings from 'Restricted' to 'Anyone with the link'. This allows external users to open the file without signing into an account.

Excel & Data Problems