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 <b>add </b> 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
    <span class="kwd"> Public Overloads Overrides Function</span> Evaluate(<span class="kwd">ByVal</span> args() <span class="kwd">As Object</span>) <span class="kwd">As Object
    Dim</span> total <span class="kwd">As Integer
    Dim</span> lbl <span class="kwd">As String</span>
    total = 0
    <span class="kwd">For</span> i <span class="kwd">As Integer</span> = args(0).Row <span class="kwd">To</span> args(0).Row + args(0).Row + args(0).RowCount - 1
    lbl = Sheet.GetValue(i, 0)
    <span class="kwd">If Not</span> lbl <span class="kwd">Is Nothing AndAlso</span> lbl.ToString.StartsWith(<span class="st">"Total Salary from"</span>) <span class="kwd">Then</span>
    total += Sheet.GetValue(i, 1)
    <span class="kwd">End If
    Next
    Return</span> total
    End Function

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

    <span class="kwd">
    </span>

  • 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.

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


    Public Class</SPAN> mySum
    <SPAN class=kwd>Inherits</SPAN> FarPoint.CalcEngine.FunctionInfo

    <SPAN class=kwd>Dim</SPAN> Sheet <SPAN class=kwd>As</SPAN> FarPoint.Win.Spread.SheetView

    <SPAN class=kwd>Public Sub New</SPAN>(<SPAN class=kwd>ByVal</SPAN> sv <SPAN class=kwd>As</SPAN> FarPoint.Win.Spread.SheetView)
    Sheet = sv
    <SPAN class=kwd>End Sub

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

    Public Overrides Function</SPAN> AcceptsReference(<SPAN class=kwd>ByVal</SPAN> i <SPAN class=kwd>As Integer</SPAN>) <SPAN class=kwd>As Boolean
    Return True
    End Function

    Public Overrides ReadOnly Property</SPAN> MaxArgs() <SPAN class=kwd>As Integer
    Get
    Return</SPAN> 1
    <SPAN class=kwd>End Get
    End Property

    Public Overrides ReadOnly Property</SPAN> MinArgs() <SPAN class=kwd>As Integer
    Get
    Return</SPAN> 1
    <SPAN class=kwd>End Get
    End Property

    Public Overrides ReadOnly Property</SPAN> Name() <SPAN class=kwd>As String
    Get
    Return</SPAN> <SPAN class=st>"mySum"</SPAN>
    <SPAN class=kwd>End Get
    End Property
    End Class</SPAN>
     
  • 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

    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:


    <BLOCKQUOTE>

    <FONT class=keyword>Private</FONT> <FONT class=keyword>Sub</FONT> PageLoad(<FONT class=keyword>ByVal</FONT> sender <FONT class=keyword>As</FONT> System.<FONT class=keyword>Object</FONT>, <FONT class=keyword>ByVal</FONT> e <FONT class=keyword>As</FONT> System.EventArgs) Handles MyBase.Load


    <FONT class=keyword>Dim</FONT> dataModel <FONT class=keyword>As</FONT> <FONT class=keyword>New</FONT> FarPoint.Web.Spread.Model.DefaultSheetDataModel(5, 5)
    dataModel.<FONT style="BACKGROUND-COLOR:#ffffff;" color=#000000>AddCustomFunction</FONT>(<FONT class=keyword>New</FONT> CubeFunctionInfo)
    dataModel.SetFormula(1, 1, <FONT class=string>"CUBE(4)"</FONT>)
    FpSpread1.ActiveSheetView.DataModel = dataModel


    <FONT class=keyword>End</FONT> <FONT class=keyword>Sub</FONT>

    </BLOCKQUOTE>

    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