Calculate how long it would take to reduce a backlog to 0 based on weekly additions/subtractions

[Jennifer_Reynolds]

Hi everyone,

I am looking to generate a formula that takes a current customer list and calculates a number for how many weekly cycles of additions/subtractions (new customers minus completed orders) it would take to get the Customer List to 0. The required steps are therefore:

1) The current 'Customer List' (cell B2) and adds 'Projected Additions (C2).
2) Then adds 'Projected Activity' (D2)
3) Repeats this cycle until the difference is 0 or less and then adds in E2 that number

So for the first example, week 1 is 100 + 50 - 100 = 50. Week 2 is 50 + 50 - 100 = 0. 0 is reached, therefore number is 2 cycles to get to 0.
For the second example, week 1 is 100 + 1 - 100 = 1. Week 2 is 1 + 1 - 100 = - 98. Therefore number is 2 cycles to get to 0.
As an additional problem, if the additions are higher than the activity then of course 0 will never be reached - in those circumstances I'd like a value of 'Never' to be returned.

Can anyone please help?

Customer Base	Customer List	Projected Additions	Projected Activity	Weeks To 0
A	100	50	100	2
B	100	1	100	2
C	100	10	120	1
D	100	100	1	Never

I think I am nearly there with this:

=IF(CEILING(B2/(C2-D2),1)>0,"Never",ABS(CEILING(B2/(C2-D2),1)))

However for Example B it gives a value of '1' whereas the answer should be 2. Not sure what is causing that - some mistake in forcing rounding?

 

[Snakehips]

@Jennifer_Reynolds Does this give you what you want?

Book1
	A	B	C	D	E
1		Cust List	Additions	Activity	Weeks To 0
2		100	50	100	2
3		100	1	100	2
4		100	10	120	1
5		100	100	1	Never
6		100	1	2	100
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=IF(C2>=D2,"Never",ROUNDUP(B2/(D2-C2),))