Formula not working for cell

Posted by: koustubh on 8 September 2017, 1:33 pm EST

  • Posted 8 September 2017, 1:33 pm EST

    Hello Sir, I am using spread control for dot net 2.0 (VS 2005) windows based.

    I have define formula for cell as folllows

    <FONT size=2>

    <FONT color=#ff0000>if(H9<0,if(H10>0,"Error",if(H11>0,"Error","Ok")),"Ok") </FONT><FONT color=#000000>but it is giving me run time error as </FONT><FONT size=1>

    <FONT color=#ff0000 size=2>Unexpected Operand was encountered Error offset 27</FONT>

    <FONT color=#000000 size=2>but same formula on VB6.0's Spread control working fine. Please Help. </FONT>

    <FONT color=#ff0000 size=2></FONT> 



  • Replied 8 September 2017, 1:33 pm EST

    .Net handles quotations within quotations differently.  Do the following...


  • Replied 8 September 2017, 1:33 pm EST


    The formula worked fine in my testing...

    In VB, I used the code...

        FpSpread1.Sheets(0).Cells(11, 7).Formula = "if(H9<0,if(H10>0,""Error"",if(H11>0,""Error"",""Ok"")),""Ok"")"

    In C#, I used the code...

        fpSpread1.Sheets[0].Cells[11,7].Formula = "if(H9<0,if(H10>0,\"Error\",if(H11>0,\"Error\",\"Ok\")),\"Ok\")";

    If you are still haing problems, could you provide the code that you are using to perfrom the formula assignment?

    Note that the spreadsheet will throw an ParseException when the string being assigned does not represent a valid formula.  For example, if I mistakenly left out the comma between the "Error" string constant and the if function...

        FpSpread1.Sheets(0).Cells(11, 7).Formula = "if(H9<0,if(H10>0,""Error""if(H11>0,""Error"",""Ok"")),""Ok"")"

    then I would get an exception similar to what you are seeing...

        ParseException was unhandled
        Unexcepted operand was encountered
        Error offset: 24

    The error offset informs you of the location in the string where the parsing is failing.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels