L
Legacy 414377
Guest
Hi all,
I am having a problem thinking of formula to calculate my wine stock with Excel 2013.
Here is an example:
<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
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