Formula cells to default to zero

Posted by: mhenrikson on 8 September 2017, 2:16 pm EST

  • Posted 8 September 2017, 2:16 pm EST

    I have a spread that does several cross sheet calculations. The cells with formulas that contain cross sheet calculations display as blank at runtime until all dependend data have been entered. Is there a way to default the cell to display a 0 instead?


    In Spread Designer, if I set the text property for these cells it automatically resets the text property to blank when I leave the property field.


    Thanks,


    Mark

  • Replied 8 September 2017, 2:16 pm EST

    Hello Mark,


    I checked your issue with Spread version 4.0.xxxx.xxxx and it shows 0 if the referenced cells have no value rather than blank at runtime. And in case we are setting the text property to 0 using designer for the cells having referenced formulas, it works fine and shows 0 until there are some values in the cells being referred i.e the calculated value replaces zero.


    Please let us know the version of Spread you are using and how are you setting the formulas?


    Thanks.

  • Replied 8 September 2017, 2:16 pm EST

    Hi Reeva,


     Thank you for the reply.


     We are using version 5 (5.0.3503.2008).


     I find that I can set the text to 0 in the designer for some fields, but if it has a cross sheet math formula the text will stay blank.


    Mark

  • Replied 8 September 2017, 2:16 pm EST

    Hello,

    That seems to be intending behavior  as until you have any mathematical function(addition, subtraction etc.) used in your formula for a cell it doesn't show a 0 in the cell.

    for example if the formula for a cell is 

    FpSpread1.Sheets(1).Cells(2, 0).Formula = "Sheet1!A2"

    it will not show a 0 in Sheets(1).Cells(2, 0)

    where as if the formula for a cell is

    FpSpread1.Sheets(1).Cells(1, 0).Formula = "Sheet1!A1+Sheet1!B1"

    it will show 0 for Sheets(1).Cells(1, 0)

     

    Thanks,

     

     

     

  • Replied 8 September 2017, 2:16 pm EST

    Do you know if there is anyway to override this behavior in code without losing the formula?


    Thanks

  • Replied 8 September 2017, 2:16 pm EST

    Hello Mark,


    You can try setting the formula using IF function which performs a comparison and returns one of two provided values based on that comparison and then try using ISBLANK method which tests whether a value, an expression, or contents of a referenced cell is empty.



    fpSpread1.Sheets[1].Cells[2, 2].Formula = "if(ISBLANK(sheet1!A1), 0,Sheet1!A1)";


    Hope this above mentioned code will help you. Thanks.

Need extra support?

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

Learn More

Forum Channels