On Sat, 29 Oct 2005 19:40:14 GMT, "Sam via OfficeKB.com" <u4102@uwe wrote:

Hi Ron,

Ron Rosenfeld wrote:
I misread your specifications. The correct formula would be:

=INDEX(A1:A30,MATCH(D1,C1:C30,-1))

Your Formula does now provide exactly what I requested. Unfortunately, I over
simplified the example: in a few cases the Numeric Label above the Summed
Target Value could be zero (0). Is there anyway of adapting your existing
Formula to exclude any Numeric Labels of zero (0) and thus return what would
be the next non-zero Numeric Label.

Apologies for my over simplified example.

Further assistance much appreciated.

Cheers,
Sam

In your expansion you do not indicate how many zeros might be present, but
rather you ask to return the first non-zero label counting upwards.

I decided it would be simpler to write (and perhaps subsequently modify) a UDF.

The UDF accepts as arguments the Data table with numerical labels in the first
column and your values in the second column. The second argument will be the
Sum Target.

It should give the result you specify no matter how many 0's are present.

In addition, it will give a #NUM! error if the Sum Target cannot be reached.
You can certainly add other error conditions.

To enter this, <alt<F11 opens the VB Editor. Ensure your project is
highlighted inthe project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter a formula of the type:

=label(A1:B30,D1)

where A1:B30 is your table of Numerical Labels and Data, and D1 is the Sum
Target.

===========================================
Option Explicit

Function Label(ByVal DataTable As Range, SumTargetValue As Range)
Dim Dt As Variant
Dim i As Long
Dim s As Double

Dt = DataTable

For i = UBound(Dt) To 1 Step -1
s = s + Dt(i, 2)
If s = SumTargetValue Then Exit For
If i = 1 Then
Label = CVErr(xlErrNum)
Exit Function
End If
Next i

Do Until Label < 0
Label = Dt(i, 1)
i = i - 1
Loop

End Function
=============================


--ron