Pivot table shows #DIV/0! in value columns that are set to Average AVERAGEIF formulas

Aspose.Cells Product Family
1

Hi,

Could we get some help on an issue we’re having with displaying “AVERAGEIF” formulas values in pivot tables as {Values}.

When we have a formula column that is using Excel’s “AVERAGEIF” function and we add this item into a pivot table as a value, converting the file to be a PDF will cause the pivot table to have “#DIV/0!” in the column with the “AVERAGEIF” values.

I have attached a small project to demonstrate the issue. I have also attached an input file “input file with values.xlsx” to show the issue. The code will open in input file, refresh the formulas, refresh the pivot table, then saves it as a PDF and XLSX called “generated file”.

If you open the PDF output you can see that the columns that are displaying data from columns that using AVERAGEIF only shows “#DIV/0!”.

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to replicate this issue using your sample project and source file. The output pdf shows “#DIV/0!” in the column with the “AVERAGEIF” values.We have logged this issue in our database. We will look into it and resolve this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41963.

We have attached the output files for a reference.

3

Hi,

You guys probably already found out that the pivot table shows “#DIV/0!” because the source table is showing “#DIV/0!”; So the formula wasn’t calculated correctly in the source table in the first place.

Could you confirm that you guys have seen this please?

Thank you.

4

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to see this issue. After running your code, the pivot table in the output xlsx file shows “#DIV/0!” because the source table is showing “#DIV/0!”. Similarly the output pdf also shows “#DIV/0!” in the pivot table and source table.

We will look into this issue and once, we will have some fix or update for you, we will let you know asap.

5

Hi,

Sorry to be a pain, but could you give me information about progress in this issue please?
I
need to give my manager daily updates on the status of this issue. “AVERAGEIF”, “SUMIF”, and “COUNTIF” are important for the type of information we are adding into the Excel file we want to create. We’re
aiming to be have a releasable build of our project next week, and we
need to know if this is still a possibility.

Thank you

6

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have fixed this issue in the major release of Aspose.Cells for .NET. Please wait for the major release which will be published soon. Once, it will be released, you will be notified.

7

Good to know, thank you very much; my manager will be looking forward to it.

8

The issues you have found earlier (filed as CELLSNET-41963) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.