View Single Post
  #7   Report Post  
Domenic
 
Posts: n/a
Default Return Numerical Label for LAST value Subtracted to reach Sum Target Value

Hi Sam!

No problem, see if this is what you're looking for...

D1:

=ROWS(Values)-MATCH(TRUE,SUBTOTAL(9,OFFSET(Values,ROWS(Values)-1,0,-SMALL
(ROW(Values)-MIN(ROW(Values))+1,ROW(Values)-MIN(ROW(Values))+1)))=C1,0)+
1

....confirmed with CONTROL+SHIFT+ENTER

E1:

=LOOKUP(2,1/(A1:INDEX(NumLabels,D1)0),A1:INDEX(NumLabels,D1))

Note that I've assumed that your data doesn't contain negative numbers.

Hope this helps!

In article <56932bf9b7374@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thank you. Your time and assistance is very much appreciated.

Your Formula does 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 appreciated.

Cheers,
Sam