Charles:

Just comformed the efficiencies I suspected you can with your methods.
I too see less than a second using your "AreAnyEmpty" function.
compared to original troublesome 300 without.

Your vArr assignment must be doing something important too, since it
takes 16 seonds using this simple filtering loop at the top of my
"AverageUnderTailof" function

For i = 1 To Distribution.Cells.Count
If IsEmpty(Distribution(i).Value) And _
Len(Distribution(i).Formula) 0 Then Exit Function
Next

Nick

On Thu, 11 Dec 2008 04:26:16 GMT, (Nicholas Dreyer)
wrote:

Charles:

First off thanks for posting this extraordinarily thorough solution.

Now I must exclaim, what a remarkable coincidence it is to see this
great response of yours today!

Earlier today I found your extremely informative web site which
already has explained enough for me to see (sort of) what was going
on, at least enough to be able to come up with the solution to just
filter all cells of incoming ranges for this condition displayed on
your site:

ISEMPTY(Cell.Value) AND Len(Cell.formula)0.

Your, no doubt better, solution below does raise a few questions for
which answers, if you are in a position, and so inclined to provide
them would help me greatly understand a few long-standing VBA puzzles.


First, the most significant is invoked by your use of the statement

vArr = Distribution.Value2

Given that you must be much more versed in the efficiencies of VBA
coding than I, I must assume that using vArr - with it's awkward extra
dimension - has advantages over directly accessing a range argument.
If so, I am very curious what they would be.

The way the assignment above is done in fact reminds me of a strange
asymmetry I have never been able to comprehend: It is possible to,
with extreme efficiency, assign very large two-dimensional VBA arrays
to ranges by a simple assignment

Range object = VBA ArrayVariable

The above looks like it might be the way I have been looking for
achieving the reverse. Does going through vArr improve the relatively
sluggish VBA response time seen when accessing range values by
referencing each individual cell? This would be quite a revelation to
me if you can say this is true, as I have never been able to figure
out how to get similar speeds loading massive range values into VBA
variables as you do loading large arrays back into excel sheets.

Second, I do not understand the difference between value2 and value.
In particular, is this distinction crucial in the context of your
solution?

Thanks a-million Charles. I will be studying your site for some time
to come, and look forward to any further insights you might be able to
post here regarding my follow-up queries.

Nick


On Wed, 10 Dec 2008 11:44:30 -0000, "Charles Williams"
wrote:

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