Need Custom formula

Posted by: mohanmuthusamy on 8 September 2017, 1:12 pm EST

  • Posted 8 September 2017, 1:12 pm EST

    Employer 1        
    HRA and Other Allowances        3000
    HRA and Other Allowances        4000
            
    Total Salary from Employer 1        7000
    Employer 2        
    HRA and Other Allowances        5000
    Fully Exempt Cash Allowance        6000
            
    Total Salary from Employer 2        11000
    Employer 3        
    HRA and Other Allowances        7000
    Fully Exempt Cash Allowance        3000
            
    Total Salary from Employer 3        10000
    Employer 4        
    Fully Exempt Cash Allowance        1000
    Fully Exempt Cash Allowance        4000
            
    Total Salary from Employer 4        5000
    Employer 5        
    HRA and Other Allowances        2000
    Fully Exempt Cash Allowance        3000
            
    Total Salary from Employer 5        5000
    Total Salary:        


    We see Above . i want to add  Total Salary from Employer 1 +Total Salary from Employer 2 +Total Salary from Employer 3 +Total Salary from Employer 4 + Total Salary from Employer 5
            Total Salary from Employer is dynamic row in future there may come  Total Salary from Employer 6,7 like. But my query is add all the Total Salary from Employer  and put it in total salary. I tried in many formauls by using SUMIF, mid functions... please help me.....


  • Replied 8 September 2017, 1:12 pm EST

    Dear Scotts,
                         I tried this one in farpoint 2.5 version. we convert all coding to c#. But in this part
     Public Overloads Overrides Function Evaluate(ByVal args() As Object) As Object
    Dim
    total As Integer
    Dim
    lbl As String
    total = 0
    For i As Integer = args(0).Row To args(0).Row + args(0).Row + args(0).RowCount - 1
    lbl = Sheet.GetValue(i, 0)
    If Not lbl Is Nothing AndAlso lbl.ToString.StartsWith("Total Salary from") Then
    total += Sheet.GetValue(i, 1)
    End If
    Next
    Return
    total
    End Function

    In this Part (args[0].Row ) . Row is not there.. please convert this one into c# and help me kindly.



  • Replied 8 September 2017, 1:12 pm EST

    Dear Bobbyo,
                         Thank you so much for Your reply.Its working fine. Thanks alot for your support
  • Replied 8 September 2017, 1:12 pm EST

    Hello,


    You should be able to debug this function in the custom class and see what type of object is passed into args[0] and then cast the object to that type. That type will have a Row property you can use.

  • Replied 8 September 2017, 1:12 pm EST

    hello i am getting error  "AddCustomFuntion not a member  of farepoint.web.spread.fpspread.how can i solve this?
  • Replied 8 September 2017, 1:12 pm EST

    mohanmuthusamy,


    You could use the SUBTOTAL function.  If the cell range passed to the SUBTOTAL function contains subtotals (i.e. nested subtotals) then these nested subtotals are ignored to avoid double counting.


    An example with two employers might look like...


                SheetView sheet = fpSpread1.Sheets[0];


                sheet.Cells[0, 0].Value = "Employer1";
                sheet.Cells[1, 1].Value = 3000.0;
                sheet.Cells[2, 1].Value = 4000.0;
                sheet.Cells[3, 1].Formula = "SUBTOTAL(9,B2:B3)";


                sheet.Cells[4, 0].Value = "Employer1";
                sheet.Cells[5, 1].Value = 5000.0;
                sheet.Cells[6, 1].Value = 6000.0;
                sheet.Cells[7, 1].Formula = "SUBTOTAL(9,B6:B7)";


                sheet.Cells[9, 0].Value = "Total";
                sheet.Cells[9, 1].Formula = "SUBTOTAL(9, B1:B9)";

  • Replied 8 September 2017, 1:12 pm EST

    Hello,


    Here is a way to implement this in a custom function.

     
      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    FpSpread1.Sheets(0).AddCustomFunction(New mySum(FpSpread1.Sheets(0)))
    FpSpread1.Sheets(0).Cells(25, 1).Formula = "MySum(A1:B25)"
    End Sub


    Public Class
    mySum
    Inherits FarPoint.CalcEngine.FunctionInfo

    Dim Sheet As FarPoint.Win.Spread.SheetView

    Public Sub New(ByVal sv As FarPoint.Win.Spread.SheetView)
    Sheet = sv
    End Sub

    Public Overloads Overrides Function
    Evaluate(ByVal args() As Object) As Object
    Dim
    total As Integer
    Dim
    lbl As String
    total = 0
    For i As Integer = args(0).Row To args(0).Row + args(0).Row + args(0).RowCount - 1
    lbl = Sheet.GetValue(i, 0)
    If Not lbl Is Nothing AndAlso lbl.ToString.StartsWith("Total Salary from") Then
    total += Sheet.GetValue(i, 1)
    End If
    Next
    Return
    total
    End Function

    Public Overrides Function
    AcceptsReference(ByVal i As Integer) As Boolean
    Return True
    End Function

    Public Overrides ReadOnly Property
    MaxArgs() As Integer
    Get
    Return
    1
    End Get
    End Property

    Public Overrides ReadOnly Property
    MinArgs() As Integer
    Get
    Return
    1
    End Get
    End Property

    Public Overrides ReadOnly Property
    Name() As String
    Get
    Return
    "mySum"
    End Get
    End Property
    End Class

     
  • Replied 8 September 2017, 1:12 pm EST

    Hello,


    Could you please provide more details related to the issue? Are you using the Windows application or web application? The AddCustomFunction is valid member of both the Spread for Windows as well as Web application and it can be accessed as follows in a web application:



    Private Sub PageLoad(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    Dim dataModel As New FarPoint.Web.Spread.Model.DefaultSheetDataModel(5, 5)
    dataModel.AddCustomFunction(New CubeFunctionInfo)
    dataModel.SetFormula(1, 1, "CUBE(4)")
    FpSpread1.ActiveSheetView.DataModel = dataModel


    End Sub


    When do you get  the above mentioned message, how are you using the Spread control at your side? Thanks

Need extra support?

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

Learn More

Forum Channels