Dynamic cumulative sum formula (?!)

Hi, new to the forum, hope someone can help....

I attach a simplified version the spreadsheet I am working on so as to isolate the particular issue as best I can.

It shows sales projects in terms of units of product on a month by month basis over a 12 month period.

Below each month's entry are 2 further entries 'Min Reorder period ending Month' and 'Min Reorder Quantity'. Values for eacvh entry have been entered manually for the sake of this post. I am looking for a way to get excel to calculate this values - ideally without having to enter more data to help it get there.


Min Reorder period ending Month: refers to the FUTURE Month in which - if I order product in the current month (the month the 'Min Reorder period ending Month' text sits under - I will have made projected sales that reach the supplier's min order quantity. For this example this quantity is 100.


Min Reorder Quantity - refers to the actual quantity - based on projected sales - of product I would need to order when making this order.

Clearly these 2 figures change with each month.

The figures manually filled in illustrate how: eg in Month 1 I will need to order 112 units (the sum of b2:n2 being the first cumulate sum in that row to be equal to or more than 100); it also tells me that I will have ordered enough to cover my projected sales until Mom=nth 7 if I order this amount in the current month - in this example Month 1.

Is there a way of getting a formula to make these calculations?

I realise that if I had a tally of the cumulative projected orders month by month that I could use MIN(IF which I have done in cell B10 using cumulative data in Row 3.

BUT the problem with that is that I have to have a different row of cumulative data for each new month so that the formula would be sure to look up the correct cumulative order amount relative to IT. This is illustrated by the entry in D10 - using the same Min(if formula for month 2 it simply returns the same result as for Month 1, which is clearly not correct.

I'd really prefer not to have many rows of different cumulative projected sales data to make this work. Can anyone help? Hope this is clear!

Just to clarify, is this the actual layout of your data? In other words, do you currently have two columns per month, and the cumulative data in row 3 anyway?

Hi Rorya, thanks for the response!

Actual worksheet is quite a bit more complex and is still a work in progress. But essentially, as it stands,:

yes, I have 2 columns per month
no, the cumulative data is in the same column (one row above the incremental data).

BUT I am very happy to change the layout - as I said, this is a work in progress, all is flexible.

Does this help?

Jason - fantastic, nailed it. Very grateful INDEED!