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

[rkobeyer]

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. 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%

[bgoree09]

Good morning,

Here is a rough stab... I've changed all of the months to numbers. I assume these need to show the previous descriptions. I would incorporate these codes as descriptions and hide the numbers, but the numbers are what drive the calculations. Anyway, the only part that will probably be questioned is the December figure. The rounding simply has to go somewhere, and December was the simplest. You could probably do something to distribute this rounding error, but I can't think of anything simple offhand.

Hope this helps,

[rkobeyer]

Good morning,

Here is a rough stab... I've changed all of the months to numbers. I assume these need to show the previous descriptions. I would incorporate these codes as descriptions and hide the numbers, but the numbers are what drive the calculations. Anyway, the only part that will probably be questioned is the December figure. The rounding simply has to go somewhere, and December was the simplest. You could probably do something to distribute this rounding error, but I can't think of anything simple offhand.

Hope this helps,

Hi

Your solution works perfectly for "Ramp" figures, and get's close to perfect on "Even" but some totals give a negative in December as you said theres a rounding error.

Thank you for your help! You have helped me take a huge step forward. I will post back if I come up with something to fix the negatives.

Cheers,
Russell