How can I prevent "#Div/0!" in calculated cells?

How can I prevent "#Div/0!" in calculated cells (Aspose Cells)?

I am generating a spreadsheet with this calculated field:

pivotTable.AddCalculatedField("Average Price", "=TotalPrice/TotalQty", true);

It works like a charm most of the time, but once in awhile there are 0-values, and thus produce a "#Div/0!", such as for the second and last items shown below.

How can I prevent that?

Here is the PivotTable creation code in full, for more context:

private void PopulatePivotTableSheet()
{
int DESCRIPTION_COLUMN = 1;
int MONTHYR_COLUMN = 3;
int TOTALQTY_COLUMN = 4;
int TOTALPRICE_COLUMN = 5;
int PERCENTOFTOTAL_COLUMN = 7;
int MONTHLY_PERCENTAGE_COLUMN = 8;
int AVGPRICE_COLUMN = 10;
int COLUMNS_IN_DATA_SHEET = 11;
int HEADER_ROW = 8;

AddPreDataSectionToPivotTableSheet();

PivotTableCollection pivotTables = pivotTableSheet.PivotTables;
int colcount = COLUMNS_IN_DATA_SHEET;
string lastColAsStr = ReportRunnerConstsAndUtils.GetExcelColumnName(colcount);
int rowcount = sourceDataSheet.Cells.Rows.Count;
string sourceDataArg = string.Format("sourceDataSheet!A1:{0}{1}", lastColAsStr, rowcount);
int index = pivotTableSheet.PivotTables.Add(sourceDataArg, "A6", "PivotTableSheet");
PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

pivotTable.DisplayNullString = true;
pivotTable.NullString = "0";

pivotTable.AddFieldToArea(PivotFieldType.Row, DESCRIPTION_COLUMN);
pivotTable.RowHeaderCaption = "Description";

pivotTable.AddFieldToArea(PivotFieldType.Column, MONTHYR_COLUMN);
pivotTable.ColumnHeaderCaption = "Months";

pivotTable.AddFieldToArea(PivotFieldType.Data, TOTALQTY_COLUMN);
pivotTable.DataFields[0].DisplayName = "Total Packages";

pivotTable.AddFieldToArea(PivotFieldType.Data, TOTALPRICE_COLUMN);
pivotTable.DataFields[1].DisplayName = "Total Purchases";

pivotTable.AddCalculatedField("Average Price", "=TotalPrice/TotalQty", true);

pivotTable.AddCalculatedField("PercentOfTotal", "=TotalPrice", true);
pivotTable.DataFields[3].DisplayName = "Percentage of Total";
pivotTable.DataFields[3].DataDisplayFormat = PivotFieldDataDisplayFormat.PercentageOfColumn;
pivotTable.RowFields[0].IsAutoSubtotals = false;

PivotField field = pivotTable.RowFields[0];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 1;
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;

pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.CalculateData();
pivotTable.RefreshDataFlag = false;
List contractItemDescs = GetContractItemDescriptions();
ColorizeContractItemBlocks(contractItemDescs);
HideItemsWithFewerThan1PercentOfSales();
FreezePanePivotTable(HEADER_ROW, 2);
FormatPivotTableNumbers();

Style style = workBook.CreateStyle();
style.Font.Name = "Calibri";
style.Font.Size = 12;
pivotTable.FormatAll(style);

sourceDataSheet.IsVisible = false;
}

Hi,


Thanks for your posting and using Aspose.Cells.

You need to set the setting as shown inside the screenshot. Please use the following sample code to work with this setting.

C#
PivotTable pt = worksheet.PivotTables[0];
pt.DisplayErrorString = true;
pt.ErrorString = “—”;//Display 3 dashes whenever error occurs in pivot table

I think the problem was solved from another answer, but as to this sort of code, I already had:


pvt.ErrorString = “”;
pvt.DisplayErrorString = true;

I have now changed it to:

pvt.DisplayErrorString = true;
pvt.ErrorString = “—”;

…to correspond with what you showed. Thanks!

Hi,


Thanks for your posting and using Aspose.Cells.

It seems, your original issue is resolved now, so we have now closed this thread. In case, you are still having this issue, you may open it again.

Yes, it’s good now, thanks!