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

Question

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 here:

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<String> 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;
}

score 1 · Accepted Answer · answered Nov 25 '16 at 21:49

1

Check whether TotalQty is zero before dividing by it:

"=IF(TotalQty<>0,TotalPrice/TotalQty,0)"

The third argument is the value to display if TotalQty is zero.

answered Nov 25 '16 at 21:49

Michael Liu

52,147
13
117
150

score 1 · Answer 2 · answered Jan 07 '17 at 09:32

1

You could also try:

IFERROR(TotalPrice/TotalQty,"")

The double quotes can be a zero if you wish.

answered Jan 07 '17 at 09:32

Solar Mike

7,156
4
17
32

score 1 · Answer 3 · answered Jan 08 '17 at 19:44

simply use

if(ISBLANK((range of cell),"",your function here)

"" is used to show as empty cell (blank as if nothing is written here) in case the range of cell doesn't consist of anything. Otherwise your function will be run if any value is found in the cells

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

3 Answers3