title: Logarithmic averaging
body: <p>I am developing a custom function that will calculate the logarithmic average of a range.  The answer for the post <a href="https://stackoverflow.com/questions/23956551/logarithmic-averaging-preset-function-in-excel-using-ranges-as-input-values">logarithmic averaging question</a> is wrong but it is a starting point.  The problem lies in calculating the anti-logs (10^(0.1x)) of range values.  This is my first post so please forgive any missteps.</p>
  
  <p>Here is my code:</p>
  
  <pre><code>Function logavg(rngValues As Range) As Double
  
      Dim lSumofValues As Double
      Dim lCountofValues As Double
      Dim lAntilog As Double
      Dim rngLoop As Range
  
      lSumofValues = 0
      lAntilog = 0
      lCountofValues = rngValues.Count 'Get count of values in items in range
  
  'For loop - add the antilogs of the values in the range - does not work
      For Each rngLoop In rngValues
          lAntilog = WorksheetFunction.Power(10, 0.1 * rngLoop.Value)
          lSumofValues = lSumofValues + lAntilog
      Next
  
  'Perform calculation - logarithmic average
      logavg = 10 * Log(lSumofValues / lCountofValues)
  
  End Function
  </code></pre>
  
  <p>I tried this 'for' loop but it doesn't work:</p>
  
  <pre><code>    For Each rngLoop In rngValues
          lSumofValues = lSumofValues + (10 ^ (0.1 * rngLoop.Value))
      Next
  </code></pre>
  
  <p>This code for a simple (arithmetic) average works so I know that the range values are being transferred and used properly:</p>
  
  <pre><code>    For Each rngLoop In rngValues
      lSumofValues = lSumofValues + rngLoop.Value
      Next
      logavg = lSumofValues / lCountofValues
  </code></pre>
  
  <p>Test data is: 92.8, 79.1, 81.6, 78.3, 89.4, 86.5, 86.9</p>
  
  <p>The arithmetic average is 84.9 and the logarithmic average is 87.6.</p>
  
  <p>Two Excel formulae that calculate the logarithmic average of B2:B8 are:</p>
  
  <p>a) array formula =10*LOG(SUM(10^(0.1*B2:B8))/COUNT(B2:B8)), and</p>
  
  <p>b) standard formula =10*LOG(SUMPRODUCT(10^(0.1*B2:B8))/COUNT(B2:B8))</p>
  
  <p>Thanks.</p>