none
sum of an average

    Question

  • I have the following matrix and i have a column average(the last column) but i also need a row subtotal and the cell marked in yellow should actually show me the sum or the averages above..can somebody help me with this..I tried to use the idea of Ian roof in one of the posts here to use a custom code function but using that it gives me the sum of the average but gives me NaN for the cells above...

     

    Location Type 1 P1 68.38 68.92 68.94 68.75
    P2 5.56 5.34 5.43 5.44
    P3 22.40 22.12 22.07 22.20
    P4 2.68 2.72 2.72 2.71
    Total  99.02 99.10 99.16 24.77
    Type 2 P1 69.20 69.57 69.50 69.42
    Total  69.20 69.57 69.50 69.42

    this is the code

    Private m_total As Double

    Private m_count As Integer

    Public  Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

    If inDateScope And inDivisionScope Then

        ' Regular cell

      

        Return subtotal

    Else If Not inDateScope And inDivisionScope Then

        ' Average of Division

        Return average

    Else If inDateScope And Not inDivisionScope Then

        ' Subtotal of Date

        m_total = m_total + subtotal

        m_count = m_count + 1

        Return average

    Else

        ' Average of Subtotal

        Dim avg as Double

        avg =  m_total / m_count

        m_count =2

        m_total =0

        Return avg

    End If

    End Function

     

    and i call the function like this 

    =Code.CalculateSumSubtotalOrAverage(Sum(Fields!CV.Value),AVG(Fields!CV.Value),InScope("Month"),InScope("Product"))

    Thursday, September 04, 2008 7:09 PM