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:

Wine Name	Opening Stock	Bottles	Total	Sales	Closing Stock
	Glass	Bottle	Total	Received		Glass	Bottle	total	Glass	Bottle	total
Wine 1	0.0	1.0	1.0	0.0	1.0	0.4		0.0	0.6	0.0	0.6
Wine 2	0.4	1.0	1.4	0.0	1.4	0.8	0.0	0.8	0.6	1.0	0.6

<tbody>
</tbody>

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)