Formula - if inside if

Aspose.Cells Product Family
1

Hello,



How can it be possible :



var expenseFinalConlusionFormula = “=IF(1,1,IF(0,0,“titi”)”;



ws.Cells[“A1”].Formula = expenseFinalConlusionFormula;

ws.Cells[“A2”].PutValue(expenseFinalConlusionFormula);





and when I open the file I get :



in A1 : =IF(1,1)

in A2 : =IF(1,1,IF(0,0,“titi”)



For the example I simplified the function.

2

Hi,


Thank you for contacting Aspose support.

I am afraid, your inquiry is not quite clear, however, what I understand is that you wish to confirm if nested IF functions are supported by Aspose.Cells APIs. If so, yes, Aspose.Cells APIs support the nested IF functions. Please check the following piece of code that created the spreadsheet from scratch, inputs some data and adds a formula with nested IF function. By checking the resultant spreadsheet (attached) you will notice that Aspose.Cells APIs can handle this situation.

If I am mistaken, please elaborate your scenario further, more appropriately with the help of a sample spreadsheet.

C#

var book = new Workbook();
book.Worksheets[0].Cells[“A1”].PutValue(“10x12”);
book.Worksheets[0].Cells[“A2”].PutValue(“8x8”);
book.Worksheets[0].Cells[“A3”].PutValue(“6x6”);
book.Worksheets[0].Cells[“A4”].Formula = “=IF(A1=“10x12”,120,IF(A1=“8x8”,64,IF(A1=“6x6”,36)))”;
book.CalculateFormula();
book.Save(“D:/output.xlsx”, SaveFormat.Xlsx);
3

Hello, thank you for your answer, I tried it, it work fine,


But, When I replace if by :

var book = new Aspose.Cells.Workbook();
book.Worksheets[0].Cells[“A1”].PutValue(“10x12”);
book.Worksheets[0].Cells[“A2”].PutValue(“8x8”);
book.Worksheets[0].Cells[“A3”].PutValue(“6x6”);
var expenseFinalConlusionFormula = “=IF(’{0}’!{1}{2}<>”",’{0}’!{1}{2},IF(’{0}’!{1}{3}=“Below SUM: Satisfactory”,’{0}’!{1}{3},“Conclusion is missing”)";
expenseFinalConlusionFormula = string.Format(expenseFinalConlusionFormula, “Sheet1”, “E”, 27, 25);
book.Worksheets[0].Cells[“A1”].Formula = expenseFinalConlusionFormula;

book.CalculateFormula();
book.Save(“c:\output.xlsx”, Aspose.Cells.SaveFormat.Xlsx);

When I debug step by step, the expenseFinalConclusionFormula is a string that contains : =IF(‘Sheet1’!E27<>"",‘Sheet1’!E27,IF(‘Sheet1’!E25=“Below SUM: Satisfactory”,‘Sheet1’!E25,“Conclusion is missing”)

and the value of formula property is =IF(sheet1!e27<>"", Sheet1!e27)

The problem is that there is a missing parenthesis in my sentence. And Aspose does not throw exception, it just corrects the formula in a wrong way by completely omitting the last IF statement.

Could you give a look on this, I think it would be nice to have an error to check the formula is correctly inputted.

Thanks

Thierry
4

Hi Thierry,


Thank you for writing back.

We have evaluated your recently shared scenario while using the latest version of Aspose.Cells for .NET 8.6.0.1, and we are able to notice the said problem, that is; Aspose.Cells APIs should throw an appropriate exception while setting an invalid formula to the cell, rather it tries to correct the formula by removing the inner IF statement, that is not an appropriate action. We have logged this incident in our bug tracking system under the ticket CELLSNET-43933 for further investigation. Our product team will further look into the details of this problem and we will keep you updated on the status of correction.

We apologize for your inconvenience.
5

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-43933 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

6

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.6.0.4 and let us know your feedback. .

7

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

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