Running total up to a set value, then take the remainder and continue with running total

[McFly1]

I have a series of values by month. I want to create a running total formula that sums the values until a set value (coming from another column) is reached. Once that set value is reached, only display that set value, take the remainder from the running total and continue with the running total, each time stopping at the set value and displaying that value.

Example: The Values are a forecast of items used each month. The set order point is 119. I want to show the months when the 119 items need to be ordered. I have tried a few things but am stumped. Is there a way to do this?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]Values[/TD]
[TD]10[/TD]
[TD]74[/TD]
[TD]66[/TD]
[TD]65[/TD]
[TD]65[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD]Running total[/TD]
[TD]10[/TD]
[TD]84[/TD]
[TD]150[/TD]
[TD]215[/TD]
[TD]281[/TD]
[TD]328[/TD]
[/TR]
[TR]
[TD]What I want to see[/TD]
[TD][/TD]
[TD][/TD]
[TD]119[/TD]
[TD][/TD]
[TD]119[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RCBricker]

you could enter this formula in your running totals cells

if your table is in cell A1.

so JAN is B1
Values is A2

cell B3=IF(B2>119,119,B2) (due to this being the first month of the table)
Cell C3 =IF(SUM(C2+B3)>119,119,SUM(C2+B3))

Copy C3 across to the remaining cells in that row.

The problem is once you hit 119 the remaining values will all be 119. You have not indicated here how the values will drop.

This should get you headed in the right direction though.

 

[RCBricker]

sorry I was out of the office yesterday.

So same situation except I think I realized a mistake I made in what you want.

Row 3 of your example is simply a running total.

Row 4 is where you want 119 to show for any instance where you need to reorder.

So try this,

Cell B3=B2
C3=SUM(C2+B3) Drag this to the right
B4=IF(B2-119>0,119,"")
C4=IF(SUM(C2+B3)-SUM(119*SUM(COUNTIF($B$6:B6,119)+1))>0,119,"") Drag this tot the right