Difficulty Getting Formula to Work

Aspose.Cells Product Family
1

I'm setting a formula using this line of code

worksheet.Cells(row, 2).Formula = "=SUMPRODUCT((C2:C" + CStr(row) + "<>"""")/COUNTIF(C2:C" + CStr(row) + ",C2:C" + CStr(row) + "&""""))"

When I open the worksbook in excel it show #VALUE. If I select the cell, click in the edit area and simple press enter it correctly calculates the results. What do I need to do the have it correctly calculate by simply opening the document in excel.

By the way, I got the formula from here, http://www.mrexcel.com/archive2/63500/73502.htm. It returns a count of distinct values. It would also be nice if there was a simpler formula to do this.

Thanks
Scott

2

Hi Scott,

Thank you for considering Aspose.

The problem you have mentioned may be occurring due to the use of an older version of Aspose.Cells API. Please try the attached latest version of Aspose.Cells and do let us know if you still face any problem.

Thank you & Best Regards,

3

Thanks, that helped the issue with the first formula. However, while waiting for a response I tried searching for a simpler formula to get a distinct count and found this.

=SUM(IF(FREQUENCY(C2:C308,C2:C308)>0,1))

I like this formula better so I would like to get it to work. However, it shows 1 for the result until I click in the edit box in excel and press enter. At that point it shows the correct value of 210.

What do I need to do to get to work?

Thanks,
Scott

4

Hi Scott,

Thank you for considering Aspose.

As per your issue regarding the new formula, I am afraid currently Aspose.Cells does not support to set / read FREQUENCY formula.

Following is the list of all the formula supported by Aspose.Cells: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/supported-formulas-functions.html).

We will provide the support for FREQUENCY formula soon.

Thank you and Best Regards,

5

Hi Scott,

Please try this fix.

We have supported to set,get and calculate the FREQUENCY fucntion.