自定义函数条件LOGEST

Question

我创建了一个函数，它将范围作为参数，并尝试执行条件LOGEST，只要单元格不是空白的，并且单元格没有通过文本进行划。但是，我无法让它运行:

Function CustomTrend(rng As Range) As Double
Dim TrendArr() As Variant
Dim ArrSpot As Integer
Dim count, countsq, countsum As Double
Dim LNy, Xsq, XxLNy As Double
Dim last As Integer
last = rng.End(xlDown).row
LNy = 0
Xsq = 0
XxLNy = 0
ArrSpot = 0
count = 0
countsq = 0
countsum = 0
For i = 1 To last Step 1
If rng.Cells(i, 1).Value <> "" And rng.Cells(i, 1).Font.Strikethrough = False Then
ArrSpot = ArrSpot + 1
count = count + 1
TrendArr(ArrSpot) = rng.Cells(i, 1).Value
End If
Next i
For k = ArrSpot To 0 Step -1
LNy = LNy + WorksheetFunction.Ln(TrendArr(ArrSpot))
XxLNy = ArrSpot * WorksheetFunction.Ln(TrendArr(ArrSpot)) + XxLNy
countsq = ArrSpot ^ 2 + countsq
countsum = countsum + ArrSpot
Next k
CustomTrend = (count * XxLNy - countsum * LNy) / (count * countsq - countsum ^ 2)
End Function

Answer 1

排序在我自己的,请看下面的修改后的代码。需要注意的是，我还必须在代码的其他部分添加.calculatefull，以确保在单元格中使用该函数时会更新。

Function CustomTrend(rng As Range) As Double

Dim count, countsq, countsum As Double
Dim LNy, Xsq, XxLNy As Double
LNy = 0
Xsq = 0
XxLNy = 0
ArrSpot = 0
count = 0
countsq = 0
countsum = 0
For Each cell In rng
If cell.Value <> "" And cell.Font.Strikethrough = False Then
count = count + 1
countsum = countsum + count
LNy = LNy + WorksheetFunction.Ln(cell.Value)
XxLNy = count * WorksheetFunction.Ln(cell.Value) + XxLNy
countsq = count ^ 2 + countsq
End If
Next cell
CustomTrend = (count * XxLNy - countsum * LNy) / (count * countsq - countsum ^ 2)
End Function