自定义对数平均值计算函数开发求助：反对数计算异常

Hey Cameron, let's work through this logarithmic mean custom function issue step by step—since your arithmetic mean code works, we already know the range value passing is solid, so we can focus on fixing the antilog calculation loop and related logic.

First, let's confirm the core logic you're aiming for (based on your mention of 10^(0.1x)):

1. Convert each value x in your target range to its antilog: 10^(0.1*x)
2. Calculate the arithmetic mean of these converted antilog values
3. Convert that mean back to the logarithmic scale: 10 * log₁₀(mean_value)

Since your arithmetic mean code runs without issues, the problem is almost certainly in the antilog calculation loop or the final reverse conversion step. Let's break down common mistakes and fixes:

1. Common Loop Errors & Corrected Code Example #

Let's assume you're using VBA (common for spreadsheet custom functions)—here's a typical broken loop and how to fix it:

Example of a Non-Working Loop (Possible Your Original Code) #

Function LogMean(rng As Range) As Double
    Dim total As Double
    Dim cell As Range
    ' Issue: Uninitialized accumulator + potential syntax error in exponentiation
    For Each cell In rng
        total = total + 10^(0.1*cell.Value) ' Syntax quirk in VBA can break this
    Next cell
    Dim avg As Double
    avg = total / rng.Cells.Count
    LogMean = 10 * Log10(avg)
End Function

The main issues here are: not initializing total to 0, missing parentheses around the exponent term, and no handling for non-numeric cells.

Corrected Working Version #

Function LogMean(rng As Range) As Double
    Dim total As Double
    Dim cell As Range
    total = 0 ' Always initialize accumulators to avoid garbage values
    
    For Each cell In rng
        ' Skip empty/non-numeric cells to prevent runtime errors
        If IsNumeric(cell.Value) Then
            total = total + 10 ^ (0.1 * cell.Value)
        End If
    Next cell
    
    Dim validCount As Integer
    validCount = Application.WorksheetFunction.Count(rng) ' Count only numeric cells
    
    If validCount = 0 Then
        LogMean = CVErr(xlErrDiv0) ' Return division-by-zero error if no valid data
    Else
        Dim avgAntilog As Double
        avgAntilog = total / validCount
        LogMean = 10 * Application.WorksheetFunction.Log10(avgAntilog)
    End If
End Function

2. Key Checks for Your Specific Language #

If you're using a different language (Python, JavaScript, etc.), adjust these points:

• Antilog Syntax: Use the correct exponentiation method:
  • Python: 10 ** (0.1 * x) or math.pow(10, 0.1 * x)
  • JavaScript: Math.pow(10, 0.1 * x)
• Logarithm Base: Ensure you're using base 10 for the final conversion (not natural log ln), since your antilog uses base 10.
• Edge Cases: Add checks for empty values, zeros, or negative numbers (if applicable to your use case) to avoid invalid math operations.

3. Test with Known Values to Verify #

To confirm your function works, use a small test set where you can calculate manually:

• For range [20, 40]:
  1. Antilogs: 10^(0.1*20) = 100, 10^(0.1*40) = 1000
  2. Mean of antilogs: (100 + 1000)/2 = 550
  3. Final log mean: 10 * log₁₀(550) ≈ 27.40

If your function returns this value, it's working correctly!

内容的提问来源于stack exchange，提问作者Cameron