That's a wonderful example of the way Excel's recalculation process works
(this behaviour is by design).

see http://www.decisionModels.com/Calcsecretsj.htm for an explanation and
examples of how to make this UDF more efficient,
and associated pages for an explanation of Excel's calculation process.

The function below runs in about 0.6 of a second as compared to over 300 on
your TestBugN example
(note you also have to change your sort routine to handle a 2-d variant
array).

Function AverageUnderTailof(Distribution As Range, atEnd As Long, Number As
Long) As Double
Dim i As Long, StartofTail As Long, EnterTime As Double

Dim vArr As Variant

EnterTime = Timer
If funcount = 0 Then StartTime = EnterTime
vArr = Distribution.Value2
If AreAnyEmpty(vArr) Then Exit Function
StartofTail = 1 + atEnd * (UBound(vArr) - Number)
AverageUnderTailof = 0
With SortOrderof(vArr)
For i = StartofTail To StartofTail + Number - 1
AverageUnderTailof = AverageUnderTailof + vArr(.Values(i), 1)
Next
End With
AverageUnderTailof = AverageUnderTailof / Number
funcount = funcount + 1
Debug.Print Format(funcount, "0"), Format(Timer - EnterTime, "0.0000"),
_
Format(Timer - StartTime, "0.0000"),
Format(AverageUnderTailof, "#,##0.00")
End Function

Function AreAnyEmpty(vArr As Variant) As Boolean
Dim j As Long

AreAnyEmpty = False
If VarType(vArr) = 8192 Then
For j = 1 To UBound(vArr)
If IsEmpty(vArr(j, 1)) Then
AreAnyEmpty = True
Exit For
End If
Next j
Else
If IsEmpty(vArr) Then AreAnyEmpty = True
End If
End Function

regards
Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

"Nicholas Dreyer" wrote in message
...
I have gotten these symptoms to show up in both Excel 2003 and Excel
2000.

The following downloadable sheet, has just the one user-defined
function (in cell selected when opened). To make testing manageable,
calculation is purposely set to "manual" and "not on save". After
opening, pressing F9 (calculate key) takes you through 1981 passes of
the function until the sheet thinks it is calculated. The function
has a 2001-cell range argument. Calculation can be forced by
editing/changing any single cell in the range D18 through AA18

As usual, during calculation you can interrupt the process by
selecting a cell on the sheet before it is done. Checking the seconds
per function pass dumped to the Immediate Window, you will be able to
estimate roughly how long it will take to finish after 1981 passes (5
minutes on one of my machines, 10 on another).

http://www.oz.net/~gurfler/Download/TestBugN.xls

Once you have let the calculation complete, it never takes that long
again. The following sheet is in that state, but is in no other
respect different:

http://www.oz.net/~gurfler/Download/TestBugOK.xls

Diagnostics illustrating very strange behavior during each pass of the
function are dumped to the Immediate Window in the following (just hit
F9 as soon as it is loaded):

http://www.oz.net/~gurfler/Download/TestBugDebug.xls

These diagnostics indicate that on first pass the function only sees
one cell in its range-argument as non-empty, next pass two cells,
etc., more or less, until everything clears up after 1981 passes (why
20 short of 2001 ???).

Making the range argument of the function into a worksheet array is a
great way to prevent this horrible stall in calculation, though the
diagnostic here does show that even then it still takes two passes to
complete, first pass apparently all cells come through empty.

http://www.oz.net/~gurfler/Download/...DebugArray.xls

OK, no smart-alec comments: I know that this function merely
duplicates what can be achieved through the following formula using
exclusively excel's built in functions:

{=AVERAGE(SMALL(AJ27:AJ2027,AG28:AG128))} (as an ArrayFormula)

My purpose is only to test the functionality of user defined functions
like this, and the example provided here shows some limitations, or at
least the need for work-arounds in some instances.

Does anybody know what is causing this behavior, and how to mitigate
its effects? Is it really necessary/advisable to create arrays for
all unser-defined function arguments, or can something else be done to
help avoid this bizarre behavior.

Thanks, Nick