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

Hi Sam!

After looking at this again, I've come up with another formula to
replace the first one, which I believe is more efficient...

D1:

=ROWS(Values)-MATCH(TRUE,MMULT(--(LARGE(ROW(Values),ROW(Values)-MIN(ROW(V
alues))+1)<=TRANSPOSE(ROW(Values))),Values)=C1,0) +1

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
Domenic wrote:

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!