Home 
Search 
Today's Posts 
#1




Return Numerical Label for LAST value Subtracted to reach Sum Target Value
Hi All,
I am looking for a Formula that can process the following: The Sum Target Value is variable e.g.; 147 I have two columns of numerical values: Column “A” and Column “B.” Column “A” Houses the Numerical Labels that I wish to have Returned when the Sum Target Value is processed /reached  Subtract Sum Target Value as noted below. To Subtract Sum Target Value: Start from LAST nonzero numerical value in Column “B” and Subtract one Cell value at a time (or Sum up the Column) until the Sum Target Value or nearest possible Sum BELOW, the Sum Target Value is reached. In this instance, it is 147. I wish to Sum the values in Column “B” to 147 per the above. The summed values can be below BUT NOT over the Sum Target Value. Return the Numerical Value that is Offset ONE Cell to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column “B”) to reach Sum Target Value. The Result – Numerical Label should come from Column “A.” Col “A” Col “B” 200 3 205 1 210 5 215 11 220 10 225 15 230 16 235 10 240 11 245 29 250 20 255 27 260 22 265 34 270 24 275 36 280 30 285 25 290 31 295 26 300 15 305 18 310 23 315 17 320 9 325 11 330 2 335 9 340 1 0 0 Reaching the Sum Target Value of 147 in Column “B” would go up to value 26, Label 295 in Column “A”, totalling 131 which is below the Sum Target 147 but including the value of 31 above it, would exceed the Sum Target Value of 147. The required Result is returned from Column “A” Label 290 which is ONE Cell to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column “B”). Thanks Sam  Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200510/1 
#2




Return Numerical Label for LAST value Subtracted to reach Sum Target Value
On Fri, 28 Oct 2005 21:01:37 GMT, "Sam via OfficeKB.com" <[email protected] wrote:
Hi All, I am looking for a Formula that can process the following: The Sum Target Value is variable e.g.; 147 I have two columns of numerical values: Column “A” and Column “B.” Column “A” Houses the Numerical Labels that I wish to have Returned when the Sum Target Value is processed /reached  Subtract Sum Target Value as noted below. To Subtract Sum Target Value: Start from LAST nonzero numerical value in Column “B” and Subtract one Cell value at a time (or Sum up the Column) until the Sum Target Value or nearest possible Sum BELOW, the Sum Target Value is reached. In this instance, it is 147. I wish to Sum the values in Column “B” to 147 per the above. The summed values can be below BUT NOT over the Sum Target Value. Return the Numerical Value that is Offset ONE Cell to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column “B”) to reach Sum Target Value. The Result – Numerical Label should come from Column “A.” Col “A” Col “B” 200 3 205 1 210 5 215 11 220 10 225 15 230 16 235 10 240 11 245 29 250 20 255 27 260 22 265 34 270 24 275 36 280 30 285 25 290 31 295 26 300 15 305 18 310 23 315 17 320 9 325 11 330 2 335 9 340 1 0 0 Reaching the Sum Target Value of 147 in Column “B” would go up to value 26, Label 295 in Column “A”, totalling 131 which is below the Sum Target 147 but including the value of 31 above it, would exceed the Sum Target Value of 147. The required Result is returned from Column “A” Label 290 which is ONE Cell to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column “B”). Thanks Sam 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 
#3




Return Numerical Label for LAST value Subtracted to reach Sum Target Value
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.9999999999 9999E+307,Sheet1!$A:$A) ) Click Add Name: Values Refers to: =Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9.9999999999 9999E+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! In article <[email protected], "Sam via OfficeKB.com" <[email protected] wrote: Hi All, I am looking for a Formula that can process the following: The Sum Target Value is variable e.g.; 147 I have two columns of numerical values: Column “A” and Column “B.” Column “A” Houses the Numerical Labels that I wish to have Returned when the Sum Target Value is processed /reached  Subtract Sum Target Value as noted below. To Subtract Sum Target Value: Start from LAST nonzero numerical value in Column “B” and Subtract one Cell value at a time (or Sum up the Column) until the Sum Target Value or nearest possible Sum BELOW, the Sum Target Value is reached. In this instance, it is 147. I wish to Sum the values in Column “B” to 147 per the above. The summed values can be below BUT NOT over the Sum Target Value. Return the Numerical Value that is Offset ONE Cell to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column “B”) to reach Sum Target Value. The Result – Numerical Label should come from Column “A.” Col “A” Col “B” 200 3 205 1 210 5 215 11 220 10 225 15 230 16 235 10 240 11 245 29 250 20 255 27 260 22 265 34 270 24 275 36 280 30 285 25 290 31 295 26 300 15 305 18 310 23 315 17 320 9 325 11 330 2 335 9 340 1 0 0 Reaching the Sum Target Value of 147 in Column “B” would go up to value 26, Label 295 in Column “A”, totalling 131 which is below the Sum Target 147 but including the value of 31 above it, would exceed the Sum Target Value of 147. The required Result is returned from Column “A” Label 290 which is ONE Cell to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column “B”). Thanks Sam 
#4




Return Numerical Label for LAST value Subtracted to reach Sum Target Value
On Fri, 28 Oct 2005 23:27:57 0400, Ron Rosenfeld
wrote: On Fri, 28 Oct 2005 21:01:37 GMT, "Sam via OfficeKB.com" <[email protected] wrote: Hi All, I am looking for a Formula that can process the following: The Sum Target Value is variable e.g.; 147 I have two columns of numerical values: Column “A” and Column “B.” Column “A” Houses the Numerical Labels that I wish to have Returned when the Sum Target Value is processed /reached  Subtract Sum Target Value as noted below. To Subtract Sum Target Value: Start from LAST nonzero numerical value in Column “B” and Subtract one Cell value at a time (or Sum up the Column) until the Sum Target Value or nearest possible Sum BELOW, the Sum Target Value is reached. In this instance, it is 147. I wish to Sum the values in Column “B” to 147 per the above. The summed values can be below BUT NOT over the Sum Target Value. Return the Numerical Value that is Offset ONE Cell to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column “B”) to reach Sum Target Value. The Result – Numerical Label should come from Column “A.” Col “A” Col “B” 200 3 205 1 210 5 215 11 220 10 225 15 230 16 235 10 240 11 245 29 250 20 255 27 260 22 265 34 270 24 275 36 280 30 285 25 290 31 295 26 300 15 305 18 310 23 315 17 320 9 325 11 330 2 335 9 340 1 0 0 Reaching the Sum Target Value of 147 in Column “B” would go up to value 26, Label 295 in Column “A”, totalling 131 which is below the Sum Target 147 but including the value of 31 above it, would exceed the Sum Target Value of 147. The required Result is returned from Column “A” Label 290 which is ONE Cell to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column “B”). Thanks Sam 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 I misread your specifications. The correct formula would be: =INDEX(A1:A30,MATCH(D1,C1:C30,1)) ron 
#5




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 nonzero 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 
#6




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 nonzero 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 
#7




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 <[email protected], "Sam via OfficeKB.com" <[email protected] 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 nonzero Numeric Label. Apologies for my over simplified example. Further assistance appreciated. Cheers, Sam 
#8




Return Numerical Label for LAST value Subtracted to reach Sum Target Value
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 nonzero Numeric Label. Apologies for my over simplified example. Further assistance much appreciated. Cheers, Sam Ron Rosenfeld wrote: Hi All, [quoted text clipped  71 lines] ron I misread your specifications. The correct formula would be: =INDEX(A1:A30,MATCH(D1,C1:C30,1)) ron  Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200510/1 
#9




Return Numerical Label for LAST value Subtracted to reach Sum Target Value
Hi Domenic,
That's Great! Cheers, Sam Domenic wrote: 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!  Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200510/1 
#10




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! 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Return Range of Numerical Values in Single Column based on Frequency Percentage  Excel Worksheet Functions  
AVERAGE Row of Numbers and Return Corresponding Numeric Label  Excel Worksheet Functions 