Stock book subtraction after a certain sum (for vaccine stock)

[Raja Nazir]

Hi,
I 'm a vaccinator , I have to maintain stock book of vaccine. vaccine vial comes in different doses. So until the dose is, we have to place the same previous value.
for example a vaccine vial of TT comes in 20 doses,

now, I have used 4, 6, 8, 4(=22) doses in four days, so On fourth day i will have to open a new vial, until then, I want the same quantity in total column that is 220, and on fourth day,the formula subtract only 20 doses .
is there any formula for that, it will save my time and will be thankful

 

[maabadi]

Welcome to MrExcel Message Board

Book1
	A	B	C	D
1
2
3	TT
4	In	Out	Total
5
6
7
8		0	220
9		4	220
10		6	220
11		8	220
12		4	200
13		2	200
14		9	200
15		6	200
16		5	180
17		3	180
18		4	180
19		5	180
20
Sheet2

Cell Formulas
Range	Formula
C9:C19	C9	=$C$8 + SUM($A$5:A9) - INT(SUM($B$5:B9)/20)*20

 

[maabadi]

Or Try This:

Book1
	A	B	C	D
1
2
3	TT
4	In	Out	Total
5
6
7
8		0	220
9		4	220
10		6	220
11		8	220
12		4	200
13		2	200
14		9	200
15		6	200
16		5	180
17		3	180
18		4	180
19		5	180
20
Sheet2

Cell Formulas
Range	Formula
C9:C19	C9	=INDEX(C:C,MATCH(TRUE,INDEX(ISNUMBER(C:C),0),0)) + SUM($A$5:A9) - INT(SUM($B$5:B9)/20)*20