Summing portions of a column until certain value is met

I have been trying to find the best way of solving the problem without success. Any advice from the experts out there would be greatly appreciated.

For Segment 1 (Column D), I need a formula which sums the volumes in column B until the SumTotal Volume in Column E (Volume) is met or nearly met. There will be very few circumstances where there is an exact match so the sum calculated needs to be as close as possible to the volume in Column E. Whichever cell in column B is added last to reach the necessary sum, I need the adjacent cell from column A to be input into the yellow box in Column G. I've done them partly by hand and using some formulas based on how a similar problem was solved on another forum an example of the data that should appear. (e.g. Starting at Raw Point 0, it took up to a Raw Point 25 to reach the sum volume of 3.97 which is the number closet to 4. If we had gone to raw Point 26 the total would have been 4.78 which is further away from 4 than 3.97).

For Segment 2, I need the start Raw Point to be 1 greater than the end Raw Point from segment 1. (e.g. starting at a Raw Point of 26, it took up to a Raw Point of 31 to reach a volume of 6.49 which is the number closet to 7). And so on.

Please let me know what are the best formulas to put into the yellow boxes. A very similar problem was solved on another forum - http://www.mrexcel.com/forum/excel-q...value-met.html. I have tried to replicate this but unfortunately this formula seems to fail when it reaches Segment 6 and beyond. Is there something I am doing wrong? Any help or advise with this will be appreciated

Thanks very much for your efforts on this. I now see how complicated this might turn out to be. What I am trying to do is recalibrate a lot of data to fit into a 9 segment model. Using this model I need data to be assigned to each segment so that the model makes sense - i.e. the data must fit as close as possible to volume totals you see in column E (these are percentages that add up to 100%). Therefore to get a meaningful result we need to have some data showing for each segment. So having no data under Segment 9 makes the data unusable. Currently I am doing this summing up manually - which is very painful across 45 tabs). How we assign this data seems to be the issue? As you suggest if I have to pick a summing method I would say there might be 2 options (1) use the total just before or equal to volume total (2) use the total within 10% of our required figure (whether its bigger or smaller). Does that make sense? Is this even possible? Is there maybe some other way to achieve this result - maybe I have tried to force a formula that doesn't really work for this situation.

Hi,

Yes your solution is very good - the numbers coming out are very suitable to what I need except for final segment. What kind of workaround do you suggest?

One guidance that might be useful. When I do this manually, I find that the outer categories - i.e. the 4% and 7% categories are often most difficult to deal with. They are often too big or too small - so for example I often have to accept 2% at the top or the bottom, though I was hoping for 4%. It would be helpful if these outer categories were as close to the target as possible while the middle ones can vary a bit more. Maybe the formula could say for the 4% categories we can accept 4.7 max before one category of rawpoints become part of the 7% group, etc. Does this make sense?
The above are just some thoughts which might not be possible to implement in excel. Once again thanks for all your help - you have made me think a bit more about this.

Regards

This should simply equal to the very last entry in the raw points column - this is always 60.