Results 1 to 5 of 5
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    convert subtotals to means using sumproduct (97sr2H)

    I needed this, perhaps it'll be useful to someone, and testing and improvements are welcome.

    I produce reports on all our customers, showing average projected revenue and profitability. Each of our customers may have a variety of products with different rates, and different enrollment counts for each product.

    To product the report I use Data, Subtotals and select the appropriate columns to subtotal by customer. Then to develop average customer rate and average customer profitability, I convert certain of the subtotals to means using =SUMPRODUCT, using this:


    Sub subtot2sumprodmean()
    Dim rngCell As Range, rngFormulaCells As Range
    Dim strFormula As String, strLocalRange As String, strWeightRange As String
    Dim intCalcSet As Integer, intFormulaLen As Integer, intOffsetColsLeft As Integer, intColonPos As Integer

    Application.ScreenUpdating = False
    intCalcSet = Application.Calculation
    Application.Calculation = xlCalculationManual
    On Error GoTo Reset ' if no formulas are found in Selection.SpecialCells
    Set rngFormulaCells = Selection.SpecialCells(xlCellTypeFormulas, 1)

    intOffsetColsLeft = Application.InputBox( _
    "How many columns offset to the left is the denominator/weight data?", _
    "Subtotal to SumproductMean", , , , , , 1)

    For Each rngCell In rngFormulaCells
    strFormula = rngCell.Formula
    If Left(strFormula, 12) = "=SUBTOTAL(9," Then
    intFormulaLen = Len(strFormula)
    ' get the range now being subtotalled
    strLocalRange = Mid(strFormula, 13, intFormulaLen - 13)
    intColonPos = InStr(strLocalRange, ":")
    ' check that more than one cell is being totalled
    If Left(strLocalRange, intColonPos - 1) <> Mid(strLocalRange, intColonPos + 1) Then
    'get the range to be used a denominator/weight
    strWeightRange = Range(strLocalRange).Offset(0, -intOffsetColsLeft).Address
    'set new formula, using =IF(,,) to avoid zero denominator errors
    rngCell.Formula = "=IF(sum(" & strWeightRange & "),SUMPRODUCT(" & strWeightRange & _
    Right(strFormula, intFormulaLen - 11) & "/sum(" & strWeightRange & "),)"
    End If
    End If
    Next rngCell
    rngFormulaCells.Replace What:="$", Replacement:=""

    Reset:
    If Err.Number = 1004 Then MsgBox "No formulas of any kind in Selection."
    Set rngFormulaCells = Nothing
    Application.Calculation = intCalcSet
    Application.ScreenUpdating = True
    Application.Calculate

    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert subtotals to means using sumproduct (97sr2H)

    Are you aware, that by using the second argument to SUBTOTAL you can have the average:

    =SUBTOTAL(Range,1)
    whereas:
    =SUBTOTAL(Range,9)
    gives the SUM
    (look in OLH for the others)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: convert subtotals to means using sumproduct (97sr2H)

    Jan,

    Not being picky, but should not the Function argument come before the range.

    Andrew

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert subtotals to means using sumproduct (97sr2H)

    Of course. Silly me.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: convert subtotals to means using sumproduct (97sr2H)

    Yes, but that yields a simple average rather than the arithmetic mean I need.

    However, I also see that =SUBTOTAL(6,range,...) is PRODUCT, I'll have to see if that could be of use: looks like if I'm working with numerator and denominator columns A & B it would have to be in the form =SUBTOTAL(6,A1:B1,A2:B2,A3:B3,...) to get what I'm looking for.

    Hmmm ...
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •