Hello,

Glad that you had some fun.

I think it's you who returns to a "regularly scheduled life"

Just in case you lose some faith in your formula in future:

FunctionFoodTop2(r As Range) As Variant

'Select range of 3 x 3 cells andarray-enter thisfunction.

'First column of input range is Type, second Product, third Pounds.

'Reverse("moc.liborplus.www") PB V0.1 05-Jun-2010

Dim i As Long, j As Long

Dim s1 As String, s2 As String

Dim st(1 To 2, 1 To 2) As String

Dim d(1 To 2, 1 To 2) As Double

Dim vR(1 To 3, 1 To 3) As Variant

s1 = "Fruits"

s2 = "Vegetables"

For i = 1 To r.Rows.Count

If IsEmpty(r.Cells(i, 2)) Then Exit For

j = 1: If s2 = r.Cells(i, 2).Text Then j = 2

If st(j, 1) = "" Then

'Initialize first of this product

st(j, 1) = r.Cells(i, 1).Text

d(j, 1) = r.Cells(i, 3).Value

Else

If st(j, 2) = "" Then

'Initialize second of this product

st(j, 2) = r.Cells(i, 1).Text

d(j, 2) = r.Cells(i, 3).Value

Else

If d(j, 2) < r.Cells(i, 3).Value Then

If d(j, 1) < r.Cells(i, 3).Value Then

'Replace first and second

st(j, 2) = st(j, 1)

d(j, 2) = d(j, 1)

st(j, 1) = r.Cells(i, 1).Text

d(j, 1) = r.Cells(i, 3).Value

Else

'Replace second only

st(j, 2) = r.Cells(i, 1).Text

d(j, 2) = r.Cells(i, 3).Value

End If

End If

End If

End If

Next i

vR(1, 1) = "": vR(1, 2) = 1: vR(1, 3) = 2

vR(2, 1) = s1: vR(2, 2) = st(1, 1): vR(2, 3) = st(1, 2)

vR(3, 1) = s2: vR(3, 2) = st(2, 1): vR(3, 3) = st(2, 2)

FoodTop2 = vR

EndFunction

A sample file is here:

http://dl.dropbox.com/u/6077606/20100610_PB_01_Stats_on_Subtypes_1500...

This macro is about 10x slower than your formula - which means that

the algorithm could be about 10x more efficient than your formula

(assuming that VBA is in general 100x slower than worksheet

functions). Of course this will only be important for you if you

detect an error in your formula or if you need to apply changes later

which you might not be able to solve with your formula.

Regards,

Bernd- Hide quoted text -

- Show quoted text -