about changable parameters

Posted by: zhenghq on 8 September 2017, 2:48 pm EST

  • Posted 8 September 2017, 2:48 pm EST

    I use this formula

    max(h1:h720), countif((h1:h720),0),sum(h1:h720) ......

    but the number of 720 is not fixed, it's changable according to the database record. As the record increase or decrease, it changes

    in Excle,I use an enough large number to solve this problem. For example, my total database record at that time is 500, my Excel2003 formula is max(h1:h65000)

    so how to write the formula in Spread.

  • Replied 8 September 2017, 2:48 pm EST

    zheng_hq,

    If your want to include all values in the column in the formula then you can use a column reference. For example, the formula SUM(H:H) adds up all the values in column H. Column references are supported in both Spread and Excel.

  • Replied 8 September 2017, 2:48 pm EST

    bobbyo:

    I use FrozenTrailingRows instead of ColumnFooter

    IF fill the cells in FrozenTrailingRows by using formula SUM(H:H), it can't show the computation.

     so how to use sum(h:h) in ColumnFooter

  • Replied 8 September 2017, 2:48 pm EST

    Hello,


    As per your requirement you want to sum all the values in a column, regardless of the number of values in the column, and display the result in ColumnFooter or FrozenTrailingRow. In case you want to display the sum in ColumnFooter you can use the SetAggregationType method which would sum all the values in a Column regardless of the count and display the result in ColumnFooter. The code to implement the same is:


                                           fpSpread1.Sheets(0).ColumnFooter.Visible = True
                                            Private Sub button1_Click(sender As Object, e As EventArgs)
                                                   fpSpread1.Sheets(0).ColumnFooter.SetAggregationType(0, 0, FarPoint.Win.Spread.Model.AggregationType.Sum)
                                            End Sub
                  


    This piece of code would sum all the values in first column and display the result in the first cell of the first row in the ColumnFooter.


    Secondly, if you want to display the sum in FrozenTrailingRow, then you can make use of a local variable, to get the total number of rows in the sheet and use that variable in your formula to specify the index of the last row in the cell range specified in the formula, the code for implementing the same is:


                                           fpSpread1.Sheets(0).FrozenTrailingRowCount = 1;
                                           Dim num As Integer = fpSpread1.Sheets(0).RowCount - 2
                                           Private Sub button1_Click(sender As Object, e As EventArgs)
                                              fpSpread1.Sheets(0).Cells(fpSpread1.Sheets(0).RowCount - 1, 0).Formula = "SUM(A1:A" & num & ")"                                                                                              
                                           End Sub
                                                                           


     This piece of code would sum all the values in the first column and display the result in first cell of the first FrozenTrailingRow.


    Hope it will help you. Please let me know if you have any queries further.


    Thanks,


    Manpreet Kaur.

Need extra support?

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

Learn More

Forum Channels