# 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