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 non-zero 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 |
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 |