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

Hi Ron,

Thank you. I really do appreciate you taking the time and effort to provide a
workable solution to my often not so clear scenarios.

Ron Rosenfeld wrote:
Here's one way that'll get you the correct answer. It involves adding a third column:


C1: =SUM(B1:$B$30)
Then copy/drag down to C30.
The formula that will return the Label would then be:
=INDEX(A1:A30,MATCH(target_value,C1:C30,-1)+(COUNTIF(C1:C30,target_value)=0))


--ron


Your Formula gets me very close to my required Result. It provides the
Numeric Label of the Summed Target Value; however, the required Result is the
Numeric Label ONE Row above the Numeric Label of the Summed Target Value. I
would have subtracted the value of one (1) from the Result returned by the
Formula but unfortunately, in a few cases the Numeric Label above the Summed
Target Value could be zero (0). Is there anyway of adding to your existing
Formula to return the Numeric Label that is one Row above the Summed Target
Value, excluding any Numeric Labels of zero (0) and thus returning what would
be the next non-zero Numeric Label.

Apologies for my over simplified example.

Further assistance appreciated.

Cheers
Sam

Ron Rosenfeld wrote:
Here's one way that'll get you the correct answer. It involves adding a third
column:

C1: =SUM(B1:$B$30)

Then copy/drag down to C30.

The formula that will return the Label would then be:

=INDEX(A1:A30,MATCH(target_value,C1:C30,-1)+(COUNTIF(C1:C30,target_value)=0))

--ron



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