Excel数据透视表添加DONE占TOTAL百分比行遇#N/A错误求助

Hey there! Let’s get that % DONE calculation working correctly in your pivot table—those #N/A errors are no fun. Here’s why your current approach is causing issues, plus two reliable ways to fix it:

Why Your Current Setup Fails #

When you set the value display to % Of with "previous" as the base value, Excel tries to compare the current row’s DONE value to the previous row’s TOTAL value. If there’s no previous row (like the first row in your table) or your pivot table structure doesn’t have sequential rows with valid TOTAL values, you’ll get #N/A. This isn’t what you want—you need to calculate DONE / TOTAL for the same row.

Method 1: Use a Calculated Field (Most Reliable) #

This is the straightforward way to compute the ratio directly between the two fields:

• Click any cell inside your pivot table.
• Go to the Analyze (or Options, depending on your Excel version) tab in the top menu bar.
• Select Fields, Items & Sets → Calculated Field.
• In the dialog box:
  1. Name the new field % DONE.
  2. In the formula box, enter: =DONE/TOTAL
  3. Click Add, then OK.
• The new % DONE field will appear in your pivot table. If it doesn’t show as a percentage, right-click the field’s cells → Format Cells → select Percentage and adjust decimal places as needed.

Bonus: Handle Divide-by-Zero Errors #

If some TOTAL values are 0, you’ll get #DIV/0! instead. Modify the formula to:
=IFERROR(DONE/TOTAL, 0)
This will show 0% instead of an error when TOTAL is 0.

Method 2: Correctly Use "Of" Value Display (Alternative) #

If you prefer using the built-in value display option, adjust your settings to target the right comparison:

• Drag the DONE field into the Values area again (so you have Sum of DONE2).
• Right-click any cell in the Sum of DONE2 column → Value Field Settings → Show Values As.
• Select % Of from the dropdown.
• For the Base Field, choose your row label field (e.g., if your rows are grouped by "Project", select "Project").
• For the Base Item, select Grand Total only if you want DONE as a percentage of the overall grand total. Note: This method works best for comparing to totals, not per-row DONE/TOTAL ratios—stick with Method 1 for that use case.

Give Method 1 a shot first—it’s designed exactly for calculating ratios between two fields in the same pivot table row. Let me know if you hit any snags!

内容的提问来源于stack exchange，提问作者4lackof