Spread a yearly total evenly across chosen months using whole numbers only

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 whole numbers.

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.

Here is the new solution below in text too. The new formula should give the figures shown from CELL F2:Q5

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Start	End	Spread	2014	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
2	Customer 1	JAN	DEC	Ramp	50	1	2	2	3	3	4	4	5	5	6	7	8
3	Customer 2	MAY	DEC	Even	30					3	3	4	4	4	4	4	4
4	Customer 3	DEC	DEC	Ramp	10												10
5	Customer 4	MAR	NOV	Even	20			2	2	2	2	2	2	2	2	3
6
7					Even %	8.3%	8.3%	8.3%	8.3%	8.3%	8.3%	8.3%	8.3%	8.3%	8.3%	8.3%	8.3%
8					Ramp %	2%	3%	4%	6%	7%	8%	9%	10%	11%	12%	13%	15%

Hi

I've had a play about and came up with this. Please let me know if there's anything else mate

The rising percentile was a struggle but used a secondary table to balance it.

Click the reputation button as well please.

Hi Jockney,

Thanks so much for your help, it gets close, but not perfect.

For example on line 6, the total of 20, evenly spread using whole numbers only from MAR to NOV appears as MAR to OCT=2 but NOV=14. It should be MAR to SEP=2, OCT to NOV=3 or something similar.

And on line 3, a total of 10, spread "ramped up" from JAN to DEC appears as JAN to AUG= 1, SEP to NOV=2 and DEC=10 - This is a difficult example because realistically the "ramp up" 10 based on "ramp up" figures should finish earlier than DEC

I'll keep working on your solution for now to see if I can tweak it a bit, but it's a huge step for me. I'll clicked your reputation button.

Sorry I hadn't amended an absolute reference properly and hadn't updated the formula's properly. This has been sorted and works fine now. The attached is a lot better and hopefully exactly what you're after.

Many thanks