Problem with summing a column of formula results

Posted by: jasonmell on 8 September 2017, 1:51 pm EST

  • Posted 8 September 2017, 1:51 pm EST

    Hi,

     I'm using Spread.NET for WinForm v4.0.3509.2008. Attached is a JPEG image of a portion of my spreadsheet where I'm having trouble getting some SUM formulas to work within the cells.

    The two columns in focus are the Real-time Nominated and Final Variance. The Final Variance column is formula set using R1C1 style taking the Real-time Nom numbers and subtracting them from the Confirmed column. As you can see, the formula appears to be working fine. At the bottom, in between the two bold black lines, is where I am having summing issues with the Final Variance column.  It continues to say 0 as the SUM result and my formula is just a simple SUM(R1C11:R342C11).  I've triple-checked and that is the correct column/row range for this data. I've opened up "Allow User Formulas" to even type this in another cell, but I get the same results... can I not SUM a column of formulas?  This seems unusual.  Any ideas?

     Thanks,

    Jason 



  • Replied 8 September 2017, 1:51 pm EST

    Jason,


    Cells with formulas can reference other cells with formulas.  So, that is not the problem.


    What is the data type of the cell values that are being passed to the SUM function.  The SUM function adds up number values (e.g. 123) but ignores string values (e.g. "123").  If the values in R1C11:R342C11 are string values then the formula =SUM(R1C11:R342C11) would evaluate to 0.  You could use a formula like =ISNUMBER(R1C11) or =ISTEXT(R1C11) to check the data type of the cell values.


    Do you have any hidden columns?  If so, then what visually looks like column C11 might actually be C12 or higher.  You could use a formula like =R1C11 to check that you using the correct column reference.

  • Replied 8 September 2017, 1:51 pm EST

    Hi bobbyo,

    Thanks for the advice.  I am referencing the correct row/column as I tried the =R1C11 parameter and returned the correct data. I did however try the ISNUMBER and ISTEXT routines and came back with 0 for ISNUMBER and 1 for ISTEXT which seems to tell me what you're saying, that the celltypes are text.  The thing I don't understand is that I have the columns set in Spread Designer to be number and after seeing your message, I additionally went into the code and set each individual celltype as it loops and sets the formula:

     

    .setformula(0, 10, "R1C8 - R1C9")
    .cells(0, 10).CellType = TotalsCellType 

    I'm setting the celltype declaration at the beginning of the procedure.

    Dim TotalsCellType As FarPoint.Win.Spread.CellType.NumberCellType = New FarPoint.Win.Spread.CellType.NumberCellType 

     

    Even after this, it still comes up as 1 for ISTEXT.  ???? \

    I got the following looking at the celltype for the field(s):

    ?GX19CONFIRMspread.ActiveSheet.Cells(0, 10).CellType

    {FarPoint.Win.Spread.CellType.NumberCellType}

        FarPoint.Win.Spread.CellType.NumberCellType: {FarPoint.Win.Spread.CellType.NumberCellType}

     

     

     Doing some research, =SUM(R1C11:R1C11) still comes to zero when it should be 36 based on what's in the cell.  Also just want to add that doing a simple addition/subtration of two cells within this column works. (ie. =R1C11 - R2C11).  It is just SUM that doesn't seem to.

     

     

     

  • Replied 8 September 2017, 1:51 pm EST

    Also just want to add that doing a simple addition/subtration of two cells within this column works. (ie. =R1C11 - R2C11).  It is just SUM that doesn't seem to.
  • Replied 8 September 2017, 1:51 pm EST

    SnowWhile,

    You may attach your sample project to this thread as an attachment and also upload it to our FTP site the address is ftp://ftp.fpoint.com/. In the mean time, I am attaching a sample project to calculate sum and difference on a column, which seems to be working fine.

    Thanks


    2009/04/SUMFormula.zip
  • Replied 8 September 2017, 1:51 pm EST

    Thanks SureshD,

    That example works, however, mine is a little more complicated as I'm doing a databind to the Spread from values in a database. So, it'll be difficult for me to give you a working example for your end. I'm beginning to think there's a bug occuring when you try to do a SUM on a list of formula results when the Spread is databound. To prove my theory, I went and commented out the bind statement and messed with numbers in the same Spread myself.  When I did this, the SUM function worked fine when trying to sum formula results and the ISTEXT function returned 0 for the formula result cells like it should. I also made sure that DataAutoCellTypes was false, which it is. So I'm still perplexed why my celltype shows Number in a bound spread, but ISTEXT says the cell is 1, and I can still calculate addition/subtraction, etc. but only the SUM doesn't work on these cells..... could you try an example with binding to a database and seeing if you have the same issue?

     Thanks,
    Jason 

  • Replied 8 September 2017, 1:51 pm EST

    jasonmell,


    Since the cell values being summed are text values (i.e. ISTEXT returns TRUE), the formula results you are seeing are the correct formula results.  The + operator will convert text values to number values (if possible) before adding.  The SUM function will ignore text values.  This is the same as in Excel.  For example, suppose cell A1 contains the text value "12" and cell A2 contains the text value "34".  The formula =A1+A2 will evaluate to 46 while the formula =SUM(A1:A2) will evaluate to 0.


    You mentioned that the cells have NumberCellType.  The CellType is an UI concept that determines how the user enters cell values, how cell values are displayed to the user, and how cell values are converted to/from text.  The CellType plays a part in determining the data type of cell values when entered by user or when assigned by application code via the Text property.  However, the CellType does not play any part in determining the data type of cell values when entered by application code via the Value property, or when computed via formulas, or when retrieved from a data source.


    When bound to a data source, the cell values are stored in the data source.  Thus, the data source ultimately determines the data type of the cell values.  In your example, I am guessing that your data base is defining the FinalVariance column as text values.  The formulas which subtract the Confirmed column from the RealTimeNominated column produce number values which then get converted to text values as they are being stored in the FinalVariance column in the data source.  When the total formula attempts to sum the FinalVariance column, the SUM function ignores these text values and thus returns 0.


    If you what to compute the total of the FinalVariance column using the SUM function then you will need to change your data base to define the FinalVariance column as number values.

  • Replied 8 September 2017, 1:51 pm EST

    bobbyo, Thanks. In order to correctly get all data bound, I needed to "skip" those columns in the SQL so I made them null values in the query which would result in blanks. I replaced the null values with zero and the summing is working now. Still a bit confusing on the cell type/data type confliction though.  Something to pass on to the programmers.  Cheers!

Need extra support?

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

Learn More

Forum Channels