cumulative stock without negatives

[bullit_nl]

Hi,

I'm trying to do a cumulative stock count vs needed per day.
Example
A1 is quantity need to be in stock
B1 is quantity incoming
C1 is difference between B1 and A1
D2 I want the cumulative total stock per day (copy down)
E2 I want the cumulative over stock per day (copy down)

This works well as long there's a overstock. But once the is a number of continues days with negative stock I don't want the see the cumulative anymore because negatives will be resolved daily.
How can I make a formula that I can copy down that shows me the cumulative over stock results until I run into a negative stock and then continues again on overstock?

Thank you

Example data:

100​	500​	400​		400
150​	1.000​	850​	1.400	1.250
200​	1.500​	1.300​	2.750	2.550
250​	2.000​	1.750​	4.550	4.300
300​	2.500​	2.200​	6.800	6.500
350​	3.000​	2.650​	9.500	9.150
400​	3.500​	3.100​	12.650	12.250
450​	4.000​	3.550​	16.250	15.800
500​	4.500​	4.000​	20.300	19.800
550​	5.000​	4.450​	24.800	24.250
600​	5.500​	4.900​	29.750	29.150
650​	6.000​	5.350​	35.150	34.500
700​	6.500​	5.800​	41.000	40.300
10.000​	7.000​	-3.000​	47.300	37.300
20.000​	7.500​	-12.500​	44.800	24.800
30.000​	8.000​	-22.000​	32.800	2.800
40.000​	8.500​	-31.500​	11.300	-28.700
50.000​	9.000​	-41.000​	-41.000	0
60.000​	9.500​	-50.500​	-50.500	0
70.000​	10.000​	-60.000​	-60.000	0
80.000​	10.500​	-69.500​	-69.500	0
90.000​	11.000​	-79.000​	-79.000	0
200.000​	225.000​	25.000​		25.000
225.000​	250.000​	25.000​	275.000	50.000
250.000​	275.000​	25.000​	325.000	75.000
275.000​	300.000​	25.000​	375.000	100.000
300.000​	325.000​	25.000​	425.000	125.000
325.000​	350.000​	25.000​	475.000	150.000
350.000​	375.000​	25.000​	525.000	175.000
375.000​	400.000​	25.000​	575.000	200.000

 

[jasonb75]

Something like this?

Book2
	A	B	C	D	E
2	100	500	400	500	400
3	150	1000	850	1400	1250
4	200	1500	1300	2750	2550
5	250	2000	1750	4550	4300
6	300	2500	2200	6800	6500
7	350	3000	2650	9500	9150
8	400	3500	3100	12650	12250
9	450	4000	3550	16250	15800
10	500	4500	4000	20300	19800
11	550	5000	4450	24800	24250
12	600	5500	4900	29750	29150
13	650	6000	5350	35150	34500
14	700	6500	5800	41000	40300
15	10000	7000	-3000	47300	37300
16	20000	7500	-12500	44800	24800
17	30000	8000	-22000	32800	2800
18	40000	8500	-31500	11300	-28700
19	50000	9000	-41000	-41000	0
20	60000	9500	-50500	-50500	0
21	70000	10000	-60000	-60000	0
22	80000	10500	-69500	-69500	0
23	90000	11000	-79000	-79000	0
24	200000	225000	25000	225000	25000
25	225000	250000	25000	275000	50000
26	250000	275000	25000	325000	75000
27	275000	300000	25000	375000	100000
28	300000	325000	25000	425000	125000
29	325000	350000	25000	475000	150000
30	350000	375000	25000	525000	175000
31	375000	400000	25000	575000	200000
Sheet3

Cell Formulas
Range	Formula
C2:C31	C2	=B2-A2
D2:D31	D2	=IF(E2=0,C2,E2+A2)
E2:E31	E2	=IF(AND(E1<=0,C2<0),0,N(E1)+C2)

 

[bullit_nl]

Hello Jason,

Exactly something like that! Super.....works like a charm. Thank you for your time and effort to help me out on this.
I have also made a percentage difference in column F2 (copy down):

Formula:
E2/A2 %

But if the result is 0 then I would like to get the percentage result of D2/A2.

 

[bullit_nl]

How can I do this in one formula in column F?

 

[Fluff]

Maybe

=IF(E2=0,D2,E2)/A2

 

[bullit_nl]

Hi Fluff,

Yes it works but I don't understand the formula
How does it know when it has to do E2/A2 or D2/A2?

IF E2 = 0 then D2/A2 otherwise E2/A2?

And do I get the right percentage if D is a negative number and A is a positive number?

 

[Fluff]

The first part of the formulaIF(E2=0,D2,E2) returns either D2 or E2 depending on if E2 is 0 & then divides that by A2

 

[bullit_nl]

Ok clear thank you for the explanation.
Do you know about the negative vs positive percentage outcome?

 

[Fluff]

I've no idea if you get the right result, as I have no idea what you expect to get.