Sum formula not evaluating but adding works

Posted by: abergheim on 8 September 2017, 12:41 pm EST

  • Posted 8 September 2017, 12:41 pm EST

    Hi


     


    I really hope somebody can help me. When I use the formula =A2+A3 it evaluates correctly, but when I use =SUM(A2,A3) it gives me 0.


    The sheetview is built from a datatable, which has all the columns as typeof string. When I look at A2 value, it says it is typeof string, I have tried to change the cell type editor with


    double val = double.parse(spread.Sheets[0].Cells[rownum, colnum].Value);


    spread.Sheets[0].Cells[rownum, colnum].Value = null;


    spread.Sheets[0].Cells[rownum, colnum].CellType = new FarPoint.Win.Spread.CellType.NumberCellType();


    spread.Sheets[0].Cells[rownum, colnum].Value = val;


     


    but this seems to do no good. ANY IDEAS?


     


    Thanks


    Anton

  • Replied 8 September 2017, 12:41 pm EST

    Anton -

    Are you setting the celltype for the cell before or after you do your binding?  Make sure you do it after you bind the sheet with your data.  You should also try setting DataAutoCellTypes = False and then set up your cells as number cell types.
  • Replied 8 September 2017, 12:41 pm EST

    Anton,


    The + operator is designed to convert non-numeric operands to numbers (if possible).  The SUM funtion is designed to ignore non-numeric operands.  For example, "1"+"2" evaluates to 3 but SUM("1","2") evaluates to 0.  This mimics the way Excel works.  In order to use the SUM function, the operands must be numbers.


    The cell type is used for parsing user input and for formatting user output.  The cell type does not come into play when getting or setting the Value property.  In you code snipet, the cell type setting does not affect the Value property assignment.


    Is your spreadsheet bound or unbound?


    If your spreadsheet is unbound then your code (get String, convert String to Double, set Double) should convert the data stored values from String to Double.  Once the values are convert to Double, the SUM function should work.


    If your spreadsheet is bound then your code might not work.  The assigned Double value will be passed back to the data source for storage.  If your data source only supports String values then the Double value would be converted back to a String value for storage in your data source.


    Bobby

  • Replied 8 September 2017, 12:41 pm EST

    Hi


    I have managed to resolve the issue, it seems to me that the sum will only work against columns which are of a numeric type (in bound mode). My issue was that I was data binding the sheet to a datatable containing all string columns. After I changed some columns to double type, and then data bound, it all worked fine. This means of course that I can't have strings and numbers in one column, but that works for me.


     


    Thanks for the quick response


    Thanks


    Anton

Need extra support?

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

Learn More

Forum Channels