Subtracting until zero then taking remainder from another cell.

[Legacy 414377]

Hi all,

I am having a problem thinking of formula to calculate my wine stock with Excel 2013.

Here is an example:

[TABLE="width: 878"]
<tbody>[TR]
[TD]Wine Name[/TD]
[TD="colspan: 3"]Opening Stock[/TD]
[TD]Bottles[/TD]
[TD]Total[/TD]
[TD="colspan: 3"]Sales[/TD]
[TD="colspan: 3"]Closing Stock[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Glass[/TD]
[TD]Bottle[/TD]
[TD]Total[/TD]
[TD]Received[/TD]
[TD][/TD]
[TD]Glass[/TD]
[TD]Bottle[/TD]
[TD]total[/TD]
[TD]Glass[/TD]
[TD]Bottle[/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD]Wine 1[/TD]
[TD]0.0[/TD]
[TD]1.0[/TD]
[TD]1.0[/TD]
[TD]0.0[/TD]
[TD]1.0[/TD]
[TD]0.4[/TD]
[TD][/TD]
[TD]0.0[/TD]
[TD]0.6[/TD]
[TD]0.0[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]Wine 2[/TD]
[TD]0.4[/TD]
[TD]1.0[/TD]
[TD]1.4[/TD]
[TD]0.0[/TD]
[TD]1.4[/TD]
[TD]0.8[/TD]
[TD]0.0[/TD]
[TD]0.8[/TD]
[TD]0.6[/TD]
[TD]1.0[/TD]
[TD]0.6[/TD]
[/TR]
</tbody>[/TABLE]


The problem comes from glasses and bottles being separate. 1 bottle = 5 glasses or 0.2. If we sell more glasses than we have in opening stock then we have to open a new bottle. I want closing stock to minus glass sales from opening glasses until it is zero, then take the remainder from a freshly opened bottle (worth 5 glasses); decreasing the closing bottle count when doing so and adding the remainder to glasses.

I hope this can be done its been driving me mad trying to figure this out and takes ages inputting these manually every time!

Thank you very much in advance

 

[Peter_SSs]

Welcome to the MrExcel board!

Your second example doesn't make sense to me. Opening stock was 1.4, no purchases give a total still of 1.4. If glass sales were 0.8 how could there still be 1 full bottle left?

Anyway, see if these help.

Book1
	B	C	D	E	F	G	H	I	J	K	L
1	Op Stock			Bottles	Total	Sales			Cl Stock
2	Glass	Bottle	Total	Received		Glass	Bottle		Glass	Bottle	total
3	0	1	1	0	1	0.4			0.6	0	0.6
4	0.4	1	1.4	0	1.4	0.8	0		0.6	0	0.6
5	0.4	1	1.4	3	4.4	1.6	1		0.8	1	1.8
Wine stock

Cell Formulas
Range	Formula
J3	=F3-H3-G3-K3
K3	=INT(F3-H3-G3)
L3	=SUM(J3:K3)