View Single Post
  #6   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default Return Numerical Label for LAST value Subtracted to reach Sum Target Value

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


Domenic wrote:
Try the following...

First, define dynamic ranges for Column A and Column B...

Insert Name Define

Name: NumLabels

Refers to:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9.999999999 99999E+307,Sheet1!$A:$A)
)

Click Add

Name: Values

Refers to:

=Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9.999999999 99999E+307,Sheet1!$A:$A)
)

Click Ok

Then use the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER...

=INDEX(NumLabels,ROWS(Values)-MATCH(TRUE,SUBTOTAL(9,OFFSET(Values,ROWS(Va
lues)-1,0,-SMALL(ROW(Values)-MIN(ROW(Values))+1,ROW(Values)-MIN(ROW(Value
s))+1)))=C1,0)+1)

...where C1 contains your 'Sum Target Value'.

Hope this helps!

Hi All,

[quoted text clipped - 58 lines]
Thanks
Sam



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1