Hi there,

I've been working on the holy grail of excel formulas to solve my current labor intensive excel work.

A startup company entrusted me to figure out their monthly orders based on a yearly total that they give me.

They also wanted me to take into account if the orders are spread evenly or "ramped up" incrementally over the year, depending on what they choose.

I created an excel solution that solved this, for a while. For example 10 orders spread evenly over 12 months was 0.8, or alternatively, a "ramped up" spread is 0.2 in Jan, 0.3 in Feb to 1.5 in Dec.

But the engineers have come back to me saying they don't want to see decimal places when figuring out how many products they need to build. They want [B]whole numbers[/B].

Fair enough, but there are 4 products divided over 4 years for 30 customers, all with their own monthly order quantities; doing this manually is a nightmare and opens me up to error. And these quantities can change daily with new customers, or an engineer's change of mind.

On top of that, if a customer wants, say, 20 products in 2014, but the engineers can't start manufacturing until June, I will need to spread 20 from June to December.

So using whole numbers this, spread evenly, would be: JUN = 2, JUL to DEC = 3. Or if they can't start until December, all 20 products will be manufactured in December.

I've looked everywhere to a solution to this problem but haven't had much luck. I am starting to worry that a solution might not exist!

Please help!

I have attached an excel file showing my current, inadequate solution and the layout of the new solution that you will hopefully help me with.