Inserting a formula issue

Aspose.Cells Product Family
1

Hi folks,

Sorry I have another issue during my trial of .Cells

I have inserted a formula in to my speadsheet to count the distinct values in that column using the following command :

failurecells("I" + CStr(failurerow + 1)).R1C1Formula = "=SUM(IF(FREQUENCY(MATCH(I2:I" + CStr(failurerow) + ",I2:I" + CStr(failurerow) + ",0),MATCH(I2:I" + CStr(failurerow) + ",I2:I" + CStr(failurerow) + ",0))>0,1))"

Which looks like this on my example run =SUM(IF(FREQUENCY(MATCH(I2:I1870,I2:I1870,0),MATCH(I2:I1870,I2:I1870,0))>0,1))

When I open the speadsheet with excel the cell that has the formula in it displays #VALUE! and a message 'A value used in the formula is of the wrong data type'

If I click on the formula and DO NOT edit it but just hit the return key Excel accepts the formula and displays the correct value !

Am I doing something stupid ?

Thanks

Kevin

2

Hi Kevin,

Thanks for sharing us the details.

We will soon enhance the Aspose.Cells formula calculation engine to support and set FREQUENCY function for your need.

Thank you.

3

Hi Kevin,

Could you try the attached version if the FREQUENCY function is set fine and the results are correct in the output file using the fix (attached).

Your feedback is highly appreciated in this regard. If you still find the issue, we will be happy to fix it asap.

Thank you.

4

Hi,

This update has partially fixed the issue. Instead of getting the error message (#VALUE!) I now get the number 1 but this is not the correct value.

Please open the attached spreadsheet and look at cell I134 on the Failures worksheet and you will see the value 1. Click on this cell and then click on the end of the formula in the edit line and press enter you will see that the cell now shows the correct value of 19.


Thanks

Kevin

5

Hi,

Thank you for considering Aspose.

After an initial test, we have found out the issue. We will fix it and get back to you soon.

Thank You & Best Regards,

6

Hi,

Thank you for considering Aspose.

Please try the attached latest fix; we have fixed the issue regarding the formulas.

Thank You & Best Regards,

7

Perfect guys,

Works great.

Just to let you know that thanks to your exceptional customer service I have selected Aspose Total and have now purchased a licence.

Thanks

Kev

8

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

This message was posted using Notification2Forum from Downloads module by Laurence.