Hi,
If I try to use the IF condition in SharedFormula the output result is wrong while setting the formula through code. If I use the formula manually in excel gets the correct output.
For example:
cells[“H2”].SetSharedFormula("=IF(AND(NOT(ISBLANK(P2)),P2<50), “< $50M”, IF(AND(P2>=50,P2<=299), “$50M - $299M”, IF(AND(P2>=300,P2<=999), “$300M - $999M”, IF(AND(P2>=1000,P2<=4900), “$1B - $4.9B”,IF(P2>4900, “> $5B”,“Empty”)))))", 12, 1);
Here the P2 cell is Blank, the output is > $5B supposed to be Empty
Where I am going wrong?
If I type the formula manually, its shows Empty
@kiran5388,
I have tried this formula using the latest version 21.9 but could not observe this issue. It returns “Empty” (12 times) from H2 to H13. Could you please give it a try using the latest version 21.9 and share your feedback again? Following sample code is used for testing.
Workbook workbook = new Workbook();
workbook.Worksheets[0].Cells["H2"].SetSharedFormula("=IF(AND(NOT(ISBLANK(P2)),P2<50), \" < $50M\", IF(AND(P2>=50,P2<=299), \"$50M - $299M\", IF(AND(P2>=300,P2<=999), \"$300M - $999M\", IF(AND(P2>=1000,P2<=4900), \"$1B - $4.9B\",IF(P2>4900, \" > $5B\",\"Empty\")))))", 12, 1);
workbook.Save("output.xlsx");
Unfortunately, I can’t use this version. As my company using 17.3, can you make any adjustments to the formula and make that to work?
@kiran5388,
We are sorry there may not be any workaround/adjustments to cope with it using your older version. Neither we can include any fixes to older versions. The fixes are based on latest APIs set. We can only recommend you to kindly upgrade to and try our latest version. If latest version does not work the same way as MS Excel does, please share your template file, we will check it soon.
I asked for any adjustments in the formula, but the reply was NO.
Anyways Thanks
For your kind information, here is the workaround in 17.3 version
cells[“H2”].SetSharedFormula("=IFERROR(IF(ISBLANK(P2),"",LOOKUP(P2,{0, “< $50M”;50,"$50M - $299M";300,"$300M - $999M";1000,"$1B - $4.9B";4901,"> $5B"})),"")", 12, 1);
@kiran5388,
We are sorry if we could not give you workaround while using older version to cope with it. Actually, we do not evaluate issues using older APIs. Anyways, it is nice to know that you have sorted it out now. In the event of further queries or issue, feel free to write us back.
Can I know how to set the background color to a certain range for this formula
cells[“HM2”].SetSharedFormula("=IFERROR(IF(ISBLANK(P2),"",LOOKUP(P2,{0,"< $50M";50,"$50M - $299M";300,"$300M - $999M";1000,"$1B - $4.9B";4901,"> $5B"})),"")", rows.Count, 1);
@kiran5388,
See the document on how to set background color to cells range for your reference.
Setting Background Color and Font Attributes to a Named Range
Moreover, you may also try to use conditional formatting when a formula evaluates to true, it sets the fill color for the cell.